Find missing index warnings from cached plans

This query could take some time on a busy instance, so be cautious when executing it on production environments.

The following SQL query is designed to retrieve the top 25 cached plans that are missing index recommendations. It may take some time to execute on heavily loaded instances:

SELECT TOP (25) OBJECT_NAME(objectid) AS [ObjectName]
    ,cp.objtype
    ,cp.usecounts
    ,cp.size_in_bytes
    ,qp.query_plan
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);

This query checks the cached execution plans for missing index recommendations in the current database. It can help identify potentially useful indexes that could improve performance. The query returns:

• ObjectName: The name of the object (such as a table) associated with the cached plan.
• objtype: The type of object (e.g., table, index, etc.) that the cached plan pertains to.
• usecounts: The number of times the cached plan has been executed.
• size_in_bytes: The size of the cached plan in bytes.
• query_plan: The XML query plan that details the execution of the query.

Note: The query uses the WITH (NOLOCK) hint to avoid blocking and allow for faster execution, though it may return uncommitted data. The OPTION (RECOMPILE) ensures that the query is recompiled each time it is executed, which is important for reflecting the most up-to-date plan.