Written by Tom Stumpf, Microsoft Premier Field Engineer.

The top 3 reasons you might find this tool useful are:
- Durations that include round-trip times from the AOS. SQL Server can provide you with excellent details not available in this trace such as execution plans, IO details, etc, but this trace provides more realistic times as it pertains to the user experience because it includes the time it takes the AOS to request information and get the results back across the network.
- Very little overhead and maintenance: When configured with a reasonable threshold, this this type of tracing can be very lightweight and can be left on for long periods of time without concern for how it affects overall system performance.
- Who, what, where, when? And best of all, this trace can help answer these difficult questions because of the additional information tracked along with the SQL text and duration. Here’s a quick list of the additional information I find most useful:
- Call Stack: The name of the form, report, or class that executed the SQL statement.
- AX User ID: The name of the user that opened the form or report referenced in the call stack.
- Date/Time: When the SQL statement executed.
Setup
Now that you know why you might want to use it, it’s time to set it up. There are really only 2 steps:
- 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
- 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.
- Marking the "SQL trace" option enables tracing for this user.
- The "threshold" setting controls how long a query must take before information is collected about it. Note that this is in milliseconds, so 2000 = 2 seconds. 2000 seems to be a good place to start. If you want to go below that mark, you’ll have to watch carefully to see how much logging is happening. Make sure you're not overloading they system with log events by having a threshold that's too low.
- Marking the "table" checkbox tells the application to log the information to the database in the SYSTRACETABLESQL table.
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. Check out the the following for more details on installing the Performance Analyzer for Microsoft Dynamics tool.
Results
Once you have tracing set up, 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.
Maintenance
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.
Editor’s Note: if you want to check out more great Dynamics AX content by Microsoft Premier Field Engineers, make sure you visit Dynamics AX in the Field.