Unmatched Indexes

sp_BlitzCache™ Result: Unmatched Indexes

There is a filtered index present on a table. SQL Server queried the table but was unable to use the filtered index due to parameterization. If the query were not parameterized, SQL Server would have been able to use the filtered index and dramatically reduce the query’s I/O.

How to Fix the Problem

Locate the queries where SQL Server is attempting to use the filtered index. It may require using techniques like dynamic SQL to coerce the query optimizer into picking the filtered index.

You can locate queries with unmatched indexes through the following query:

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.text,
        qp.query_plan
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value('count(//p:UnmatchedIndexes)', 'int') > 0 ;

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)

SELECT st.text,

qp.query_plan

FROM (

SELECT TOP 50 *

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC

) AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qp.query_plan.value('count(//p:UnmatchedIndexes)', 'int') > 0 ;