page contents

Improving performance in the DynamicsPerf benchmark sample queries


In Performance Analyzer for Microsoft Dynamics, we a script called 7-Benchmark Queries.  The first set of sample queries in this script helps you answer the questions “What change?”.  It does this by showing you the hourly difference in row counts on your tables. 

 

As part of developing our next version of Performance Analyzer for Microsoft Dynamics we have been looking into performance issues with the [PERF_HOURLY_ROWDATA_VW].  To address this, you can apply the following script to either version 1.15 or 1.16 of Performance Analyzer for Microsoft Dynamics. 

 

This must be run in your DynamicsPerf database.

 

Alter VIEW [dbo].[PERF_HOURLY_ROWDATA_VW]

AS

  WITH ROWSRANK

       AS (

 

SELECT A.STATS_TIME,

                  A.DATABASE_NAME,

                  A.TABLE_NAME,

                  A.ROWS_LAST_HOUR,

                  DENSE_RANK() OVER (partition BY A.STATS_TIME ORDER BY A.STATS_TIME DESC, A.DATABASE_NAME DESC, ( ROWS_LAST_HOUR) DESC) AS RANK

                

                 FROM

               ( 

SELECT TOP 100 PERCENT E.STATS_TIME,

                  E.DATABASE_NAME,

                  E.TABLE_NAME,

                  E.ROW_COUNT - START.ROW_COUNT                                                                                                          AS ROWS_LAST_HOUR

                         FROM   PERF_INDEX_DETAIL E

                  INNER LOOP JOIN PERF_INDEX_DETAIL START

                    ON START.OBJECT_ID = E.OBJECT_ID

                       AND START.INDEX_ID = E.INDEX_ID

                       AND START.DATABASE_NAME = E.DATABASE_NAME

                       AND START.STATS_TIME <= DATEADD(MINUTE, -58,E.STATS_TIME) AND START.STATS_TIME >= DATEADD(MINUTE, -62,E.STATS_TIME)

 

           WHERE  E.INDEX_ID <= 1 AND E.ROW_COUNT > 0 AND E.ROW_COUNT - START.ROW_COUNT <> 0

                ORDER BY 1 DESC,2 DESC,4 DESC ) AS A )

               

          

SELECT TOP 100 PERCENT STATS_TIME,

      

       DATABASE_NAME =

              CASE

                      WHEN GROUPING(DATABASE_NAME) = 1 THEN 'NULL'

                      ELSE DATABASE_NAME

                      END

         ,ROWRANK = CASE

         WHEN Grouping(RANK) = 1 THEN 9999

         ELSE RANK

       END,

       TABLE_NAME,

       Sum(ROWS_LAST_HOUR) AS ROWS_ADDED

FROM   ROWSRANK

GROUP  BY  STATS_TIME , DATABASE_NAME, RANK, TABLE_NAME WITH ROLLUP

ORDER  BY STATS_TIME DESC, DATABASE_NAME, RANK, TABLE_NAME

 

 

 

GO

 

 

CREATE NONCLUSTERED INDEX IX_PERF_INDEX_DETAIL_NC1 ON PERF_INDEX_DETAIL

(ROW_COUNT, INDEX_ID, OBJECT_ID,  DATABASE_NAME, STATS_TIME )

WHERE INDEX_ID <= 1

 

 

GO

 

CREATE NONCLUSTERED INDEX [IX_PERF_INDEX_DETAIL_NC2]

ON [dbo].[PERF_INDEX_DETAIL] ([INDEX_ID],[OBJECT_ID],[DATABASE_NAME],[STATS_TIME])

INCLUDE ([ROW_COUNT])

 

 

 

GO

 

 

 

This improves the performance of the sample queries and the SSRS reports that are built upon this particular view in the DynamicsPerf database.

 

 

Rod “Hotrod” Hansen

Senior Premier Field Engineer – Dynamics AX

 

 

Skip to main content