Performance Analyzer for Microsoft Dynamics (DynamicsPerf) is built to help resolve performance issues with Microsoft Dynamics products. As currently released it does a very good job at finding the following issues:
- SQL Configuration
- Database Schema (indexes)
- Application Code (limited)
- Poorly executing SQL statements
Given all of this, there were still questions that were consistently asked that we couldn’t answer very well. One of the biggest challenge for SQL server deployments is that sudden slowdown. There are many reasons for this to occur many of them documented on this blog. Knowing why it happens isn’t always enough to be able to help identify the problem.
So the question is, how to identify this type of issue easily. The answer is compare current query data to a Baseline so that we can see what queries are executing worse than the Baseline. This is the new feature in Performance Analyzer for Microsoft Dynamics 1.20. We have added a new SQL Job for capturing Baseline.
This job prefixes the word ‘BASE’ to the RUN_NAME that is generated during the capture. This was done for 2 reasons. Firstly, if you are doing a Baseline you will want to keep that data for longer than the 2 week data retention policy that we use in DynamicsPerf database. So any capture that starts with ‘BASE’ will not be deleted from the DynamicsPerf database by the purge job. The second reason for the RUN_NAME change is a technical internal issue to how we captured data that very few people realized nor need too. When we run capturestats, we only collected data for queries that have run since the last time we collected data. So there was a technical challenge in supporting Baselines in that if your normal capturestats job had run and you do a Baseline 5 minutes later, the only query data that would be captured would be queries that had run in that 5 minute period. We have changed this behavior for Baseline captures so that they actually capture all of procedure cache for that database.
When should I do a Baseline?
Firstly, you will want your system warmed up so that at least most of your normal day to day operations or month end operations have been run and are in procedure cache. You would actually want to create a Baseline sometime during month end and another one during normal day to day operations. The next time that you would want to do Baselines are right before you make any significant system changes to your Dynamics or SQL Server deployments such as Service Packs or Trace Flags as an example.
How does it work?
When you are having a significant slowdown, you simply run the normal Dynperf_CaptrueStats job to immediately collect data. Once you have done this, there are a set of sample queries in 7-Baseline-Benchmark Queries to assist you in the analysis.
There are basically 3 variations of the queries:
- Queries slower than the Baseline
- Queries faster than the Baseline
- Queries not in the Baseline
The first query sorts the list by %change in Avg_Elapsed_Time. This is done because a query that goes from 1ms to 3ms maybe more important than a query that went from 10ms to 15ms. One might go but the 2nd query got 5ms slower vs. 2ms slower for the first. The answer is it really depends on how many times the queries were executed as to which is the one that needs more attention. The good news is all you have to do is order the results from the difference in Avg_Elapsed_Time and you can look at the data both ways.
NOTE: This process also allows you to identify those queries that are getting worse over time. These tend to be queries that are scanning the database although there are other causes as well such as poor selectivity.
The second query actually allows us to answer another question that gets asked a lot. We made changes to the system but did anything get better. This second query is designed to answer that question.
The third query is to answer the question, “Is there anything new running?”. Now there is more than 1 reason why a query didn’t exist in your Baseline when you captured it, so don’t completely count this as new code. Let’s just say these are queries that weren’t in procedure cache when we did the Baseline. But this is still useful because we can see these new queries and determine if any of them are impacting our system performance.
CAUTION: There are many reasons for query performance going up and down naturally. Server load, Virtualization, and Power Management being just a few. This process is designed to help us find those poor ones that is causing issues for our users.
Please download Performance Analyzer for Microsoft Dynamics at :
The installation guide can be found here:
Don’t forget to create a BASELINE !!
Rod “Hotrod” Hansen