Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support teams Worldwide

Microsoft Dynamics AX general performance analysis scripts page 5

Microsoft Dynamics AX general performance analysis scripts page 5

Rate This
  • Comments 2

This is page 5 of 8 of the general performance analysis scripts online for the Performance Analyser 1.20 tool. See page 1 for the introduction. Use the links in the table below to navigate between pages.

- General analysis
Analyse SQL Configuration Page 1
Analyse SQL Indexes Page 2
Analyse SQL Queries Page 3
Analyse Blocking Page 4
Baseline - benchmark queries Page 5
- AX Specific
Analyse AX Configuration Page 6
Analyse AX Indexes Page 7
Analyse AX Queries Page 8

Baseline - benchmark queries

INDEX_CHANGES_SINCE_BASELINE
QUERIES_SLOWER_THAN_BASELINE
QUERIES_FASTER_THAN_BASELINE
NEW_QUERIES_NOT_IN_BASELINE
QUERIES_IN_BASELINE_BUT_NOT_IN_CURRENT
TRANSACTION_VOLUME_BY_HOUR
TRANSACTION_VOLUME_BY_HOUR_DETAIL
DISK_IO_BY_HOUR
BAD_SQL_WAIT_STATS
DB_GROWTH
TABLE_ACTIVITY
ACTIVITY_COMPARISON_BETWEEN_RUNS

 

USE [DynamicsPerf]
GO
SELECT *
FROM   STATS_COLLECTION_SUMMARY
ORDER  BY STATS_TIME DESC

GO

----------------------------------------------------------------
--
--  INDEX_CHANGES_SINCE_BASELINE
--
--  show index changes from BASELINE
----------------------------------------------------------------

EXEC SP_INDEX_CHANGES
  @BASELINE = 'BASE_to_compare_to',
  @COMPARISON_RUN_NAME = 'Feb_26_2020_804AM'

----------------------------------------------------------------
--
--  QUERIES_SLOWER_THAN_BASELINE
--
--  queries that got worse  from BASELINE
----------------------------------------------------------------

SELECT A.QUERY_HASH,
       A.EXECUTION_COUNT,
       A.BEFORE_AVG_TIME,
       A.CURRENT_AVG_TIME,
       A.[TIME_DIFF(ms)],
       A.[%DECREASE],
       A.SQL_TEXT,
       B.QUERY_PLAN AS BEFORE_PLAN,
       C.QUERY_PLAN AS AFTER_PLAN
FROM   (SELECT DISTINCT V1.QUERY_HASH,
                        V1.EXECUTION_COUNT,
                        V1.AVG_ELAPSED_TIME                                                               AS BEFORE_AVG_TIME,
                        V2.AVG_ELAPSED_TIME                                                               AS CURRENT_AVG_TIME,
                        V2.AVG_ELAPSED_TIME - V1.AVG_ELAPSED_TIME                                         AS 'TIME_DIFF(ms)',
                        Cast(( V2.AVG_ELAPSED_TIME - V1.AVG_ELAPSED_TIME ) / CASE V1.AVG_ELAPSED_TIME
                                                                               WHEN 0 THEN 1
                                                                               ELSE V1.AVG_ELAPSED_TIME
                                                                             END * 100 AS DECIMAL(14, 3)) AS '%DECREASE',
                        V1.SQL_TEXT,
                        V1.QUERY_PLAN_HASH                                                                AS BEFORE_PLAN_HASH,
                        V2.QUERY_PLAN_HASH                                                                AS AFTER_PLAN_HASH
        FROM   QUERY_STATS_HASH_VW V1
               INNER JOIN QUERY_STATS_HASH_VW V2
                       ON V1.QUERY_HASH = V2.QUERY_HASH
        WHERE  V1.RUN_NAME = 'BASE_to_compare_to'
               AND V2.RUN_NAME = 'Feb_26_2020_804AM'
               AND V1.AVG_ELAPSED_TIME < V2.AVG_ELAPSED_TIME
               AND V1.QUERY_HASH <> 0x0000000000000000) AS A
       CROSS APPLY (SELECT TOP 1 QUERY_PLAN
                    FROM   QUERY_PLANS W1
                    WHERE  W1.QUERY_PLAN_HASH = A.BEFORE_PLAN_HASH) AS B
       CROSS APPLY (SELECT TOP 1 QUERY_PLAN
                    FROM   QUERY_PLANS W2
                    WHERE  W2.QUERY_PLAN_HASH = A.AFTER_PLAN_HASH) AS C
