Analysis scripts for Performance Analyzer v2.0 Page 2
This is page 2 of 10 of the general performance analysis scripts online for the Performance Analyzer 2.0 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 |
SSRS Report Analysis | Page 6 |
Query Alerts | Page 7 |
– AX Specific | |
Analyse AX Configuration | Page 8 |
Analyse AX Indexes | Page 9 |
Analyse AX Queries | Page 10 |
Copy one of the following links and press Ctrl-F and click FIND NEXT
in order to go to that section of the scripts
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
—
— INDEXES_BY_SIZE
—
— List top 100 Largest Tables, Investigate for data retention
— purposes or incorrect application configuration such as logging
—
—————————————————————-
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 = ‘XXXXXXXXXXXX’
— AND SERVER_NAME = ‘XXXXXXXX’
GROUP BY SERVER_NAME,
DATABASE_NAME,
TABLE_NAME
ORDER BY 3 DESC
—
— INDEX_ACTIVITY
—
— List READ/WRITE ratios by table, Investigate for activity
— in unusual places such as logging or alerts or unused modules
—
—————————————————————-
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
—
— When were statistics last updated by table/index/stat
—
— NOTE: Database Statistics are only collected weekly so this
— data will NOT be up to date as of a day but as of the Week
—————————————————————-
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
—
— Investigate indexes that are 25% or more of the table width
— for possiblely being defined with too many columns.
—
— NOTE: Anything over 50% ir probably too wide of an index
—————————————————————-
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
—
— Investigate indexes that have more writes than reads
— for possible over indexing
—
— NOTE: Do adequate testing in Dev/Test before removing the index
— from production
—————————————————————-
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’
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
PAGE_COUNT
–ORDER BY TotalOperations DESC
–ORDER BY TotalReadOperations DESC
ORDER BY TotalWriteOperations DESC
—
— COMPRESSED_INDEXES
—
— Find indexes that are compressed.
— Compressing the database reduces the size of the db on disk
— and keeps the data compressed in memory as well. This can
— reduce Disk I/O and improve performance.
— Compression will increase CPU time ono the SQL Server
—————————————————————-
FROM INDEX_STATS_CURR_VW
WHERE DATA_COMPRESSION > 0
ORDER BY USER_UPDATES DESC
—
— UNIQUE_INDEXES_NOT_DEFINED_UNIQUE
—
— Find indexes that might be unique but not declared unique.
— Making it unique IF it actually is improves performance
— because SQL doesn’t have to look at statistics to determine
— that it is unique which would improve compile performance.
—
— For Dynamnics AX, it it’s marked unique it could be used for
— record level caching.
—
—
— NOTE: This is based upon statistics so NOT 100 PERCENT accurate
— DO NOT CHANGE the index to unique UNLESS YOU KNOW IT ABSOLUTELY IS UNIQUE
— keep in mind if business processes change it might not be unique
— in the future
—
—
— YOU COULD BREAK YOUR APPLICATION, SO BE CAREFUL !!
—————————————————————-
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
—
— Tables that have 2 or more indexes with the exact same key
— Trust me, this happens.
—
— NOTE: The indexes could be duplicate on the keys but have unique set of included columns
— in that case they should be combined into a singular index
— for performance reasons
—
—————————————————————-
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
—
— 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.
— EXAMPLE:
— 1-INDEX A, B, C
— 2-INDEX A, B
—
— In this case index 2 is a subset of index 1 on the keys
—
— NOTE: The indexes could be subset duplicate on the keys but have unique set of included columns
— in that case they should be combined into a singular index
— for performance reasons
—————————————————————-
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
—
— Find indexes with high number of include columns
— This will cause table size BLOAT and potential blocking issues
— as SQL updates the included columns
—
— It’s recommended to have 4 or less columns in the included list
— The columns should be static columns as updates to those columns
— WILL cause poor database write performance
—————————————————————-
FROM INDEX_STATS_CURR_VW
WHERE INCLUDED_COLUMNS <> ‘N/A’
AND PAGE_COUNT > 0
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 !!
–**************************************************************
—————————————————————-
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
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
—
— 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
—
— ALL TABLES SHOULD HAVE A CLUSTERED INDEX !!
—
—————————————————————-
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
—
—
— 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
—
— The index should be narrow with few, narrow columns
—
–NOTE – CHANGING CLUSTERED INDEXES WILL TAKE LONG TIME TO DO
— AND REQUIRES DOWNTIME TO IMPLEMENT
——————————————————————————————–
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
—
—
— Find indexes that might need the order of the keys changed
— based upon uniqueness
—
— MOST UNIQUE >>> LEAST UNIQUE
—
— NOTE: This should be done on an Index by Index basis
——————————————————————————————–
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 = ‘XXXXXXXXX’
— AND ISV.DATABASE_NAME = ‘XXXXXXXXXXXXXXX’
— AND ISV.SERVER_NAME = ‘XXXXXXXX’
—
— 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
—
—————————————————————-
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)
—
— MUST HAVE DEPLOYED FULLTEXT INDEXES ON DYNPERF DATABASE
— FOR THIS QUERY TO WORK
—————————————————————-
AS
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
)
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
We're always looking for feedback and would like to hear from you. Please head to the Dynamics 365 Community to start a discussion, ask questions, and tell us what you think!