Like
Share
ReportPlease find the List of Queries to monitor and improve the performance of AX from Dyanmics Perf 2.0.
Once you complete the installation of Dynamics Perf 2.0 from the below link.
It will start collecting the data and store it in the DynamicsPerf database.
To identify the below list , please query the DynamicsPerf Database name and not AX database as shown below.
- Ø INDEXES_BY_SIZE
- Ø INDEX_ACTIVITY
- Ø INDEX_STATISTICS
- Ø TOO_LARGE_INDEXES
- Ø HIGH_COST_INDEXES
- Ø COMPRESSED_INDEXES
- Ø UNIQUE_INDEXES_NOT_DEFINED_UNIQUE
- Ø EXACT_DUPLICATE_INDEXES
- Ø SUBSET_DUPLICATE_INDEXES
- Ø INCLUDED_COLUMN_INDEXES
- Ø UNUSED_INDEXES
- Ø TABLES_WITHOUT_CLUSTERED_INDEX
- Ø ADJUST_CLUSTERED_INDEXES
- Ø ANALYZE_INDEX_KEY_ORDER
- Ø INDEXES_BEING_SCANNED
- Ø SEARCH_QUERY_PLANS_FOR_INDEX_USAGE
To Find The Index by size
USEDynamicsPerf
SELECT TOP 100 DATABASE_NAME,
TABLE_NAME,
SUM(CASE
WHEN ISV.INDEX_ID IN (0,1) THENPAGE_COUNT * 8 /1024
END) AS SIZEMB_DATA,
SUM(CASE
WHEN ISV.INDEX_ID > 1 THEN PAGE_COUNT * 8 / 1024
END) AS SIZEMB_INDEXES,
COUNT(CASE
WHEN ISV.INDEX_ID > 1 THEN TABLE_NAME
END) ASNO_OF_INDEXES,
MAX(CASE
WHEN ( DATA_COMPRESSION> 0 )
AND ( ISV.INDEX_ID IN (0,1) ) THEN 'Y'
ELSE 'N'
END) AS DATA_COMPRESSED,
MAX(CASE
WHEN ( DATA_COMPRESSION> 0 )
AND ( ISV.INDEX_ID > 1) THEN 'Y'
ELSE 'N'
END) AS INDEXES_COMPRESSED
FROM INDEX_STATS_CURR_VW ISV
--WHERE DATABASE_NAME = 'Your AX Database Name'
--AND SERVER_NAME = 'Database Server Name'
GROUP BYSERVER_NAME,
DATABASE_NAME,
TABLE_NAME
ORDER BY 3 DESC
___________________________________________________________________________________
Index Activity
SELECTDATABASE_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 ASratioofwrites,
SUM(USER_SEEKS +USER_SCANS + USER_LOOKUPS) AStotalreadoperations,
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*/
--WHERE DATABASE_NAME = 'XXXXXXXXXXXX'
-- AND SERVER_NAME = 'XXXXXXXX'
GROUP BYSERVER_NAME,
DATABASE_NAME,
TABLE_NAME
--ORDER BY TotalOperations DESC
--ORDER BY TotalReadOperations DESC
ORDER BYTotalWriteOperations DESC
_____________________________________________________________________________________
INDEX_STATISTICS
SELECTDATABASE_NAME,
SERVER_NAME,
TABLENAME,
INDEXNAME,
UPDATED
FROM INDEX_STAT_HEADER ISH
WHERE UPDATED IS NOT NULL
-- AND SERVER_NAME = 'XXXXXXXXX'
-- AND DATABASE_NAME = 'XXXXXXXX'
ORDER BY UPDATED DESC
___________________________________________________________________________________________
TOO_LARGE_INDEXES
;WITH TABLE_SIZE (SERVER_NAME,DATABASE_NAME, TABLE_NAME, SIZE)
AS (SELECT SERVER_NAME,DATABASE_NAME,
TABLE_NAME,
PAGE_COUNT * 8 / 1024 AS size
FROM INDEX_STATS_CURR_VW
WHERE INDEX_ID IN (0,1) )
SELECT DISTINCT ISV.DATABASE_NAME,
ISV.TABLE_NAME,
TS.SIZE,
ISV.INDEX_NAME,
( ( PAGE_COUNT * 8 / 1024 ) / TS.SIZE ) * 100 AS [%_of_table],
ISV.INDEX_DESCRIPTION,
ISV.INDEX_KEYS,
ISV.INCLUDED_COLUMNS
FROM INDEX_STATS_CURR_VW ISV
INNER JOIN TABLE_SIZE TS
ON ISV.SERVER_NAME = TS.SERVER_NAME
AND ISV.DATABASE_NAME = TS.DATABASE_NAME
AND ISV.TABLE_NAME = TS.TABLE_NAME
AND TS.SIZE > 0
WHERE ISV.INDEX_ID > 1
ANDUSER_UPDATES > 100 -- index must be getting update
ANDPAGE_COUNT > 100 -- small parameter tables are ok
AND ( ( PAGE_COUNT * 8 / 1024 ) / TS.SIZE ) * 100 > 25 --25%
ORDER BY TS.SIZE DESC
__________________________________________________________________________________________
HIGH_COST_INDEXES
SELECTSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
PAGE_COUNT*8/1024 AS SIZE_MB,
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 ASRatioOfWrites,
SUM(USER_SEEKS +USER_SCANS + USER_LOOKUPS) ASTotalReadOperations,
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*/
WHERE USER_UPDATES > (USER_SEEKS + USER_SCANS +USER_LOOKUPS)
ANDINDEX_DESCRIPTION NOT LIKE'%UNIQUE%' andINDEX_DESCRIPTION NOT LIKE'CLUSTERED%'
-- AND SERVER_NAME = ‘XXXXXXXXX’
-- AND DATABASE_NAME = ‘XXXXXXXX’
GROUP BYSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
PAGE_COUNT
--ORDER BY TotalOperations DESC
--ORDER BY TotalReadOperations DESC
ORDER BYTotalWriteOperations DESC
_____________________________________________________________________________________________
COMPRESSED_INDEXES
SELECT *
FROM INDEX_STATS_CURR_VW
WHERE DATA_COMPRESSION> 0
ORDER BYUSER_UPDATES DESC
________________________________________________________________________________________
UNIQUE_INDEXES_NOT_DEFINED_UNIQUE
;WITH RECORDS_CTE (SERVER_NAME,DATABASE_NAME, TABLENAME, INDEX_NAME, INDEX_KEYS, ROW_COUNT, READS)
AS
-- Define the CTE query.
(SELECT DISTINCTSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
INDEX_KEYS,
ROW_COUNT,
USER_SEEKS + USER_SCANS +USER_LOOKUPS AS reads
FROM INDEX_STATS_CURR_VW)
SELECT DISTINCT dv.SERVER_NAME,
dv.DATABASE_NAME,
dv.TABLENAME,
rows.INDEX_NAME,
DS.TYPE_DESC ASindex_desc,
rows.INDEX_KEYS,
rows.ROW_COUNT
FROM INDEX_DENSITY_VECTOR dv
INNER JOIN RECORDS_CTE rows
ON dv.TABLENAME = rows.TABLENAME
AND dv.DATABASE_NAME = rows.DATABASE_NAME
AND dv.SERVER_NAME = rows.SERVER_NAME
INNER JOIN DYNSYSINDEXES DS
ON DS.DATABASE_NAME = dv.DATABASE_NAME
AND DS.RUN_NAME LIKE dv.SERVER_NAME + '%'
AND DS.NAME = rows.INDEX_NAME
WHERE DS.IS_UNIQUE = 0
ANDROW_COUNT / ( 1 / DENSITY ) =1.000000000000000000
AND rows.READS > 1000 -- relatively used tables
AND rows.ROW_COUNT > 10000 -- larger tables only
ORDER BY ROW_COUNT DESC
__________________________________________________________________________________________
EXACT_DUPLICATE_INDEXES
SELECTSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_KEYS,
COUNT(*),
STUFF ((SELECT ', ' + 'INDEX_NAME = ' + ISV.INDEX_NAME
+ ' KEYS= ' + ISV.INDEX_KEYS
+ ' INCLUDED_COLUMNS= '
+ ISV.INCLUDED_COLUMNS
FROM INDEX_STATS_CURR_VW ISV
WHERE ISV.DATABASE_NAME = A.DATABASE_NAME
AND ISV.SERVER_NAME = A.SERVER_NAME
AND ISV.TABLE_NAME = A.TABLE_NAME
AND ISV.INDEX_KEYS = A.INDEX_KEYS
FOR xml path('')), 1, 1, '''') AS indexes
FROM INDEX_STATS_CURR_VW A
WHERE INDEX_DESCRIPTION NOTLIKE '%primary key%'
GROUP BYSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_KEYS
HAVING COUNT(INDEX_KEYS) > 1
ORDER BYSERVER_NAME,
DATABASE_NAME,
TABLE_NAME
__________________________________________________________________________________________________
SUBSET_DUPLICATE_INDEXES
SELECT DISTINCT O.SERVER_NAME,
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 ASsubset_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.SERVER_NAME = O.SERVER_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 O.INDEX_DESCRIPTION NOTLIKE 'CLUSTERED%'
AND I.INDEX_NAME IS NOT NULL
AND O.PAGE_COUNT > 0
ORDER BY O.SERVER_NAME,
O.DATABASE_NAME,
O.TABLE_NAME,
O.INDEX_KEYS
____________________________________________________________________________________________
INCLUDED_COLUMN_INDEXES
SELECT TOP 100 *
FROM INDEX_STATS_CURR_VW
WHERE INCLUDED_COLUMNS <>'N/A'
ANDPAGE_COUNT > 0
ORDER BY len(INCLUDED_COLUMNS) DESC
_____________________________________________________________________________________________________
UNUSED_INDEXES
SELECTSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
MAX(PAGE_COUNT * 8 / 1024) ASSIZE_MB,
SUM(USER_SEEKS_DELTA +USER_SCANS_DELTA
+USER_LOOKUPS_DELTA) ASREAD_COUNT,
MIN(DATEDIFF(DD, DATE, GETDATE())) AS DAYS
FROM INDEX_HISTORY
WHERE FLAG = 'M'
ANDINDEX_DESCRIPTION NOT LIKE'%UNIQUE%'
ANDINDEX_DESCRIPTION NOT LIKE'CLUSTERED%'
ANDINDEX_DESCRIPTION NOT LIKE'%FILTERED%'
GROUP BYSERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME
HAVING SUM(USER_SEEKS_DELTA + USER_SCANS_DELTA
+USER_LOOKUPS_DELTA) =0
AND MAX(PAGE_COUNT * 8 / 1024) > 0
ORDER BY 5 DESC
--Indexes not used this month but used last month
SELECT TMH.*
FROM INDEX_HISTORY TMH
INNER JOIN INDEX_HISTORY LMH
ON TMH.SERVER_NAME = LMH.SERVER_NAME
AND TMH.DATABASE_NAME = LMH.DATABASE_NAME
AND TMH.TABLE_NAME = LMH.TABLE_NAME
AND TMH.INDEX_NAME = LMH.INDEX_NAME
AND TMH.DATE = Dateadd(MONTH, 1, LMH.DATE)
WHERE TMH.FLAG = 'M'
AND LMH.FLAG = 'M'
AND TMH.INDEX_DESCRIPTION NOTLIKE '%UNIQUE%'
AND TMH.INDEX_DESCRIPTION NOTLIKE 'CLUSTERED%'
AND ( TMH.USER_SEEKS_DELTA
+ TMH.USER_LOOKUPS_DELTA
+ TMH.USER_SCANS_DELTA ) = 0 --REH Not used this month
AND ( LMH.USER_SEEKS_DELTA
+ LMH.USER_LOOKUPS_DELTA
+ LMH.USER_SCANS_DELTA ) > 0 --REH was used last month
______________________________________________________________________________________
TABLES_WITHOUT_CLUSTERED_INDEX
SELECTCLUS.SERVER_NAME,
CLUS.DATABASE_NAME,
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 ) ASnonclustered_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 ASnonclustered_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
ONCLUS.TABLE_NAME =NONCLUS.TABLE_NAME
ANDCLUS.DATABASE_NAME =NONCLUS.DATABASE_NAME
ANDCLUS.SERVER_NAME =NONCLUS.SERVER_NAME
ANDCLUS.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 ) )
ANDCLUS.PAGE_COUNT >0
ORDER BY CLUS.USER_LOOKUPS DESC,
CLUS.TABLE_NAME,
(NONCLUS.RANGE_SCAN_COUNT - CLUS.RANGE_SCAN_COUNT ) DESC
_____________________________________________________________________________________________
ADJUST_CLUSTERED_INDEXES
SELECTCLUS.SERVER_NAME,
CLUS.DATABASE_NAME,
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 ) ASNONCLUSTERED_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 ASNONCLUSTERED_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
ONCLUS.TABLE_NAME =NONCLUS.TABLE_NAME
ANDCLUS.DATABASE_NAME =NONCLUS.DATABASE_NAME
ANDCLUS.SERVER_NAME =NONCLUS.SERVER_NAME
ANDCLUS.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
______________________________________________________________________________________________________
ANALYZE_INDEX_KEY_ORDER
SELECT ISV.SERVER_NAME, ISV.DATABASE_NAME,
ISV.TABLE_NAME, ISV.INDEX_NAME,
ISV.INDEX_KEYS ASCURRENT_INDEX_ORDER,
Stuff ((SELECT ', ' + KEYCOLUMN
FROM INDEX_KEY_ORDER_VW IKO
WHERE ISV.SERVER_NAME = IKO.SERVER_NAME
AND ISV.DATABASE_NAME = IKO.DATABASE_NAME
AND ISV.TABLE_NAME = IKO.TABLENAME
AND ISV.INDEX_NAME = IKO.INDEXNAME
ORDER BY TABLENAME,
INDEXNAME,
TOTAL_ROWS DESC
FOR xml path('')), 1, 1, '') ASPOTENTIAL_INDEX_ORDER
FROM INDEX_STATS_CURR_VW ISV
WHERE ISV.ROW_COUNT > 1000
AND ISV.USER_SEEKS > 1000
--AND ISV.TABLE_NAME = 'AX Table Name'
-- AND ISV.DATABASE_NAME = 'AX Database Name'
--AND ISV.SERVER_NAME = 'AX Database Server Name'
____________________________________________________________________________________________
INDEXES_BEING_SCANNED
SELECT TOP 100 *
FROM INDEX_STATS_CURR_VW
WHERE USER_SCANS >0
ANDINDEX_DESCRIPTION LIKE 'NONCLUSTERED%'
ORDER BY USER_SCANS DESC
____________________________________________________________________________________________________
SEARCH_QUERY_PLANS_FOR_INDEX_USAGE
;WITH FT_CTE2 (QUERY_PLAN_HASH,QUERY_PLAN)
AS
(SELECT QUERY_PLAN_HASH,QUERY_PLAN
FROM QUERY_PLANS
WHERE CONTAINS (C_QUERY_PLAN, '"INVENTDIM" AND "INDEX SCAN"') -- find all statements scanning a specific table
--WHERE CONTAINS (C_QUERY_PLAN, '"I_6143RECID"') — find all SQL statements that contain a specific index
)
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
INNER JOIN FT_CTE2 FT2
ON QS.QUERY_PLAN_HASH =FT2.QUERY_PLAN_HASH
WHERE 1 = 1
-- AND LAST_EXECUTION_TIME > 'XXXXXXX' -- find all queries that have executed after a specific time
ORDER BYTOTAL_ELAPSED_TIME DESC
*This post is locked for comments