Please 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
USE
DynamicsPerf
SELECT TOP 100 DATABASE_NAME,
TABLE_NAME,
SUM(CASE
WHEN ISV.INDEX_ID IN (0,1) THEN
PAGE_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) AS
NO_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 BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME
ORDER BY 3 DESC
___________________________________________________________________________________
Index Activity
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*/
--WHERE
DATABASE_NAME = 'XXXXXXXXXXXX'
-- AND
SERVER_NAME = 'XXXXXXXX'
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME
--ORDER
BY TotalOperations DESC
--ORDER
BY TotalReadOperations DESC
ORDER BY
TotalWriteOperations DESC
_____________________________________________________________________________________
INDEX_STATISTICS
SELECT
DATABASE_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
AND
USER_UPDATES > 100 --
index must be getting update
AND
PAGE_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
SELECT
SERVER_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 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*/
WHERE USER_UPDATES > (USER_SEEKS + USER_SCANS +
USER_LOOKUPS)
AND
INDEX_DESCRIPTION NOT LIKE
'%UNIQUE%' and
INDEX_DESCRIPTION NOT LIKE
'CLUSTERED%'
-- AND
SERVER_NAME = ‘XXXXXXXXX’
-- AND
DATABASE_NAME = ‘XXXXXXXX’
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
PAGE_COUNT
--ORDER
BY TotalOperations DESC
--ORDER
BY TotalReadOperations DESC
ORDER BY
TotalWriteOperations DESC
_____________________________________________________________________________________________
COMPRESSED_INDEXES
SELECT *
FROM INDEX_STATS_CURR_VW
WHERE DATA_COMPRESSION
> 0
ORDER BY
USER_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 DISTINCT
SERVER_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 AS
index_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
AND
ROW_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
SELECT
SERVER_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 NOT
LIKE '%primary key%'
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_KEYS
HAVING COUNT(INDEX_KEYS) > 1
ORDER BY
SERVER_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 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.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 NOT
LIKE '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'
AND
PAGE_COUNT > 0
ORDER BY len(INCLUDED_COLUMNS) DESC
_____________________________________________________________________________________________________
UNUSED_INDEXES
SELECT
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
MAX(PAGE_COUNT * 8 / 1024) AS
SIZE_MB,
SUM(USER_SEEKS_DELTA +
USER_SCANS_DELTA
+
USER_LOOKUPS_DELTA) AS
READ_COUNT,
MIN(DATEDIFF(DD, DATE, GETDATE())) AS DAYS
FROM INDEX_HISTORY
WHERE FLAG = 'M'
AND
INDEX_DESCRIPTION NOT LIKE
'%UNIQUE%'
AND
INDEX_DESCRIPTION NOT LIKE
'CLUSTERED%'
AND
INDEX_DESCRIPTION NOT LIKE
'%FILTERED%'
GROUP BY
SERVER_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 NOT
LIKE '%UNIQUE%'
AND TMH.INDEX_DESCRIPTION NOT
LIKE '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
SELECT
CLUS.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
) 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.SERVER_NAME =
NONCLUS.SERVER_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 ) )
AND
CLUS.PAGE_COUNT >
0
ORDER BY CLUS.USER_LOOKUPS DESC,
CLUS.TABLE_NAME,
(
NONCLUS.RANGE_SCAN_COUNT - CLUS.RANGE_SCAN_COUNT
) DESC
_____________________________________________________________________________________________
ADJUST_CLUSTERED_INDEXES
SELECT
CLUS.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
) 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.SERVER_NAME =
NONCLUS.SERVER_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
______________________________________________________________________________________________________
ANALYZE_INDEX_KEY_ORDER
SELECT ISV.SERVER_NAME, ISV.DATABASE_NAME,
ISV.TABLE_NAME, ISV.INDEX_NAME,
ISV.INDEX_KEYS AS
CURRENT_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, '') AS
POTENTIAL_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
AND
INDEX_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 BY
TOTAL_ELAPSED_TIME DESC
