Bug in DynamicsPerf views

If you have used DynamicsPerf, you may have come across views to analyse Dynamics AX database for performance issues. Two of these views are

• MISSING_INDEXES_CURR_VW 
• QUERY_STATS_CURR_VW. 

As the name implies, these views gives insight into the missing indexes or query stat for the last run of the capture stat job (hence the word CURR in the name). There are similar views without the CURR word which shows data for all runs of capture stat job.

In the latest version (1.2) there is a bug in the views resulting in these showing all data instead of the last run of capture stat job. To fix the bug change the views and add the highlighted lines

           INNER JOIN STATS_COLLECTION_SUMMARY S2 WITH (NOLOCK)  
         ON QS2.STATS_TIME = S2.STATS_TIME  
          AND QS2.DATABASE_NAME = S2.DATABASE_NAME AND S2.RUN_NAME NOT LIKE 'BASE%'  
                      AND QS2.STATS_TIME = (SELECT MAX(STATS_TIME)  
                  FROM  STATS_COLLECTION_SUMMARY)  
     GROUP BY QS2.DATABASE_NAME,  

Note: Similar changes may be required in other views as I haven't had a chance to use all of these.

This posting is provided "AS IS" with no warranties. Use code at your own risk.