ORDER  BY 6 DESC

----------------------------------------------------------------
--
--  QUERIES_FASTER_THAN_BASELINE
--
--  queries that got faster from BASELINE
----------------------------------------------------------------

SELECT A.QUERY_HASH,
       A.EXECUTION_COUNT,
       A.BEFORE_AVG_TIME,
       A.CURRENT_AVG_TIME,
       A.[TIME_DIFF(ms)],
       A.[%IMPROVEMENT],
       A.SQL_TEXT,
       B.QUERY_PLAN AS BEFORE_PLAN,
       C.QUERY_PLAN AS AFTER_PLAN
FROM   (SELECT DISTINCT V1.QUERY_HASH,
                        V1.EXECUTION_COUNT,
                        V1.AVG_ELAPSED_TIME                                                               AS BEFORE_AVG_TIME,
                        V2.AVG_ELAPSED_TIME                                                               AS CURRENT_AVG_TIME,
                        V1.AVG_ELAPSED_TIME - V2.AVG_ELAPSED_TIME                                         AS 'TIME_DIFF(ms)',
                        Cast(( V1.AVG_ELAPSED_TIME - V2.AVG_ELAPSED_TIME ) / CASE V2.AVG_ELAPSED_TIME
                                                                               WHEN 0 THEN 1
                                                                               ELSE V2.AVG_ELAPSED_TIME
                                                                             END * 100 AS DECIMAL(14, 3)) AS '%IMPROVEMENT',
                        V1.SQL_TEXT,
                        V1.QUERY_PLAN_HASH                                                                AS BEFORE_PLAN_HASH,
                        V2.QUERY_PLAN_HASH                                                                AS AFTER_PLAN_HASH
        FROM   QUERY_STATS_HASH_VW V1
               INNER JOIN QUERY_STATS_HASH_VW V2
                       ON V1.QUERY_HASH = V2.QUERY_HASH
        WHERE  V1.RUN_NAME = 'BASE_to_compare_to'
               AND V2.RUN_NAME = 'Feb_26_2020_804AM'
               AND V1.AVG_ELAPSED_TIME > V2.AVG_ELAPSED_TIME
               AND V1.QUERY_HASH <> 0x0000000000000000) AS A
       CROSS APPLY (SELECT TOP 1 QUERY_PLAN
                    FROM   QUERY_PLANS W1
                    WHERE  W1.QUERY_PLAN_HASH = A.BEFORE_PLAN_HASH) AS B
       CROSS APPLY (SELECT TOP 1 QUERY_PLAN
                    FROM   QUERY_PLANS W2
                    WHERE  W2.QUERY_PLAN_HASH = A.AFTER_PLAN_HASH) AS C
ORDER  BY 6 DESC

 

----------------------------------------------------------------
--
--  NEW_QUERIES_NOT_IN_BASELINE
--
--  NEW queries that are not in the BASELINE
----------------------------------------------------------------

SELECT A.QUERY_HASH,
       A.BEFORE_AVG_TIME,
       A.SQL_TEXT,
       B.QUERY_PLAN AS BEFORE_PLAN
FROM   (SELECT DISTINCT V1.QUERY_HASH,
                        V1.AVG_ELAPSED_TIME AS BEFORE_AVG_TIME,
                        V1.SQL_TEXT,
                        V1.QUERY_PLAN_HASH  AS BEFORE_PLAN_HASH
        FROM   QUERY_STATS_HASH_VW V1
        WHERE  V1.RUN_NAME = 'Feb_26_2020_804AM'
               AND NOT EXISTS (SELECT QUERY_HASH
                               FROM   QUERY_STATS_HASH_VW V2
                               WHERE  V1.QUERY_HASH = V2.QUERY_HASH
                                      AND V2.RUN_NAME = 'BASE_to_compare_to')
               AND V1.QUERY_HASH <> 0x0000000000000000) AS A
       CROSS APPLY (SELECT TOP 1 QUERY_PLAN
                    FROM   QUERY_PLANS W1
                    WHERE  W1.QUERY_PLAN_HASH = A.BEFORE_PLAN_HASH) AS B
ORDER  BY 2 DESC

------------------------------------------------------------------------------
--
--  QUERIES_IN_BASELINE_BUT_NOT_IN_CURRENT
--
--   queries that were in the BASELINE but not in the comparison capture
-------------------------------------------------------------------------------

