SQL Missing Index in Cached Query PLan
Author: jason
Date: 2015-06-01 11:21:16
Category: Technical

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...


Code

-- 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);




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009