In this post I want to share how we analysis load test runs for Microsoft Dynamics 365 for Finance and Operation on SQL level. Because a load test is required from Microsoft for most of our projects and we have to answer some question regarding load testing in the pre Go-Live checklist, we try to standardize how we analyze the results from a load test run.
One main area we have to analyze is the usage and utilization of the SQL. In this area we normally analyze the following aspects:
DTU usage during the load test run
We need to know if the SQL was on its edge during the run or if there is room for more load and if the DTU utilization was too high, we have to analyze further. To find out how high the DTU utilization was you can use LCS environment monitoring or you build up your own reporting e. g. by using PowerBI. To get a feeling what is possible with PowerBI, please check out the following picture.
The next aspect we have to analyze are missing indexes. For this task we normally use the command which you can find behind the following link: https://github.com/PaulHeisterkamp/d365fo.blog/blob/master/Tools/SQL/MissingIndexes.sql
The results from the command have to be analyzed further. You can not only copy the result and create all indexes. We normally check the suggested indexes with the following patterns:
- Are there any missing columns in the where clause which we can add to hit an existing index.
- Does the missing index include the columns DataAreaId and Partition? If not this is an indication for a wrong select statement which normally comes from an computed (view) column.
- Check the execution count and the last execution time of the statement behind the suggested index. If the execution count is low and/or the last execution time is long ago, I would skip the suggestion.
- Last but not least, found down sides of the index e. g. to many columns which will cause longer write duration.
Top resource consuming queries
Under Performance metrics in LCS Environment monitoring – SQL Insights you can find some aspects of the SQL Query Store. You can find the KPIs Duration (ms) / Logical IO / CPU Time (ms) / Execution Count / Total wait time (ms), but unfortunately only aggregate as “Total”. If you have a huge load test run you can only use the total execution count to find “hidden scans”. For all other/further analysis I would use the SQL Query store directly via SQL Server Management Studio, to aggregate the KPIs by Avg and Min/Max to find heavy SQL statements for further analysis.
The next aspect are deadlocks during the run. You can find them in LCS under Environment monitoring – Activity – All deadlocks in the system. If you get results from the query you can use the provided stack trace to analyze the root cause further.
The last (but not least) area of investigation are slow queries. You can find them in LCS under Environment monitoring – Activity – Slow queries. I think you will always get a result for this query and you have to check if the duration is in an acceptable range. If not you can use the provided stack trace to analyze the root cause e. g. a missing index or a suboptimal SQL statement. In our case we always see queries related to the batch orchestration framework, which we usually do not analyze further.
I´m very interested in your comments on the approach and I would love to hear what analyzation aspects you would add.