Issue:
Recently, we had an issue with a production schedule report running in Dynamics AX PROD Environment, which was taking around more than 30-40 mins to display on the screen. The same report used to come in 2 mins or 3 mins time before. This is a critical running report for the business and a performance delay on this report means a huge impact on the business.
We initially tried with AOS/SQL restart but still this issue used to pop-up every 2 days in a week and since this is a 24 * 7 system, we didn't have the luxury to do a SQL/AOS Restart as it impacts negatively production hours.
Mentioned below, are a series of steps which we followed to resolve this issue.
Troubleshooting Steps:
Using Code Profiler (20%)
i. Walk through the code for the report using Code Profiler Tool available in Dynamics AX. This is a handy tool to start with, when debugging performance issues. To know more about Code Profiler, read these links:
This helped to find out the object methods(standard and customized) ones which were taking the longest duration to execute.
ii. With the aid of code profiler and anlaysing the report code, helped us in the following:
- Removed some redundant code from report methods
- Re-wrote one report method as table methods
This helped us in achieving 20% + in performance, The rest 80% (the considerable part) of the performance improvement came in form of adding indexes, defragmentation of indexes and updating statistics on the table (Thanks to SQL Profiler), which is demonstrate in the next steps
Using SQL Profiler (80%)
Using SQL Profiler, helped us in the following:
- Created missing indexes on some of the AX tables used for the report
- Rebuilt and Reorganized some of the indexes
- Update statistics on the tables used for the report
To know more about SQL Profiler (Introduction), these links should be handy:
i. Created a new SQL Profiler Trace Template for troubleshooting production schedule report based on the Duration and Tuning default templates and filtered the report columns for database and Application name as Microsoft Dynamics AX shown below:
ii. Adding the filters
iii. Saved the trace to a table to enable troubleshooting on the queries which was taking longer duration.
iv. Queried the Trace table to extract long running queries and the table objects associated with it. The below Execution plans show the improvement in queries before and after indexing for each of the tables.
Also, to be noted below in the execution plans are the index lookup, Initially the lookup used to be from RID or bookmark lookup, but after indexing, the scan is done using the index lookups which
overall improves the query performance.
Below are the 3 table objects, identified by SQL profiler which needed some additional indexes. The below graphical plan can be generated in SQL Query Analyzer by using the Show Estimated Plan (this comes very handy when it comes to understand more from an index point of view, which index is benefitting the query and which is not)
Custom Table (Before Indexing)
After Adding Index
SalesLine Table (Before Index)
SalesLine Table (After Index)
ProdTable (Before Index)
In all the above cases, one can see that Estimated cost of the query has come down after adding the required indexes.
Estimated Subtree Cost is generated via the Query Execution plan which tells us the total cost to the query optimizer for executing all the operations within the same subtree. The lower the cost the better for the performance of the query.
Run Rebuilt and Update Statistics Queries for the following tables: (Initially, run this in your TEST/UAT environment which has a similar data load (mimics to PROD), this will help in analyzing the performance results before running it in PROD.
Performance Metrics (Before Adding new index)
| Tables | Duration (microseconds/seconds) | Estimated SubTree Cost |
| ProdTable | 976664/0.97 | 0.5911 |
| SalesLine | 8381053/8.38 | 10.200 |
| MPRPPCComponents | 1158199/1.15 | 4.8435 |
Performance Metrics (After Adding new index)
| Tables | Duration (microseconds/seconds) | Estimated SubTree Cost |
| ProdTable | 20439/0.02 | 0.0038 |
| SalesLine | 1104950/1.10 | 0.7177 |
| MPRPPCComponents | 50442/0.05 | 0.0068 |
Note – Duration column here is the time taken in microseconds by long running queries in the report which uses these tables. So the lower the duration the better the query efficiency.
Similarly, Estimated Subtree Cost is generated via the Query Execution plan which tells us the total cost to the query optimizer for executing all the operations within the same subtree. The lower the cost the better for the performance of the query.
Performance metrics (Before & After Index Defragmentation – Rebuilt and Reorganize indexes):
Similarly, Estimated Subtree Cost is generated via the Query Execution plan which tells us the total cost to the query optimizer for executing all the operations within the same subtree. The lower the cost the better for the performance of the query.
Performance metrics (Before & After Index Defragmentation – Rebuilt and Reorganize indexes):
Before:
Tables | Average Fragmentation on all Indexes (%) |
Salesline | 92.68 |
InventSum | 114.29 |
InventDim | 164.04 |
ProdTable | 264.46 |
InventTable | 280.49 |
MPRPPCComponents | 127.50 |
After:
Tables | Average Fragmentation on all Indexes (%) |
Salesline | 11.22 |
InventSum | 0.61 |
InventDim | 4.42 |
ProdTable | 15.68 |
InventTable | 7.73 |
MPRPPCComponents | 0.52 |
Note – Average Fragmentation % here is the fragmentation percentage (out-of-order pages in index or heap). Some additional notes on this - SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
One can refer SQL Books Online.chm to delve more into Query Execution Plan, Index Fragmentation etc. They have a detailed explanation about each topic. Also, there are very good links to resources on www
To conclude, SQL Server Profiler is a very handy tool which has come to my rescue number of times, when troubleshooting performance issues with Dynamics AX. Also, I would suggest to go through some valuable posts on performance by Brandon in his blog. There are series of posts which talks about perfromance issues
