Does Dynamics AX have any built-in functionality for monitoring system performance? This is a question I frequently get asked, so I thought I would address it here with a quick explanation of one of the tools that AX has had for a long time but very few people know about and use. I call it a tool, but it’s really just a feature. For lack of a better name I’ll call it the “SQL statement trace log” feature. What this feature allows you to do is track all of the SQL statements that have durations longer than a certain user defined threshold. This might not sound all that exciting at first because we’ve been able to do that in SQL Server forever. Most database administrators already have that information either through the use of the SQL Server Dynamic Management Views (DMVs) or SQL Server Profiler. So why is this an important feature to know about? The answer is this gives you a very important set of information that SQL Server can’t provide. I’m certainly not suggesting this tool can replace the SQL Server DMVs or Profiler, but I do think it can help fill in some of the gaps those tools have. The top 3 reasons you might find this tool useful are:
Now that you know why you might want to use it, it’s time to set it up. There are really only 2 steps:
1. Enable tracing on each AOS by marking the checkbox listed below. Each AOS service must be restarted for the change to take effect.
Microsoft Dynamics AX Server Configuration Utility > Tracing Tab
2. Enable tracing per user within the Dynamics AX application. The following settings control what information is collected. The three options circled in red are the ones I use.
Administration > User Options > SQL
The only bummer with this setup is that it’s per user. If you want to set it up for all users as I often do, it can be time consuming. Thankfully there’s a stored procedure (SET_AX_SQLTRACE) which is included in the Performance Analyzer for Microsoft Dynamics tool that can be used to automate the setup when you have lots of users to configure. This can save a lot of time when setting up tracing globally. See the following post for more details on installing the Performance Analyzer for Microsoft Dynamics tool http://blogs.msdn.com/b/axinthefield/archive/2011/02/28/setting-up-performance-analyzer-for-microsoft-dynamics.aspx.
Once you have tracing setup, all you have left to do is review the results. You can see the results in the Dynamics AX client (Administration > Inquiries > Database > SQL Statement Trace Log) or query the results directly from the SYSTRACETABLESQL table.
Whenever you log information like this, you need to have some sort of plan for how much data you want to retain. You can purge the data through the client (Administration > Inquiries > Database > SQL Statement Trace Log > Functions > Clear Log), but there’s no logic in that routine to allow you to keep a subset of the trace data. If you want to retain a subset of the trace data based on created date or some other criteria, you’ll have to create your own custom purge process.
Watch it friends