What? There is a DynamicsPerf 2.0? It’s taken 6 months and almost 600 hours of coding time. This upgrade really is worthy of the 2.0 version. What has changed? EVERYTHING !! Don’t worry it still has the same look and feel.
So, with that, let me begin to go through the new features.
Remote Data Collection
Yes, we can finally install the DynamicsPerf database on a server other then where your Dynamics production database resides. This has to have been one the biggest enhancement requests that we’ve had for the toolset. This gives everyone the flexibility to provide the data in DynamicsPerf without having to give them access to the production server. This also means when analyzing data in DynamicsPerf that you do not consume any resources of the production database.
DynamicsPerf now has it’s own scheduling engine that allows us to collect each of the data points on their own schedules. The core stored procedure has been broken down into individual procedures that can be scheduled independently of each other. To avoid having 100s of SQL Agent Jobs, we have added our own scheduling tables and have a couple of stored procedures that are scheduled as SQL Jobs that drive the whole process of data collection. The big impact of this change is that now we are collecting query_stats every 5 minutes. This also means you do not have to manually collect data when there is a problem because by the time you find out, it will have already been collected.
One of the challenges of collecting data more often is that creates a storage problem trying to keep all that data. To alleviate that problem, we actually aggregate query and index data into Daily and Monthly totals in history tables: QUERY_HISTORY and INDEX_HISTORY. This has been one of the far reaching impacts in DynamicsPerf 2.0. First, the size of the database has trended much smaller then prior versions. Second, you no longer have to do anything special to collect baseline data. In fact, that job has been removed. With the aggregated data you can compare any day to any other day and any month to any other month or compare a day to the month. There are many new ways to slice and dice the data. We have added many new sample queries.
More QUERY Data
We now collect QUERY_STATS every 5 minutes. This makes it possible to look in 5 minute increments and see what was happening to your SQL Server. We keep this detailed data for 2 days. This is one of the many configurable settings if you want to keep more data. DynamicsPerf database will have the data before you find out about the problem from the users.
The new version can now collect data from your SSRS server so you can track down issues that may be related to reporting. This is always a big question that comes up when the server is under load, which is how much of it is data entry vs. reporting. You will now be able to monitor that situation. Ever had a situation where, “hey the report is slow”? Is it slow because of SQL or because of something in the report, like too many subtotals or image processing? You can now solve those issues in this version
We have built a set of reports in PowerBI that you can include in your own dashboards. Take advantage of this new feature to monitor your Microsoft Dynamics system !!
Dynamics CRM collectors
YES, we have specific collectors for Dynamics CRM in this version. There are 3 CRM specific collectors for the Organization data, PrincipalObjectAccess, and Plugins data. Very exciting stuff for resolving performance issues with Dynamics CRM On Prem.
New Data collectors
We have new collectors for :
- Change Data Control
- Change Tracking
- SQL Plan Guides
- and more
We have added fulltext indexes for the DynamicsPerf database to speed processing of some of the more challenging queries that were in the project. You can now find all of the queries using a specific index in seconds instead of hours.
Hub and Spoke of DynamicsPerf datasbases
All the tables have been modified so that you can now merge data from 1 DynamicsPerf database into another DynamicsPerf database. This is especially important for those AX Retail customers who want to track data in the local store database and then move that data to corporate later. This is also great for everyone with a multiple database Dynamics deployment.
SQL Server 2016 support
We have added support for the new data added to sys.dm_exec_query_stats in SQL Server 2016. We also provide a script for enabling QUERY_STORE on your Dynamics Database
Microsoft Azure support
If you have a login to your SQL Azure environment, you can collect performance data for these databases.
There is now an option to refresh the query_plans in your DynamicsPerf database. This is great for getting the latest parameters that the query is using. This is also great for when you may have made significant Trace Flag changes. This will also help in updating/refreshing the parameter values for the query. We used to keep the values from the first time we collected the query plan. This would become a challenge months later when that data may not even exist in the database. Refreshing of the plans solves this issue.
Stale QUERY_PLAN Removal
There is an option for removing old queries from the DynamicsPerf database now. Ever change the code and have the old query stick around in the DynamicsPerf database? This is one of the new configurable options.
Performance Counter data on SQL
We have added a new data collector to DynamicsPerf that collects the data from sys.os_performance_counters. There are sample queries added to review that data.
With the addition of the new history tables and how data is aggregated we can now do some interesting monitoring. We have added a new process that will populate a table called QUERY_ALERTS. This is done based on when a query’s current execution behavior is x% worse than the Day or Month for that query. This is configurable. The goal for this feature is try to help everyone find those sudden parameter sniffing queries.
When you enable the long running trace functionality in Dynamics AX using SET_AX_TRACE it will remember the settings you used and automatically update new users to that setting. There is a new processing task that will keep this updated for you. If you enable this feature for all users, it will update any new users added to the system and enable it for them as well.
The AOTEXPORT class has been significantly enhanced. It now writes direct to the DynamicsPerf database so there is no longer a need to modify your table schema. We now collect better Batch Job and Batch Server information then prior versions. We also are now collecting License and Config Key information to better give guidance on features that could be disabled. We can also show AX table sizes and activity by Config Key/Modules. We’ve added some other configuration information that may impact the performance of your Dynamics AX system.
Parsing of QUERY_PLANS
Ever had problems trying to read a very large graphical execution plan? We now parse out that XML into a table called QUERY_PLANS_PARSED. We have broken out all of the table nodes and sorted them by estimated_rows descending. This data is now displayed in new columns of all the QUERY views to make it easier to find those challenging performance issues on large query plans.
All of the database blocking has now been consolidated into SQL Server Extended Events. For now, this matches our current capabilities. We will be enhancing our data collection of Extended Events to introduce new capabilities.
Parameters, Parameters, Parameters
Installation of DynamicsPerf 2.0 is now driven by tables in the database vs. having to edit SQL Jobs. There are so many options now that there is a separate script for configuring them all. The good news is you shouldn’t have to change any of them. The defaults will work well for everyone.
Many, Many new sample queries
There are a significant amount of new sample queries. It's based on all of the new data and capabilities that DynamicsPerf 2.0 gives us to analyze performance.
HOW DO I GET IT?
Rod “Hotrod” Hansen