Missing Index Request - Brent Ozar Unlimited®

One or more of your top poorly performing queries also have missing indexes. This can cause additional CPU usage and I/O problems and may be the only reason this query is showing up in the top poorly performing queries.

To help you identify high value missing indexes (Impact >= 70%), we’ve added a column to sp_BlitzCache and sp_BlitzQueryStore that lists out all the missing indexes for a query.

CLICK ME FOR ONE WEIRD TRICK OR MAYBE A FEW WEIRD TRICKS WHO KNOWS IT’S HALLOWEEN

This doesn’t mean you should add every index SQL recommends, but it’s a good place to find opportunities to tune indexes. Think of it as SQL Server’s little cry for help.

How to Fix the Problem

Run sp_BlitzIndex to determine which indexes you’re missing on a database by database basis.

To find the queries that are generating missing index recommendations, you can run the following query:

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.text,
        qp.query_plan,
        qs.*
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:MissingIndexGroup)', 'int') > 0

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

SELECT st.text,

qp.query_plan,

qs.*

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:MissingIndexGroup)', 'int') > 0