SELECT A.QUERY_HASH,
       A.BEFORE_AVG_TIME,
       A.SQL_TEXT,
       B.QUERY_PLAN AS BEFORE_PLAN
FROM   (SELECT DISTINCT V1.QUERY_HASH,
                        V1.AVG_ELAPSED_TIME AS BEFORE_AVG_TIME,
                        V1.SQL_TEXT,
                        V1.QUERY_PLAN_HASH  AS BEFORE_PLAN_HASH
        FROM   QUERY_STATS_HASH_VW V1
        WHERE  V1.RUN_NAME = 'BASE_to_compare_to'
               AND NOT EXISTS (SELECT QUERY_HASH
                               FROM   QUERY_STATS_HASH_VW V2
                               WHERE  V1.QUERY_HASH = V2.QUERY_HASH
                                      AND V2.RUN_NAME = 'Feb_26_2020_804AM')
               AND V1.QUERY_HASH <> 0x0000000000000000) AS A
       CROSS APPLY (SELECT TOP 1 QUERY_PLAN
                    FROM   QUERY_PLANS W1
                    WHERE  W1.QUERY_PLAN_HASH = A.BEFORE_PLAN_HASH) AS B
ORDER  BY 2 DESC

 

----------------------------------------------------------------
--
--  TRANSACTION_VOLUME_BY_HOUR
--
--  Show change in row counts by hour
----------------------------------------------------------------

USE [DynamicsPerf]

--Hourly Totals
SELECT *
FROM   PERF_HOURLY_ROWDATA_VW
WHERE  ROWRANK = 9999
       AND DATABASE_NAME <> 'NULL'
ORDER  BY STATS_TIME DESC

----------------------------------------------------------------
--
--  TRANSACTION_VOLUME_BY_HOUR_DETAIL
--
--  Show details of change in row counts by hour
----------------------------------------------------------------

SELECT *
FROM   PERF_HOURLY_ROWDATA_VW
WHERE  STATS_TIME = 'ENTER_STATS_TIME_HERE_FROM_PREVIOUS_QUERY'
       AND TABLE_NAME <> 'NULL'
ORDER  BY ROWRANK

----------------------------------------------------------------
--
--  DISK_IO_BY_HOUR
--
--  Hourly Change in Disk IO Stats by File
----------------------------------------------------------------

SELECT *
FROM   PERF_HOURLY_IOSTATS_VW
WHERE DATABASE_NAME= 'Dynamics'
ORDER BY STATS_TIME DESC, DATABASE_NAME, FILE_ID

----------------------------------------------------------------
--
--  BAD_SQL_WAIT_STATS
--
-- IO bottleneck : If Top 2 values for wait stats include IO, (ASYNCH_IO_COMPLETION,IO_COMPLETION,LOGMGR,,WRITELOG,PAGEIOLATCH_x_xxx) there is an IO bottleneck.
-- Blocking bottleneck: If top 2 wait_stats values include locking (LCK_M_BU, LCK_M_IS, LCK_M_IU, LCK_% …), there is a blocking bottleneck
-- Parallelism: Cxpacket waits > 5%
----------------------------------------------------------------

/*********************************************************************************************

************************************************************************************************/

SELECT STATS_TIME,
       RANK,
       WAIT_TYPE,
       WAITING_TASKS_LAST_HOUR,
       WAIT_TIME_MS_LAST_HOUR
FROM   PERF_HOURLY_WAITSTATS_VW
WHERE  ( WAIT_TYPE LIKE 'PAGEIOLATCH_%'
          OR WAIT_TYPE LIKE 'ASYNCH_IO_COMPLETION%'
          OR WAIT_TYPE LIKE 'IO_COMPLETION%'
          OR WAIT_TYPE LIKE 'LOGMGR%'
          OR WAIT_TYPE LIKE 'WRITELOG%' )
       AND RANK < 3
       AND WAIT_TIME_MS_LAST_HOUR > 0

 

 

--Activity between 2 data collections to look at comparisons over a longer time period
--Find all run_names

SELECT RUN_NAME
FROM   STATS_COLLECTION_SUMMARY
ORDER  BY STATS_TIME DESC

----------------------------------------------------------------
--
--  DB_GROWTH
--

