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

Question: How do you find cached queries that reference a particular index?

Answer: Search the cached statement plans for the index’s database, schema, table and index name. A match tells you that a cached plan references it. An empty result does not prove the index can safely be dropped.

During a Comprehensive Database Performance Health Check, I suggested removing an index. Its usage counters showed no use since the last SQL Server restart, more than nine months earlier. The CEO, who had originally been the DBA and created that index, wanted another way to check. Looking in the plan cache gave us additional evidence.

DECLARE @Database sysname = N'AdventureWorks2025';
DECLARE @Schema sysname = N'HumanResources';
DECLARE @Table sysname = N'Department';
DECLARE @Index sysname = N'PK_Department_DepartmentID';
DECLARE @DbToken nvarchar(258) = QUOTENAME(@Database);
DECLARE @SchemaToken nvarchar(258) = QUOTENAME(@Schema);
DECLARE @TableToken nvarchar(258) = QUOTENAME(@Table);
DECLARE @IndexToken nvarchar(258) = QUOTENAME(@Index);

SELECT SUBSTRING(txt.text, qs.statement_start_offset / 2 + 1,
           (CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(txt.text)
              ELSE qs.statement_end_offset END
            - qs.statement_start_offset) / 2 + 1) AS statement_text,
       qs.execution_count,
       qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
       qs.total_logical_writes / NULLIF(qs.execution_count, 0) AS avg_logical_writes,
       qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000.0 AS avg_cpu_ms,
       qs.total_elapsed_time / NULLIF(qs.execution_count, 0) / 1000.0 AS avg_elapsed_ms,
       qs.total_rows / NULLIF(qs.execution_count, 0) AS avg_rows,
       qs.creation_time, qs.last_execution_time,
       parsed.plan_xml
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(
    qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
CROSS APPLY (VALUES (TRY_CONVERT(xml, qp.query_plan))) AS parsed(plan_xml)
WHERE parsed.plan_xml.exist('
    declare default element namespace
      "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    //Object[@Database=sql:variable("@DbToken")
       and @Schema=sql:variable("@SchemaToken")
       and @Table=sql:variable("@TableToken")
       and @Index=sql:variable("@IndexToken")]') = 1
ORDER BY qs.last_execution_time DESC
OPTION (MAXDOP 1, RECOMPILE);

Replace the four names with the index you are investigating. The original script used a text search across the plan. This version checks the XML Object attributes so a similar name, a wildcard character, or an index name in the SQL text does not create a misleading match. It keeps the original statement offsets, execution counts and workload statistics.

I also corrected the elapsed-time average: divide total_elapsed_time by execution count, not last_elapsed_time. CPU and elapsed time are reported in milliseconds here; read counts remain per-execution averages.

Plans can be evicted, recompiled, unavailable or never cached. These statistics cover the lifetime of the cached plan, not the lifetime of the application. References can also relate to maintaining an index during writes. Before dropping it, check longer-running workloads, constraints, reporting and scheduled jobs. The cache is useful extra assurance, not a complete usage history.

SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for these DMVs; earlier versions require VIEW SERVER STATE. Start on a suitable diagnostic system because inspecting a large plan cache has a cost.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.