A little, mostly unknown feature in Performance Analyzer for Microsoft Dynamics, http://dynamicsperf.codeplex.com, is the ability to add comments to the database. We have significantly upgraded this functionality in version 1.20 of the toolset. The intent of this functionality is twofold. The first being that this is a way to keep track of tasks that need to be done to tune your Microsoft Dynamics application. The second is to keep a historical record of why a change was made to the database.
By adding a comment to a query, the purge job for DynamicsPerf will NOT delete the statistics for that query. This helps you keep a record of why you made changes to your Microsoft Dynamics system.
Let me demonstrate this functionality for you. In the following screenshot, you can see I have a query that I’m interested in fixing. There is a column just after the QUERY_PLAN column called QUERY_HASH.
For this example, I’m going to add some comments for the first query which happens to have a QUERY_HASH of 0x8A787B4A38C8E22E. To do this, you need to open up the Research Queries in the DynamicsPerf solution.
When you open this query you should see the following:
This table in the DynamicsPerf database contains the following fields:
QUERY_HASH - This is the hash value that you will find in the QUERY_STATS_CURR_VW.
NOTE: Leave this value as 0x00000000000 if you are adding a comment for the AX_SQLTRACE record instead of a QUERY_STATS record.
AX_ROW_NUM – This field is the ROW_NUM from the AX_SQLTRACE table if you want to put a comment in for a record from that table.
NOTE: Leave this value as 0 if NOT commenting a record from this table.
CREATED_ON – Date you created the comment
MODIFIED_ON – Date you updated this comment if you ever update
CREATED_BY – Who created this comment
MODIFIED_BY – Who modified this comment
TICKET_NUM – A wide open text field to enter the Ticket number that you may have created in your helpdesk software
COMPLETED – Y or N, is it complete or not
COMPELETED_ON – Date that you completed the steps necessary to fix this query
STATUS – Wide open text field. You might put who is assigned to do the task that needs done in the comment
COMMENT – Your comment for what action needs to be taken to resolve this issue
Here is an example for a new comment:
By default in 5-Analyze SQL Queries, we filter all queries that have comments out of the result set. This is so that we don’t keep looking at stuff that we have either fixed or will be fixing and work on new queries to tune.
If you want to see these queries that have comments, simply comment out this section of the WHERE clause to see them all. The comment is the last column in the QUERY_STATS views:
In version 1.20 of Performance Analyzer for Microsoft Dynamics, we have enhanced the SSRS report that we have built for comments.
Here is an example of the report once you have added comments into the system.
As you can see this produces a rather nice set of documentation for you. Please take advantage of this functionality as you work on performance tuning your Microsoft Dynamics system.
Core installation instructions can be found here:
Dynamics AX customers can go here for the Dynamics AX components after installing the core components
Also checkout these blogs on DynamicsPerf 1.20 :
Rod “Hotrod” Hansen