See Also: Main_Page - Transact SQL Code Library - Index Performance Tuning

Queries in the Plan Cache That Are Missing an Index

This query goes against the proc cache and returns one row for every plan missing an index.

The cache is not permanent: servers with small amounts of memory (or large numbers of completely different queries) may want to run this periodically to catch queries while they're still in the cache. For historical tracking, the DBA could create a job that inserts this data into a permanent table every hour, and then check over time to see what queries have created problems.

This query is expensive in terms of server resources: use it with caution if the procedure cache is large.

Credit to Adi Cohen for replacing the LIKE wildcard filter.

T-SQL Code

If you copy/paste this query into your server and get an error about "VALUE", change the three "VALUE" words to lower-case "value". There's an issue with case-sensitivity in the SQL-rendering code we use here at the wiki to display code.

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU 
, total_elapsed_time/execution_count AS AvgDuration 
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, execution_count 
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt 
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No - unfortunately, there's no way to gather this data on SQL Server 2000.
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes (see note above the query)

Tests Updated by Brent Ozar, 2009-04-01