Dynamics AX Performance Troubleshooting Step by Step
In this blog post, I would like to summarize the different tools that one Dynamics AX administrator can leverage to troubleshoot performance issues. The typical situation is when end users are complaining of slow performance when using the Dynamics AX application and you are being asked to investigate reactively to find bottleneck.
Here is the typical workflow with the major steps:
Let's now describe each step of the workflow:
1_Open the SQL Server Management Studio and look at the running queries
Open the [Performance Analyzer for Dynamics solution] and run the query [Quick - Activity Monitor with Cursor Support]. You will see the queries that are currently running with the status like "executing" or "suspended". First, you can look at the queries with top "Total Elapsed Time", in milliseconds. If you edit the registry key of the transactional AOS servers, you will see the User ID running the query, as well as the AOS server.
2_Detect the blocked queries and read the database events
From the first query [Quick - Activity Monitor with Cursor Support], you can also see the queries that are being blocked by running [EXEC SP_LOGLOCKS]. For every blocked statement you can also see the blocking statement with all related information.
If the SQL Trace has been setup properly in DynamicsPerf, you can execute the following query to find the most recent events that occurred on the databases such as: Block Process Report, Lock Escalation, Deadlock and also data or log file auto growth.
SELECT top 25 F.StartTime, F.Duration, F.EventClass, F.TextData, F.DatabaseName, F.HostName, F.ApplicationName FROM fn_trace_gettable(‘C:\SQLTRACE\DYNAMICS_DEFAULT.trc’, DEFAULT) F, sys.trace_events E WHERE EventClass = trace_event_id order by StartTime desc
Note: You can execute the DynamicsPerf SQL Job [DYNPERF_Optional_Polling_for_Blocking] to capture more information in the BLOCK table of the DynamicsPerf database.
3_Investigate the Dynamics AX Long Running queries
When Long Running queries are enabled in the application for all users, you will certainly capture traces in the SysTraceTableSql of the Dynamics AX database. From SQL Server Management Studio, you can execute the following query to find the last 25 kernel traces with the User ID and X++ Call Stack:
Select top 25 CREATEDDATETIME, CREATEDBY, TRACETIME, STATEMENT, CALLSTACK from SYSTRACETABLESQL where TRACETIME > 0 order by CREATEDDATETIME desc
Here you can already detect if the X++ Call stack is related to execution of reports, database logging, number sequences or customization.
Note: You can read the section 4 of the following blog post to enable the Dynamics AX Kernel trace with different threshold, from 2 to 5 seconds for example.
4_Review the Reporting Services Log if the call stack is related to report execution
When execution of reports is potentially the root cause of performance, you can directly query the ExecutionLog3 view of the ReportServer database. You will be able to see following details for every report execution: TimeStart, TimeEnd, Time Processing, TimeRendering.
Note: you need to enable the "EnableExecutionLogging" value of the SQL Server Reporting Server property.
5_Check the resource contention with Windows Performance Monitoring
At this stage, it is time to also check the usage of all resources for all critical server roles such as SQL Server, AOS Server, Reporting Server and Terminal Server. When looking at Windows Performance Monitoring, you can for example verify latency of the Disks especially for Data and Log files of Dynamics AX and TempDB databases, availability of the Memory, utilization of the processors and networking bandwidth.
Note: Please read this blog post to set up the Windows Performance Monitoring.
6_Look at the Dynamics AX AOS Event Log
When you have many Dynamics AX AOS Servers, you can use DynamicsPerf to easily collect the events. The VBS script AOSANalysis.CMD will automatically retrieve all Dynamics log from all AOS where there is an active Dynamics AX AOS configuration, since 14 days.
Then you can execute the following query from DynamicsPerf to find the latest events:
Select top 25 * from AOS_EVENTLOG order by Time_Written Desc
7_Capture Dynamics AX Event Trace
Finally, if the root cause of the performance is still not found, you can capture the Dynamics AX Event Trace on the AOS server where the User complaining about performance is connected to.
To capture the trace, you can start the Event Tracing for Windows (ETW) within Windows Performance Monitoring or using PowerShell script. You should only run the trace for few minutes because it collects all the sessions connected to the AOS and its size can easily reach couple of GB.
Then you can import the trace into Trace Parser and look for top Call stacks and SQL Statements order by duration and count.
The tools and the scripts mentioned in this blog post are compatible with all versions of Dynamics AX 2012 (running on LCS or On Premise) with the DynamicsPerf version 1.20.
We will write the same workflow for the new Dynamics AX (On Line) when the DynamicsPerf version 2.0 will be released to market.
Principal Premier Field Engineer