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.

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