AX Performance – What information and data to collect when you want to open a support case
The aim of this blog post is to provide you with some suggestions on what information and data to collect and provide when opening a Microsoft support case. You can of course use these suggestions in your own organization too, whether you are a Partner or End User of Microsoft Dynamics AX. It’s usually a good idea to have a structured and repeatable approach to data collection and performance troubleshooting, though it can of course be hard to come up with a single template that covers all scenarios. Let’s start with some comments from our In-Market Engineering Team:
TechNet Blogs » Dynamics AX In-Market Engineering » Improve your chances for fast case turnaround
http://blogs.technet.com/b/dynamicsaxse/archive/2013/05/13/improve-your-chances-for-fast-case-turnaround.aspx
Secondly, if you are experiencing general performance issues, you should take a look at this blog post first:
MSDN Blogs > Microsoft Dynamics AX Support > Managing general performance issues in Microsoft Dynamics AX
http://blogs.msdn.com/b/axsupport/archive/2014/09/11/managing-general-performance-issues-in-microsoft-dynamics-ax.aspx
So what can you provide Microsoft support with that will help us expedite your AX performance case from the start?
1. A good problem description
It should include things like background on whether the issue happens often, consistently, intermittently, for specific users only, etc. It’s also usually interesting to hear if the issue has been occurring for a long time, or if it has started happening recently, and if there have been any recent changes or not. If you are having problems with a specific form or process, and you have identified some X++ call stacks, please provide that information. We are also interested in your observations and ideas when it comes to a possible cause. Finally, if you have made customizations to the area you are seeing issues with, that is of course relevant, and we’d also be interested to know if you have an unusually large amount of data in the underlying tables being queried.
TIP:
Even if you’ve spent a lot of time putting together a very comprehensive problem description written in a Microsoft Word document, please ensure that there are a few lines in the case description in clear text that outline the main issue, rather than simply writing “see attached document”. This will allow us to more quickly route your case to an appropriate Engineer. Almost any performance issue can usually be summarized in a sentence or two, regardless of complexity, in my experience. This is part of the normal case SCOPING process we follow here at Microsoft Support.
2. AX build numbers
These are a must. We need to know your AX kernel build and your AX application build. We may also need more insights into individual fixes you may have installed.
To check your current AX builds, go to:
AX CLIENT / HELP / About Microsoft Dynamics AX
e.g.
3. AX traces
AX traces are usually a great source of information when it comes to troubleshooting a performance issue.
AX 2009
To capture AX traces on AX 2009, you use the Microsoft Dynamics AX Server Configuration Utility on the AOS and the Dynamics AX Configuration Utility on a client.
AX 2009 – Tracing
https://technet.microsoft.com/en-us/library/aa496440(v=ax.50).aspx
AX 2009 – Set tracing options
https://technet.microsoft.com/en-US/library/aa569614(v=ax.50).aspx
AX 2012 RTM / R2 / R3
When it comes to AX 2012 onwards, I prefer to use PERFMON to capture AX traces personally, as once it has been set up, all you have to do is press PLAY / STOP in PERFMON and you will get consistent data capture with minimum effort.
MSDN Blogs > Dynamics Ax Performance Team Blog > Collect AX 2012 event traces with Windows Performance Monitor
http://blogs.msdn.com/b/axperf/archive/2011/11/18/collect-ax-2012-event-traces-with-windows-performance-monitor.aspx
Alternatively, you can use the Tracing Cockpit form.
Tools for monitoring performance [AX 2012]
https://technet.microsoft.com/en-us/library/jj149695.aspx
4. DynamicsPerf database backups
Installing and running Performance Analyzer for Microsoft Dynamics (DynamicsPerf) also has many benefits, and the tool can be very helpful in establishing the cause of a performance issue.
There are some good blog posts out there when it comes to DynamicsPerf so please take a look at those, e.g.:
MSDN Blogs > Microsoft Dynamics AX Support > Troubleshooting that elusive “slowdown” in AX using Performance Analyzer 1.20 for Microsoft Dynamics
http://blogs.msdn.com/b/axsupport/archive/2015/02/18/troubleshooting-that-elusive-slowdown-in-ax-using-performance-analyzer-1-20-for-microsoft-dynamics.aspx
MSDN Blogs > Dynamics AX in the Field > All Tags > performance analyzer for dynamics
http://blogs.msdn.com/b/axinthefield/archive/tags/performance+analyzer+for+dynamics/
The tool itself can be downloaded here:
Performance Analyzer for Microsoft Dynamics
http://dynamicsperf.codeplex.com/releases/view/122779
If you install DynamicsPerf then you can also set up the following:
AX Long Query Tracing
If you aren’t able to capture an AX trace for some reason, you may be able to set up long query tracing and establish what X++ call stacks and SQL statements are taking time to run.
Tracing with the Tools Menu [AX 2012]
https://msdn.microsoft.com/en-us/library/aa854677.aspx
MSDN Blogs > Dynamics AX in the Field > How to Monitor for Long Running Queries in AX
http://blogs.msdn.com/b/axinthefield/archive/2011/06/09/how-to-monitor-for-long-running-queries-in-ax.aspx
I would usually suggest that you establish a reasonable threshold and log the information to a database table.
This is also covered in the DynamicsPerf documentation:
MSDN Blogs > Dynamics AX in the Field > Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation
http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide-dynamics-ax-installation.aspx
TIP:
Once this has been enabled, we should hopefully capture some long running queries in a table in the AX 2012 RTM / R2 / R3 database called SYSTRACETABLESQL.
To see how may rows the table contains, run this query:
SELECT COUNT(*) AS myRowCount_SYSTRACETABLESQL FROM SYSTRACETABLESQL
GO
Once you’ve captured some data, you can run something like this to collect the top 50 slowest queries into a new table in a new database, which you can then take a backup of and send to Microsoft for further analysis.
— YOU ONLY NEED TO RUN THIS ONCE TO CREATE A NEW EMPTY DB
CREATE DATABASE DB_MSFT_LONG_QUERIES
GO
— Top 50 slowest queries taking more than a second to run
SELECT TOP 50 [DATAAREAID], [CREATEDDATETIME] , [CREATEDBY], [TRACETIME], [CALLSTACK], [STATEMENT]
INTO DB_MSFT_LONG_QUERIES.dbo.MSFT_LONG_QUERIES_TOP_50
FROM SYSTRACETABLESQL
WHERE [TRACETIME] >1000
ORDER BY TRACETIME DESC
GO
We can then run queries like this to filter the data further, for example:
SELECT *
FROM DB_MSFT_LONG_QUERIES.dbo.MSFT_LONG_QUERIES_TOP_50
WHERE CALLSTACK LIKE ‘%SalesTable%’
GO
e.g.
The BACKUP to DISK command would look something like this (ensure you can write data to the path used of course):
BACKUP DATABASE [DB_MSFT_LONG_QUERIES]
TO DISK = N’C:\TEMP\DB_MSFT_LONG_QUERIES.BAK’ WITH NOFORMAT, NOINIT,
NAME = N’DB_MSFT_LONG_QUERIES-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
SQL Server Profiler traces
Profiler traces can also provide valuable insights, though it is important to be able to tie the SQL statements back to an X++ call stack in the majority of AX performance scenarios. A Profiler trace is usually most useful in blocking scenarios, but again, you may need an AX trace too in order to be able to tie back the information displayed in the blocked process report, for example, in order to establish what X++ has generated the blocking SQL statement.
This blog post covers some of specifics when it comes to using SQL Server Profiler in blocking scenarios.
MSDN Blogs > Microsoft Dynamics AX Support > AX Performance Troubleshooting Checklist Part 2
http://blogs.msdn.com/b/axsupport/archive/2014/09/08/ax-performance-troubleshooting-checklist-part-2.aspx
The documentation that DynamicsPerf comes with covers how to set it up, as do numerous other general SQL Server blogs and walk-throughs.
Performance Monitor traces
Perfmon traces can be useful too, depending on scenario, and can be set up manually or in conjunction with installing and running DynamicsPerf
Set up Performance Monitor counters [AX 2012]
https://technet.microsoft.com/en-us/library/aa834416.aspx
For more information on how to set this up and use PAL to analyze the data, see this blog post:
MSDN Blogs > Microsoft Dynamics AX Support > AX Performance Troubleshooting Checklist Part 2
http://blogs.msdn.com/b/axsupport/archive/2014/09/08/ax-performance-troubleshooting-checklist-part-2.aspx
The DynamicsPerf tool ships with a Perfmon template that you can use to collect AOS performance counters, for example, called Server2008_AOS.xml.
5. LCS Cloud Powered Support with Repro VM
Finally, and perhaps most importantly, if you have found a performance issue that can easily be reproduced on standard AX, you should consider using LCS to log a case and provide a repro VM where you can illustrate the issue and record a repro video of it for an Engineer to look at and troubleshoot
Cloud-powered support (Lifecycle Services, LCS) [AX 2012]
https://technet.microsoft.com/en-us/library/dn715995.aspx