How to Monitor for Long Running Queries in Microsoft Dynamics AX

avatar of premier-field-engineersPremier Field Engineers0

Written by Tom Stumpf, Microsoft Premier Field Engineer.
Microsoft Dynamics AXDoes Microsoft 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:

  1. 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.
  2. 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.
  3. 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:

  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

Dynamics AX Server Configuration Utility tracing

 

  1. 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

Dynamics AX 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.


Comments (0)

Skip to main content