Master Station Log
techno-rants, musings, misc geek writing, and occasional caffeine binges
SQL Missing Index in Cached Query PLan
Date: 2015-06-01 11:21:16
I can't remember where I got this, likely from SQL Authority... Its a great query for high count use cached plans with missing indexes...
-- Find missing index warnings for cached plans in the current database
-- Note: This query could take some time on a busy instance
SELECT TOP(50) OBJECT_NAME(objectid) AS [ObjectName],
query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);