Identify long-running queries in Dynamics AX

Often there is a blanket statement “the system is slow” in various Dynamics AX 2012 installations. Most of the time this note is without concrete evidence.
The “SQL statements trace logging” function (System Administration -> Queries -> Database -> SQL Statement Trace Log) and the “SysTraceTableSQL” table behind it can provide more detailed information about long-running database operations of all user sessions.
The form provides information about user ID, called object, associated SQL query, date and time of the executed action. This way, performance critical parts of the application can be quickly identified and adjusted.
The Threshold parameter allows you to set a limit in milliseconds for each user in the Options (File -> Tools -> Options) when records are written in the table “SysTraceTableSQL”.

This setting can be set cyclically and automatically for new users by means of an SQL script in the “Performance Analyzer for Microsoft Dynamics 1.20”, also called DynamicsPerf: Enable Long Running Query Capture for AX.
Note from the Microsoft performance team: For reasons of performance, the setting for the trace protocol of the SQL statements should not be activated for a longer period of time in a productive environment.

Teilen per
<< back to news