--Find record count and table size differences between the runs
--Can use this to accurately predict database growth
--NOTE only TOP 1000 tables are returned
--------------------------------------------------------------------------------
SELECT *
FROM   fn_dbstats('STARTING_RUN_NAME', 'ENDING_RUN_NAME')
ORDER  BY DELTA_SIZEMB DESC

 

----------------------------------------------------------------
--
--  TABLE_ACTIVITY
--

--Find record read/write and row count differences between the runs
-------------------------------------------------------------------

SELECT A.TABLE_NAME,
       B.ROW_COUNT - A.ROW_COUNT                       AS DELTA_IN_ROWS,
       B.TOTALREADOPERATIONS - A.TOTALREADOPERATIONS   AS DELTA_IN_READS,
       B.TOTALWRITEOPERATIONS - A.TOTALWRITEOPERATIONS AS DELTA_IN_WRITES
FROM   INDEX_OPS_VW A
       INNER JOIN INDEX_OPS_VW B
               ON A.TABLE_NAME = B.TABLE_NAME
                  AND A.DATABASE_NAME = B.DATABASE_NAME
                  AND A.RUN_NAME = 'STARTING_RUN_NAME'
                  AND B.RUN_NAME = 'ENDING_RUN_NAME'
ORDER  BY 2 DESC

----------------------------------------------------------------
--
--  SQL_WAIT_STATS_BY_HOUR
--- Hourly Change in SQL Server Wait Stats
----------------------------------------------------------------

SELECT *
FROM   PERF_HOURLY_WAITSTATS_VW
ORDER BY STATS_TIME DESC, RANK

 

----------------------------------------------------------------
--
--  ACTIVITY_COMPARISON_BETWEEN_RUNS
--
--
--  Comparison queries between different data captures
-----------------------------------------------------------------

SELECT D1.RUN_NAME           AS RUN1,
       D2.RUN_NAME           AS RUN2,
       D1.SQL_TEXT,
       D1.QUERY_PLAN,
       D1.AVG_ELAPSED_TIME   AS RUN1_AVG_TIME,
       D2.AVG_ELAPSED_TIME   AS RUN2_AVG_TIME,
       D2.AVG_ELAPSED_TIME-D1.AVG_ELAPSED_TIME AS TIME_DIFF,
       D1.AVG_LOGICAL_READS  AS RUN1_READS,
       D2.AVG_LOGICAL_READS  AS RUN2_READS,
       D2.AVG_LOGICAL_READS-D1.AVG_LOGICAL_READS AS READS_DIFF,
       D1.AVG_LOGICAL_WRITES AS RUN1_WRITES,
       D2.AVG_LOGICAL_WRITES AS RUN2_WRITES,
       D2.AVG_LOGICAL_WRITES-D1.AVG_LOGICAL_WRITES AS WRITES_DIFF,
       D1.QUERY_HASH
FROM   QUERY_STATS_VW D1
       INNER JOIN QUERY_STATS_VW D2
         ON D1.QUERY_HASH = D2.QUERY_HASH
        AND D1.DATABASE_NAME = D2.DATABASE_NAME
WHERE  D1.QUERY_HASH <> 0x0000000000000000
       AND D1.RUN_NAME = 'STARTING_RUN_NAME'
       AND D2.RUN_NAME = 'ENDING_RUN_NAME'
ORDER  BY D2.AVG_ELAPSED_TIME - D1.AVG_ELAPSED_TIME

 

 

 

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
  • Hello,

    I've set up DynamicsPerf in a test environment. I just ran the baseline script. Now when I attempted to run the QUERIES_SLOWER_THAN_BASELINE script I get this error ...

    Invalid object name 'QUERY_STATS_HASH_VW'

    Is this object created as part of another step which I've missed?

    Thank you.

  • Hi KB, this view is created as part of the script: 1-Create_Core_Objects.sql,  lines 9768 to 9809. If the view already exists, it will drop it and recreate it, so ultimately you could run that part again if everything else looks ok.

    However, first:

    Are you running it against the DynamicsPerf database - if you execute the following immediately before the script you mentioned, does it work?

    USE DYNAMICSPERF

    GO

    If you run the script below, what is returned?

    USE DYNAMICSPERF

    GO

    SELECT NAME

    FROM SYS.VIEWS

    WHERE NAME LIKE '%QUERY%'

    You should see three rows returned including QUERY_STATS_HASH_VW.

    Hope this helps.

    Glen

Page 1 of 1 (2 items)