Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

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