Consulting, Dynamics AX Performance
Trick to see the user, the x++ code, and the SQL responsible for whatever you need to solve in Dynamics AX
12087 
Jul
There are times when you need to get to the bottom of a performance or troubleshooting problem. Trust me, I’ve been there (sometimes, I feel like I’m there every week, but that is another story). So, I thought I would share one of my most revered tricks for being able to see everything fast.
Let’s say that we hear from one particular user that the system is slow or crashing. So, our goal is to figure out what they were running when they experienced the problem, so we can figure out the cause of the problem. What we need is a full-blown X++/SQL trace to tell us everything. Believe it or not, this is easy to do.
First, you need to run a SQL Statement. What this does is turn on x++ SQL tracing for a specific user.
For example, here is a statement that you could run to trace all sql statements for an AX user with an ID of ‘BigDAddySoonerFan’.
Update
USERINFO
Set
DebugInfo
= 268,
TraceInfo
= 2048
where
ID
=
‘BigDaddyOklahomaSoonerFan’
What this does is turn on x++ SQL tracing for a specific user. Or, let’s say that you wanted to trace all SQL Queries over 60 seconds and the users who experiences it along with the ax code running at the time.
Update
USERINFO
Set
QUERYTIMELIMIT
=
’60’,
DebugInfo
= 268,
TraceInfo
= 2048
Now, once you are done with that, it will actually turn on tracing. This takes all of 15 seconds to do. To see the output, AX keeps things in a special table called SysTraceTableSQL.
Run the statement: Select * from SysTraceTableSQL.
CAN I GET A HELL YEAH!!!!!!!
Okay, but just don’t forget to turn it off after you are all done figuring out what you needed. Setting the debuginfo back to 12 and Traceinfo to 0 gets you back to the default so the Systracetablesql doesn’t get too big. I’ve also purged that one if it was too big.
Update
USERINFO
Set
DebugInfo
= 12,
TraceInfo
= 0
where
ID
=
‘BigDaddyOklahomaSoonerFan’
This is one of those AX tricks that I’ve used a million times. Enjoy.. For those of you who want to correlate this trace with SQL profiler, notice that the SPID is also there. As I said, a very powerful method.
