page contents

Dynamics AX 2012 Performance Troubleshooting step by step


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:

Perfworkflow3

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.

Note: read the section 1 of this blog post to set up this registry key to see the User ID.
Tool: you can download the tool [DynamicsPerf] from  http://dynamicsperf.codeplex.com/

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.

Regards,

@BertrandCaillet
Principal Premier Field Engineer

Comments (3)
  1. Mathieu says:

    Blog posts about Dynamics AX are always welcome. I understand this post is about different tools, but I find it misleading that a Dynamics AX administrator should start from the long queries when “end users are complaining of slow performance”. It is a bad way to instruct System Administrators on how to “investigate reactively to find bottleneck”. The current problem is that System Administrator are looking for performance issues without knowing the business challenges.

    It would be great to see a list of questions or another diagram that would help the business to understand their performance issues. Once that done, maybe “Long Queries” will not be the first place to look at.

    Nonetheless, this blog post does help in troubleshooting performance issues, I just believe it is missing a pre-troubleshooting steps and hence misleading.

    1. Hello Mathieu,

      Totally understand your concern. My blog post was purely written from the Dynamics AX administrator role and based on recent customer onsite where, unfortunately, there is no proactive monitoring of the application. You could say that the Step by Step I wrote should only be the last process to follow. I’ll try to think about another post for proactive monitoring in the future. Also the “Long Queries” is just an indicator: these queries are not always the root cause of bad performance.

      Regards,
      @BertrandCaillet

      1. Mathieu says:

        Thank you / Merci !

Comments are closed.

Skip to main content