Microsoft Dynamics AX general performance analysis scripts page 2

This is page 2 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 Indexes

INDEXES_BY_SIZE
INDEX_ACTIVITY
COMPRESSED_INDEXES
EXACT_DUPLICATE_INDEXES
SUBSET_DUPLICATE_INDEXES
INCLUDED_COLUMN_INDEXES
UNUSED_INDEXES
TABLES_WITHOUT_CLUSTERED_INDEX
ADJUST_CLUSTERED_INDEXES
INDEXES_BEING_SCANNED
SEARCH_QUERY_PLANS_FOR_INDEX_USAGE

— ————————————————————–
—
—   INDEXES_BY_SIZE
—
— List top 100 Largest Tables, Investigate for data retention
— purposes or incorrect application configuration such as logging
—————————————————————-

USE DynamicsPerf

SELECT TOP 100 DATABASE_NAME,
               TABLE_NAME,
               Sum(CASE
                     WHEN INDEX_DESCRIPTION LIKE ‘CLUSTERED%’
                           OR INDEX_DESCRIPTION LIKE ‘HEAP%’ THEN PAGE_COUNT * 8 / 1024
                   END)   AS SIZEMB_DATA,
               Sum(CASE
                     WHEN INDEX_DESCRIPTION LIKE ‘NONCLUSTERED%’ THEN PAGE_COUNT * 8 / 1024
                   END)   AS SIZEMB_INDEXES,
               Count(CASE
                       WHEN INDEX_DESCRIPTION LIKE ‘NONCLUSTERED%’ THEN TABLE_NAME
                     END) AS NO_OF_INDEXES,
               Max(CASE
                     WHEN ( DATA_COMPRESSION > 0 )
                          AND ( INDEX_DESCRIPTION LIKE ‘CLUSTERED%’
                                 OR INDEX_DESCRIPTION LIKE ‘HEAP%’ ) THEN ‘Y’
                     ELSE ‘N’
                   END)   AS DATA_COMPRESSED,
               Max(CASE
                     WHEN ( DATA_COMPRESSION > 0 )
                          AND ( INDEX_DESCRIPTION LIKE ‘NONCLUSTERED%’ ) THEN ‘Y’
                     ELSE ‘N’
                   END)   AS INDEXES_COMPRESSED
FROM   INDEX_STATS_CURR_VW
GROUP  BY DATABASE_NAME,
          TABLE_NAME
ORDER  BY 3 DESC

— ————————————————————–
—
—   INDEX_ACTIVITY
—
— List Activity by table, Investigate for activity
— purposes or incorrect application configuration such as logging
—————————————————————-

