How to find out which queries (or plans) use a particular index on Azure SQL or SQL Server?

user_2694411 0
Viewed 91

I can get overall statistics of index usage from sys.indexes and sys.dm_db_index_usage_stats and can easily see that index in question is used a lot. However, I could not identify which plans actually use the index.

My overall goal is to identify the optimal list of columns to INCLUDE for the index in question if that makes any difference. I suspect that the current list is too long, but the application is too complex to be sure without verifying the assumption.

indexing tsql sql-server sql
1 Answers

I did not find any system view that could help and I ended up just searching plan XML as text to find the name of the index.

SELECT query_plan, query_stats.last_execution_time, sql_text.text
FROM sys.dm_exec_cached_plans as cached_plan
CROSS APPLY sys.dm_exec_query_plan(cached_plan.plan_handle)  as exec_query_plan
CROSS APPLY sys.dm_exec_sql_text(cached_plan.plan_handle) AS sql_text
INNER JOIN sys.dm_exec_query_stats as query_stats
    ON query_stats.plan_handle = cached_plan.plan_handle
WHERE CHARINDEX('INDEX_NAME', CAST(exec_query_plan.query_plan AS NVARCHAR(MAX))) > 0

-- If you would like to see only the index usage instead of everything (like statistics)
--WHERE CHARINDEX('Index="[INDEX_NAME]"', CAST(exec_query_plan.query_plan AS NVARCHAR(MAX))) > 0

-- to eliminate this query itself and other system queries
AND sql_text.text NOT LIKE '%msparam%'
AND sql_text.text NOT LIKE '%sys.%'

-- Optional conditions to narrow down search
--AND sql_text.text NOT LIKE 'MERGE%' 
--AND sql_text.text NOT LIKE '%UPDATE%'
--AND sql_text.text NOT LIKE '%INSERT%'

ORDER BY query_stats.last_execution_time DESC
user_26944110
Related