Donnerstag, August 02, 2007

Activating query tracing for all users

Sometimes when you are tracking performance (or other) problems, you may want to activate the long running query tracing for all users. Here is a little job that will do that. Beware: you have to activate trace settings on your AOS server, unless most of the queries will not be caught.


static void Set_SQLTrace_AllUsers(Args _args)
{
    #LOCALMACRO.FLAG_SQLTrace                    (1 << 8) #ENDMACRO
    #LOCALMACRO.FLAG_TraceInfoQueryTable         (1 << 11) #ENDMACRO

    boolean     set;
    UserInfo    userInfo;
    ;

    set = true;
    ttsbegin;
    while select forupdate userinfo
    {
        userinfo.querytimeLimit = 1000;
        if (set)
        {
            userInfo.DebugInfo  = userInfo.DebugInfo | #FLAG_SQLTrace;
            userInfo.TraceInfo = userInfo.TraceInfo | #FLAG_TraceInfoQueryTable;
        }
        else
        {
            userInfo.DebugInfo  = userInfo.DebugInfo ^ #FLAG_SQLTrace;
            userInfo.TraceInfo = userInfo.TraceInfo ^ #FLAG_TraceInfoQueryTable;
       }

        userinfo.update();
    }
    ttscommit;
}
um

2 Kommentare:

  1. Unknown8:59 PM

    You may want to also include the query/executionplan plan.
    This in located inside the table SysUserInfo.generateExecutionPlan.

    Like this :

    This function will enable/disable SQL logging on all users in Dynamics Ax 4.0. This is good for logging query performance etc.

    Happy coding

    /kurt

    static void SetTraceOnAllUsers(Args _args)
    {
    UserInfo UserInfo;
    SysUserInfo SysUserInfo;
    #LOCALMACRO.FLAG_TraceInfoQueryTable (1 << 11) #ENDMACRO
    #LOCALMACRO.FLAG_Trace (1 << 0) #ENDMACRO
    #LOCALMACRO.FLAG_SQLTrace (1 << 8) #ENDMACRO
    ttsbegin;
    while select forupdate UserInfo //where UserInfo.id == "KUHA"
    {
    UserInfo.querytimeLimit = 4000;
    UserInfo.traceInfo = UserInfo.traceInfo | #FLAG_TraceInfoQueryTable; //Set the trace
    UserInfo.debugInfo = UserInfo.debugInfo | #FLAG_SQLTrace; //Set the trace
    //UserInfo.traceInfo = UserInfo.traceInfo ^ #FLAG_TraceInfoQueryTable; //Disable the trace
    //UserInfo.debugInfo = UserInfo.debugInfo ^ #FLAG_SQLTrace; //Disable the trace
    UserInfo.update();
    while select forupdate sysuserinfo where sysuserinfo.Id == UserInfo.id
    {
    sysuserinfo.generateExecutionPlan = noyes::Yes;
    sysuserinfo.update();
    }
    }
    ttscommit;
    }

    AntwortenLöschen
  2. Unknown4:47 AM

    else
    {
    userInfo.DebugInfo = userInfo.DebugInfo ^ #FLAG_SQLTrace;
    userInfo.TraceInfo = userInfo.TraceInfo ^ #FLAG_TraceInfoQueryTable;
    }
    =========
    inverting trace info is not good idea

    AntwortenLöschen