Microsoft Dynamics AX Support

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

Microsoft Dynamics AX general performance analysis scripts page 7

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

INDEXES_IN_DB_NOT_IN_AOT
INDEXES_WITH_RECVERSION 


—   INDEXES_IN_DB_NOT_IN_AOT

— ————————————————————–
— Find INDEXES that are not defined in the AOT

—————————————————————–

SELECT *
FROM   INDEX_STATS_CURR_VW I
WHERE  INDEX_DESCRIPTION <> ‘HEAP’ AND INDEX_DESCRIPTION NOT LIKE ‘%FILTERED%’
       AND NOT EXISTS (SELECT *
                       FROM   AX_INDEX_DETAIL_CURR_VW A
                       WHERE  A.DATABASE_NAME = I.DATABASE_NAME
                              AND A.TABLE_NAME = I.TABLE_NAME
                              AND A.INDEX_NAME = I.INDEX_NAME)
ORDER  BY TABLE_NAME,
          INDEX_NAME


—   INDEXES_WITH_RECVERSION

— ————————————————————–
— Find INDEXES that have RECVERSION in the Key or Included list
—  RECVERSION should NOT be apart of AX Indexes do to the
—  frequency of updates
—————————————————————–

SELECT *
FROM   INDEX_STATS_CURR_VW I
WHERE  INDEX_KEYS LIKE ‘%RECVERSION%’
        OR INCLUDED_COLUMNS LIKE ‘%RECVERSION%’
ORDER  BY TABLE_NAME,
          INDEX_NAME

 

 

 

.