MS SQL Server performance with Dynamics Ax


Lately I’m working on some projects who experience a bad performance in Dynamics Ax. This can have multiple causes but one of the most important is maintaining the SQL Server. In my humble opinion every Dynamics Ax developer should have a basic knowledge on how SQL Server works and is maintained because it is the backbone of our beloved ERP software. :-).

This said if terms like rebuilding and reorganizing indexes, updating statistics or recovery model don’t ring a bell these links will be very interesting for you ;-).


Dynamics Ax SQL Trace

Hi there,

here is a simple job to enable SQL tracing for all your users, this quite handy for optimizing queries. (The macro’s for modifying other fields on the UserInfo table can be found on the ClassDeclaration of the SysUserSetup form.)

boolean     enable      = true;
UserInfo    userInfo;
#LOCALMACRO.FLAG_SQLTrace                       ( 1 << 8 ) #ENDMACRO
#LOCALMACRO.FLAG_TraceInfoQueryTable            ( 1 << 11 ) #ENDMACRO
while select forupdate userInfo
        userInfo.DebugInfo      = userInfo.DebugInfo | #FLAG_SQLTrace;
        userInfo.TraceInfo      = userInfo.TraceInfo | #FLAG_TraceInfoQueryTable;
        userInfo.querytimeLimit = 100;
        userInfo.DebugInfo      = userInfo.DebugInfo ^ #FLAG_SQLTrace;
        userInfo.TraceInfo      = userInfo.TraceInfo ^ #FLAG_TraceInfoQueryTable;

Make sure that client tracing is enabled in the server configuration. (Only use this in development and testing environments, tracing may affect the AOS performance.)

Server configuration
Server configuration

The results can be found in the Administration module.

SQL trace menu
SQL trace menu