sql server – Search plan cache for all query plans using a given index?

As well as a simple text search, you can also use an XQuery filter, which may be faster or slower.

DECLARE @idx sysname = N'(IX_YourIndexName)';

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    qp.query_plan,
    qs.execution_count,
    st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
INNER JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE qp.query_plan.exist('//Object(@Index = sql:variable("@idx"))') = 1;

Or if you want just the query plans:

DECLARE @idx sysname = N'(IX_YourIndexName)';

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE qp.query_plan.exist('//Object(@Index = sql:variable("@idx"))') = 1;

You can also add filters on @Database and @Schema if necessary.

If you are copying the index name out of system views, make sure to use QUOTENAME otherwise it won’t match.