Microsoft Dynamics AX general performance analysis scripts page 3
This is page 3 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 |
Analyse SQL Queries
EXPENSIVE_QUERIES
MISSING_INDEX_QUERIES
QUERIES_WITH_MULTIPLE_EXECUTION_PLANS
QUERIES_SCANNING_TABLES
USE DynamicsPerf
—————————————————————-
—
— EXPENSIVE_QUERIES
—
— List top 100 most expensive queries
—————————————————————-
SELECT TOP 100 *
FROM QUERY_STATS_CURR_VW QS — Queries from last data collection only
–FROM QUERY_STATS_VW QS — Review queries for all data collections
WHERE
–Remove queries with comments
NOT EXISTS (SELECT QUERY_HASH
FROM COMMENTS C
WHERE C.QUERY_HASH = QS.QUERY_HASH) — Remove queries that have comments
— AND QUERY_HASH = 0x0000000000 — find a specific query
— AND SQL_TEXT LIKE ‘%VALUE%’ — find all SQL statements that contain a specific text i.e. table name
— AND QUERY_PLAN_TEXT LIKE ‘%VALUE%’ — find all SQL Plans that contain a specific text i.e. index name
— AND LAST_EXECUTION_TIME > ‘XXXXXXX’ — find all queries that have executed after a specific time
— AND DATABASE_NAME = ‘XXXXXXXXX’ — find all queries for a specific database
— AND MAX_ELAPSED_TIME /10 > AVG_ELAPSED_TIME — Find all queries potentially getting blocked or paramater sniffing issues
ORDER BY TOTAL_ELAPSED_TIME DESC — Queries consuming most TOTAL time on SQL Server
— ORDER BY AVG_LOGICAL_READS DESC — Queries potentially causing large disk i/o
— ORDER BY EXECUTION_COUNT DESC — High execution count could be loops in application code
— ORDER BY TOTAL_LOGICAL_READS DESC — Queries to review to potentially reduce disk i/o
—————————————————————-
—
— MISSING_INDEX_QUERIES
—
— Identify queries that the optimizer suspects can be optimized
— by new or changed indexes:
—
— NOTE: DO NOT add these indexes verbatim without
— deep analysis. Large INCLUDED lists are NOT recommended
— for ERP solutions
—————————————————————-
SELECT TOP 100 *
FROM MISSING_INDEXES_CURR_VW
WHERE NOT EXISTS (SELECT QUERY_HASH
FROM COMMENTS C
WHERE C.QUERY_HASH = MISSING_INDEXES_CURR_VW.QUERY_HASH) — Remove queries that have comments
AND INDEX_IMPACT > 75
AND EXECUTION_COUNT > 100
AND AVG_ELAPSED_TIME > 20
AND AVG_LOGICAL_READS > 1000
ORDER BY TOTAL_LOGICAL_READS DESC
—————————————————————-
—
— QUERIES_WITH_MULTIPLE_EXECUTION_PLANS
—
— List queries that have more than 1 execution plan
— NOTE: Only useful on SQL2008 or greater
— This is for potential parameter sniffing issues
—————————————————————-
SELECT DATABASE_NAME,
QUERY_HASH,
COUNT(QUERY_PLAN_HASH) AS CNT
FROM QUERY_STATS_CURR_VW
GROUP BY DATABASE_NAME,
QUERY_HASH
HAVING COUNT(QUERY_PLAN_HASH) > 1
ORDER BY CNT DESC
—————————————————————-
—
— QUERIES_SCANNING_TABLES
—
— Find queries scanning a table
—————————————————————-
SELECT TOP 100 *
FROM QUERY_STATS_CURR_VW
WHERE ( QUERY_PLAN_TEXT LIKE ‘%TABLE SCAN%’
OR QUERY_PLAN_TEXT LIKE ‘%INDEX SCAN%’ )
–AND QUERY_PLAN_TEXT LIKE ‘%<Table Name>%’ — Comment this line to return all tables
ORDER BY TOTAL_LOGICAL_READS DESC
We're always looking for feedback and would like to hear from you. Please head to the Dynamics 365 Community to start a discussion, ask questions, and tell us what you think!