emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 5

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...ts-page-5.aspx
==============

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 analysisAnalyse SQL ConfigurationPage 1Analyse SQL IndexesPage 2Analyse SQL QueriesPage 3Analyse BlockingPage 4Baseline - benchmark queriesPage 5- AX SpecificAnalyse AX ConfigurationPage 6Analyse AX IndexesPage 7Analyse AX QueriesPage 8Baseline - 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_% &hellip;), 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










Источник: http://blogs.msdn.com/b/axsupport/ar...ts-page-5.aspx