How to Find Queries Using an Index From SQL Server Plan Cache? – Interview Question of the Week #195

Question: How to Find Queries Using an Index From SQL Server Plan Cache?

Answer: During the recent Comprehensive Database Performance Health Check, I had an interesting situation, where I suggested that we will drop an index for a query. I had clearly proved with the help of my script that the index is not used at all since the last SQL Server restart (in that case it was over 9 months).

However, the company CEO who was the original DBA for their system was on the call and he really wanted to be sure that they are not using that index as he was the original creator of the index. Even though it was clear from the unused index script, he wanted to get the extra assurance that his index is not used at all by some other way.

At that time, I ran the following script which goes into the plan cache and checks if Index is used by any query in the recent time or not. Please note that plan cache cannot depend on 100% but it for sure provides information about the queries which are currently in the cache.

Let us see the script quickly.

-- Query to identify query using any speicific index
-- Pinal Dave (https://blog.sqlauthority.com)
SELECT
SUBSTRING(sqltext.text, (deqs.statement_start_offset / 2) + 1,
(CASE deqs.statement_end_offset
WHEN -1 THEN DATALENGTH(sqltext.text)
ELSE deqs.statement_end_offset
END - deqs.statement_start_offset) / 2 + 1) AS sqltext,
deqs.execution_count,
deqs.total_logical_reads/execution_count AS avg_logical_reads,
deqs.total_logical_writes/execution_count AS avg_logical_writes,
deqs.total_worker_time/execution_count AS avg_cpu_time,
deqs.last_elapsed_time/execution_count AS avg_elapsed_time,
deqs.total_rows/execution_count AS avg_rows,
deqs.creation_time,
deqs.last_execution_time,
CAST(query_plan AS xml) as plan_xml
FROM sys.dm_exec_query_stats as deqs
CROSS APPLY sys.dm_exec_text_query_plan
(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset)
as detqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS sqltext
WHERE
detqp.query_plan like '%Name of Your Index Here%'
ORDER BY deqs.last_execution_time DESC
OPTION (MAXDOP 1, RECOMPILE);
GO

In this query in the WHERE condition, you will have to insert your index name and it will bring out the elaborative result from the plan cache.

Let me know if you find this query helpful or if you use any other query. I will be happy to share the query on this blog with the due credit to you.

Reference: Pinal Dave (https://blog.SQLAuthority.com)