SELECT DATABASE_NAME,
       TABLE_NAME,
       CASE
         WHEN ( Sum(USER_UPDATES + USER_SEEKS + USER_SCANS
                    + USER_LOOKUPS) = 0 ) THEN NULL
         ELSE ( Cast(Sum(USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS DECIMAL) / Cast(Sum(USER_UPDATES + USER_SEEKS + USER_SCANS
                                                                                        + USER_LOOKUPS) AS DECIMAL) )
       END                                         AS RatioOfReads,
       CASE
         WHEN ( Sum(USER_UPDATES + USER_SEEKS + USER_SCANS
                    + USER_LOOKUPS) = 0 ) THEN NULL
         ELSE ( Cast(Sum(USER_UPDATES) AS DECIMAL) / Cast(Sum(USER_UPDATES + USER_SEEKS + USER_SCANS
                                                              + USER_LOOKUPS) AS DECIMAL) )
       END                                         AS RatioOfWrites,
       Sum(USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS TotalReadOperations,
       Sum(USER_UPDATES)                           AS TotalWriteOperations,
       Sum(USER_UPDATES + USER_SEEKS + USER_SCANS
           + USER_LOOKUPS)                         AS TotalOperations
FROM   INDEX_STATS_CURR_VW    /*sys.dm_db_index_usage_stats*/
GROUP  BY DATABASE_NAME,
          TABLE_NAME
–order by TotalOperations desc
–order by TotalReadOperations desc
ORDER  BY TotalWriteOperations DESC

— ————————————————————–
—
—  COMPRESSED_INDEXES
—
— Find indexes that are compressed. 
—————————————————————-

SELECT *
FROM   INDEX_STATS_CURR_VW
WHERE  DATA_COMPRESSION > 0
ORDER  BY USER_UPDATES DESC

— ————————————————————–
—
—    EXACT_DUPLICATE_INDEXES
—
— Tables that have 2 or more indexes with the exact same key
— Trust me, this happens.
—  NOTE:  If you are using included columns the indexes may be unique
—————————————————————-

SELECT DATABASE_NAME,
       TABLE_NAME,
       INDEX_KEYS,
       Count(*)
FROM   INDEX_STATS_CURR_VW
GROUP  BY DATABASE_NAME,
          TABLE_NAME,
          INDEX_KEYS
HAVING Count(INDEX_KEYS) > 1
ORDER  BY TABLE_NAME

— ————————————————————–
—
—    SUBSET_DUPLICATE_INDEXES
—
— Just as bad (and even more common) are indexes that are a left key
— subset of another index on same table.  Unless the subsset key is
— unique, its usefulness is subsumed of the superset key.
— NOTE:  If you are using included columns the indexes may be unique
—————————————————————-

SELECT O.DATABASE_NAME,
       O.TABLE_NAME,
       O.INDEX_NAME            AS SUBSET_INDEX,
       O.INDEX_KEYS            AS SUBSET_INDEX_KEYS,
       O.INDEX_DESCRIPTION     AS SUBSET_INDEX_DESCRIPTION,
       O.PAGE_COUNT * 8 / 1024 AS SUBSET_SIZE_MB,
       I.INDEX_NAME            AS SUPERSET_INDEX,
       I.INDEX_KEYS            AS SUPERSET_KEYS
FROM   INDEX_STATS_CURR_VW O
       LEFT JOIN INDEX_STATS_CURR_VW I
              ON I.RUN_NAME = O.RUN_NAME
                 AND I.DATABASE_NAME = O.DATABASE_NAME
                 AND I.TABLE_NAME = O.TABLE_NAME
                 AND I.INDEX_KEYS <> O.INDEX_KEYS
                 AND I.INDEX_KEYS LIKE O.INDEX_KEYS + ‘,%’
WHERE  O.INDEX_DESCRIPTION NOT LIKE ‘%UNIQUE%’
       AND I.INDEX_NAME IS NOT NULL
ORDER  BY O.DATABASE_NAME,
          I.TABLE_NAME,
          I.INDEX_KEYS

— ————————————————————–
—
—   INCLUDED_COLUMN_INDEXES
—
— Find indexes with high number of include columns
— This can be indication of table that needs a different clustered index
— or poorly designed query.  Will cause table size BLOAT and
— potential blocking issues as SQL updates the included columns
—————————————————————-

SELECT TOP 100 *
FROM INDEX_STATS_CURR_VW
WHERE INCLUDED_COLUMNS <> ‘N/A’
ORDER BY LEN(INCLUDED_COLUMNS) DESC

— ————————————————————–
—
—    UNUSED_INDEXES
—
— Find indexes that are not being used.  If an index enforces
— a uniqueness constraint, we must retain it.
—
–**************************************************************
— DO NOT DELETE THESE INDEXES UNLESS YOU ARE SURE YOU HAVE RUN
— EVERY PROCESS IN YOUR DYNAMICS DATABASE INCLUDING YEAR END !!
–**************************************************************
—————————————————————-

SELECT PAGE_COUNT * 8 / 1024 AS SIZE_MB,
       *
FROM   INDEX_HISTORICAL_VW
WHERE
  — criteria for never been used indexes
  USER_SEEKS = 0
  AND USER_SCANS = 0
  — uncomment next 2 lines if you want to see indexes with very low usages
  –AND USER_SEEKS < 100
  –AND USER_SCANS < 100
  AND INDEX_DESCRIPTION NOT LIKE ‘%UNIQUE%’
  AND INDEX_DESCRIPTION NOT LIKE ‘%HEAP%’
  AND (PAGE_COUNT * 8 / 1024) > 0  — only show indexes consuming space
ORDER  BY 1 DESC

— ————————————————————–
—
—  TABLES_WITHOUT_CLUSTERED_INDEX
—
— Tables missing clustered indexes
— Heaps with multiple non-clustered indexes.
— Use the following script to identify a good clustered index
— based solely on user activity
—————————————————————-

SELECT CLUS.TABLE_NAME,
       CLUS.INDEX_NAME                                      AS HEAP_TABLE,
       CLUS.INDEX_KEYS                                      AS CLUSTERED_KEYS,
       NONCLUS.INDEX_NAME                                   AS NONCLUSTERED_INDEX,
       NONCLUS.INDEX_KEYS,
       ( NONCLUS.RANGE_SCAN_COUNT – CLUS.RANGE_SCAN_COUNT ) AS NONCLUSTERED_VS_CLUSTERED_RANGE_COUNT,
       CLUS.USER_SEEKS                                      AS CLUSTERED_USER_SEEKS,
       CLUS.USER_SCANS                                      AS CLUSTERED_USER_SCANS,
       CLUS.SINGLETON_LOOKUP_COUNT                          AS CLUSTERED_SINGLE_LOOKUPS,
       CLUS.RANGE_SCAN_COUNT                                AS CLUSTERED_RANGE_SCAN,
       NONCLUS.USER_SEEKS                                   AS NONCLUSTERED_USER_SEEKS,
       NONCLUS.USER_SCANS                                   AS NONCLUSTERED_USER_SCANS,
       NONCLUS.SINGLETON_LOOKUP_COUNT                       AS NONCLUSTERED_SINGLE_LOOKUPS,
       NONCLUS.RANGE_SCAN_COUNT                             AS NONCLUSTERED_RANGE_SCANS,
       NONCLUS.USER_UPDATES                                 AS NONCLUSTERED_USER_UPDATES
FROM   INDEX_STATS_CURR_VW CLUS
       INNER JOIN INDEX_STATS_CURR_VW NONCLUS
               ON CLUS.TABLE_NAME = NONCLUS.TABLE_NAME
                  AND CLUS.DATABASE_NAME = NONCLUS.DATABASE_NAME
                  AND CLUS.INDEX_NAME <> NONCLUS.INDEX_NAME
WHERE  CLUS.INDEX_DESCRIPTION LIKE ‘HEAP%’
       AND ( ( NONCLUS.RANGE_SCAN_COUNT > CLUS.RANGE_SCAN_COUNT )
              OR ( NONCLUS.SINGLETON_LOOKUP_COUNT > CLUS.SINGLETON_LOOKUP_COUNT ) )
ORDER  BY CLUS.USER_LOOKUPS DESC,
          CLUS.TABLE_NAME,
          ( NONCLUS.RANGE_SCAN_COUNT – CLUS.RANGE_SCAN_COUNT ) DESC

— —————————————————————————————-
—
—    ADJUST_CLUSTERED_INDEXES
—
—
— Find clustered indexes that could be changed
— to 1 of the non-clustered indexes that has more usage than the clustered index
— Use the following script to identify the non-clustered index
— that could be the clustered index based solely on user activity
— This should be the LAST activty done in a performance tuning session
—
–NOTE – CHANGING CLUSTERED INDEXES WILL TAKE LONG TIME TO DO
—  AND REQUIRES DOWNTIME TO IMPLEMENT
——————————————————————————————–

SELECT CLUS.TABLE_NAME,
       CLUS.INDEX_NAME                                      AS CLUSTERED_INDEX,
       CLUS.INDEX_KEYS                                      AS CLUSTERED_KEYS,
       NONCLUS.INDEX_NAME                                   AS NONCLUSTERED_INDEX,
       NONCLUS.INDEX_KEYS,
       ( NONCLUS.RANGE_SCAN_COUNT – CLUS.RANGE_SCAN_COUNT ) AS NONCLUSTERED_VS_CLUSTERED_RANGE_COUNT,
       CLUS.USER_SEEKS                                      AS CLUSTERED_USER_SEEKS,
       CLUS.USER_SCANS                                      AS CLUSTERED_USER_SCANS,
       CLUS.SINGLETON_LOOKUP_COUNT                          AS CLUSTERED_SINGLE_LOOKUPS,
       CLUS.RANGE_SCAN_COUNT                                AS CLUSTERED_RANGE_SCAN,
       NONCLUS.USER_SEEKS                                   AS NONCLUSTERED_USER_SEEKS,
       NONCLUS.USER_SCANS                                   AS NONCLUSTERED_USER_SCANS,
       NONCLUS.SINGLETON_LOOKUP_COUNT                       AS NONCLUSTERED_SINGLE_LOOKUPS,
       NONCLUS.RANGE_SCAN_COUNT                             AS NONCLUSTERED_RANGE_SCANS,
       NONCLUS.USER_UPDATES                                 AS NONCLUSTERED_USER_UPDATES
      
FROM   INDEX_STATS_CURR_VW CLUS
       INNER JOIN INDEX_STATS_CURR_VW NONCLUS
         ON CLUS.TABLE_NAME = NONCLUS.TABLE_NAME
            AND CLUS.DATABASE_NAME = NONCLUS.DATABASE_NAME
            AND CLUS.INDEX_NAME <> NONCLUS.INDEX_NAME
WHERE   CLUS.INDEX_DESCRIPTION LIKE ‘CLUSTERED%’ AND (( NONCLUS.RANGE_SCAN_COUNT > CLUS.RANGE_SCAN_COUNT )
        OR ( NONCLUS.SINGLETON_LOOKUP_COUNT > CLUS.SINGLETON_LOOKUP_COUNT ))
ORDER  BY CLUS.USER_LOOKUPS DESC, CLUS.TABLE_NAME,
          ( NONCLUS.RANGE_SCAN_COUNT – CLUS.RANGE_SCAN_COUNT ) DESC

— ————————————————————–
—
—   INDEXES_BEING_SCANNED
—
— Find non-clustered indexes that are being scanned.  Generally 
— this will indicate that key columns are out of order compared
— to query predicates
—
—————————————————————-

SELECT TOP 100 *
FROM   INDEX_STATS_CURR_VW
WHERE  USER_SCANS > 0
       AND INDEX_DESCRIPTION LIKE ‘NONCLUSTERED%’
ORDER  BY USER_SCANS DESC

— ————————————————————–
—
—    SEARCH_QUERY_PLANS_FOR_INDEX_USAGE
—
— Using indexes identifies in the previous query, list queries
— whose execution plan references a specific index; order by
— most expensive (logical reads)
—
—————————————————————-

SELECT TOP 100 *
FROM   QUERY_STATS_CURR_VW
WHERE  QUERY_PLAN_TEXT LIKE ‘%Index_Name%’
ORDER  BY TOTAL_LOGICAL_READS DESC

GlenTurnbull

See more articles from this author