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.

 

image

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.

 

image

 

When you open this query you should see the following:

 

image

 

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:

image

 

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.

image

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:

image

 

In version 1.20 of Performance Analyzer for Microsoft Dynamics, we have enhanced the SSRS report that we have built for comments.

image

Here is an example of the report once you have added comments into the system.

image

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:

http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide.aspx

 

Dynamics AX customers can go here for the Dynamics AX components after installing the core components

http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide-dynamics-ax-installation.aspx

 

 

Also checkout these blogs on DynamicsPerf 1.20 :

 

Baselines with DynamicsPerf 1.20 http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/baselines-with-performance-analyzer-for-microsoft-dynamics-dynamicsperf-are-here.aspx

Using DynamicsPerf in Dev/Test/QA  http://blogs.msdn.com/b/axinthefield/archive/2014/06/05/using-performance-analyzer-for-microsoft-dynamics-in-a-development-test-or-qa-environment.aspx

 

 

Rod “Hotrod” Hansen