03 October 2016

Dynamics AX Trace Parser vs SQL Server Profiler

While I was working on the previous post, I noticed a strange difference between data presented in Microsoft Dynamics AX Trace Parser and SQL Server Profiler. Let's run a couple of tests.

Problem description
Trace the following select statements in Tracing Cockpit and SQL Server Profiler and compare the results:
select AccountNum from custTable;
select firstOnly AccountNum from custTable;
Solution
Setup Tracing Cockpit to collect detailed database data:
Setup SQL Server Profiler to trace RPC:Starting and RPC:Completed events:

Let's run the first select statement. The results in Trace Parser are:
20 rows were fetched. The results in SQL Server Profiler are different - 10 rows were fetched into buffer:
What is correct and what is wrong? To be honest, I don't know for sure. Maybe the tools collect different data.

Let's run the second select statement with firstOnly keyword. I expect 1 row to be fetched. The results in Trace Parser are:
0 rows were fetched. It looks very strange. Definitely 1 record was fetched into a table buffer in the job. The results in SQL Server Profiler look better - 1 row was fetched into buffer:

Conclusion
I personally trust more SQL Server Profiler because of the much longer product history. Once again it can be that the tools present different data, but starting from now I will be more cautious about the data in Trace Parser.
Please share your thoughts and expereience on this topic.

No comments:

Post a Comment