Troubleshoot Dynamics AX Performance with SQL Profiler

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:

http://msdn.microsoft.com/en-us/library/aa499181(AX.10).aspx

http://msdn.microsoft.com/en-us/library/aa637896.aspx

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:

http://articles.techrepublic.com.com/5100-10878_11-5054787.html

http://msdn.microsoft.com/en-us/library/ms181091.aspx

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)

ProdTable (After 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): 

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

e.g. http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

http://technet.microsoft.com/en-us/library/ms178071.aspx

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 

http://dynamics-ax.blogspot.com/2009/06/getting-most-out-of-dynamics-ax.html