Troubleshooting that elusive “slowdown” in AX using Performance Analyzer 1.20 for Microsoft Dynamics
It’s 9.30am on Monday morning and you’ve just received reports of a “general slowdown” on Microsoft Dynamics AX. After probing further you find it was all over by 9:15am lasting around 15 minutes, but also happened once before and the users want action taken to prevent it happening again.
There are never any guarantees when doing a root cause analysis of historical issues and that should be explained to your users, but below are some things you can do using the Performance Analyser (aka DynamicsPerf, after the database name) both proactively and reactively to address that type of problem.
Proactive: it uses the DynamicsPerf database to store a history of SQL Server DMV data and other performance related data which can be transient. It also includes perfmon templates (which can be used to monitor for hardware bottlenecks), SQL profiler traces (which can help spot things like blocking and auto growth events), etc.
Reactive: If the issue occurred recently enough, at the point they run, the SQL Server Agent jobs that come with this tool can capture (among other things):
- Event logs from the past 2 weeks, which will include performance related events such as event ID 186 warnings (indicating orphaned SPIDs) and client disconnections (either forced closure of a session or a crash), e.g. event ID 110.
- SQL Server DMVs (including query stats and query plans) since the last SQL Server restart.
- Long query traces (copied from SysTraceTableSQL table into DynamicsPerf), which may allow you to correlate call stacks in the code with SQL queries (if you already had long query tracing turned on) in AX 2012 and previous versions.
Install and run it by following the instructions in the following two blogs in that order:
What to check:
You can use the script below to pinpoint possible root causes of an issue at a point in time, having just installed the Performance Analyser. Some of it is generic so can apply to any application running on SQL Server (on a different server to your AX database of course!)
Finally, once the Performance Analyser is up and running, in case further issues occur and partly as a preventative measure, there is a series of checklists you can use to check general settings, profiler traces, etc., which starts here:
