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;
}

Kommentare:

Kurt hat gesagt…

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;
}

Andrew hat gesagt…

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