Microsoft Dynamics Ax: 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
;
 
ttsbegin;
while select forupdate userInfo
{
    if(enable)
    {
        userInfo.DebugInfo      = userInfo.DebugInfo | #FLAG_SQLTrace;
        userInfo.TraceInfo      = userInfo.TraceInfo | #FLAG_TraceInfoQueryTable;
        userInfo.querytimeLimit = 100;
    }
    else
    {
        userInfo.DebugInfo      = userInfo.DebugInfo ^ #FLAG_SQLTrace;
        userInfo.TraceInfo      = userInfo.TraceInfo ^ #FLAG_TraceInfoQueryTable;
    }
    userInfo.update();
}
ttscommit;

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

The results can be found in the Administration module.

Dynamics Ax SQL Trace

No comments:

Post a Comment