More Refinements on the Missing Indexes Query

02Mar 2009 by Grant Fritchey 9 Comments

Greg Larson posted a really nice query to find worst performing stored procedures in your system. He put in all kinds of attributes to make it customizable, changing the definition of “worst” to different measures,etc. Great query in general. In it he linked to sys.dm_exec_plan_attributes and got the db_id attribute. Duh! So instead of spelunking through the XML to retrieve the database name, I can pull the db_id and use the DB_NAME function. Cleans things up considerably. Thanks Greg.

Here’s the cleaned up code:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
AS sp)
SELECT DB_NAME(CAST(pa.value AS INT))
,s.sql_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
--,s.query_hash
,p.query_plan
,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]',
'NVARCHAR(256)') AS TableName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]',
'NVARCHAR(256)') AS SchemaName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]',
'DECIMAL(6,4)') AS ProjectedImpact
,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM (SELECT TOP 20
s.sql_handle
,s.plan_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
--,s.query_hash
FROM sys.dm_exec_query_stats s
ORDER BY s.total_elapsed_time DESC
) AS s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup')
AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
CROSS APPLY sys.dm_exec_plan_attributes(s.plan_handle) pa
WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1
AND pa.attribute = 'dbid'

9 thoughts on “More Refinements on the Missing Indexes Query”

  • Steve Schneider

    Hey, scary DBA… when i ran your code i got multiple rows for the same plan. So, here’s a suggested modification to get just one line per plan…
    What do you think?

    WITH XMLNAMESPACES (‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS sp)
    SELECT row_number() over (partition by sql_handle order by sql_handle) as ‘rowNum’,
    DB_NAME(CAST(pa.value AS INT)) as dbname
    — ,s.sql_handle
    ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]’,’NVARCHAR(256)’) AS TableName
    — ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]’,’NVARCHAR(256)’) AS SchemaName
    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]’,’DECIMAL(6,4)’) AS ProjectedImpact
    ,p.query_plan
    ,s.total_elapsed_time
    ,s.total_worker_time
    ,s.last_execution_time
    ,s.execution_count
    ,s.total_logical_writes
    ,s.total_logical_reads
    ,s.min_elapsed_time
    ,s.max_elapsed_time
    –,s.query_hash
    –,ColumnGroup.value(‘./@Usage’, ‘NVARCHAR(256)’) AS ColumnGroupUsage
    –,ColumnGroupColumn.value(‘./@Name’, ‘NVARCHAR(256)’) AS ColumnName
    into #temp
    FROM
    (SELECT TOP 200
    s.sql_handle
    ,s.plan_handle
    ,s.total_elapsed_time
    ,s.total_worker_time
    ,s.last_execution_time
    ,s.execution_count
    ,s.total_logical_writes
    ,s.total_logical_reads
    ,s.min_elapsed_time
    ,s.max_elapsed_time
    –,s.query_hash
    FROM sys.dm_exec_query_stats s

    ORDER BY s.total_elapsed_time DESC
    ) AS s
    CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
    CROSS APPLY p.query_plan.nodes(‘/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup’) AS t1 (ColumnGroup)
    CROSS APPLY t1.ColumnGroup.nodes(‘./sp:Column’) AS t2 (ColumnGroupColumn)
    CROSS APPLY sys.dm_exec_plan_attributes(s.plan_handle) pa
    WHERE
    p.query_plan.exist(N’/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes’) = 1
    AND pa.attribute = ‘dbid’
    — and pa.value = db_id()
    select * From #temp where rownum = 1 order by total_elapsed_time desc

    drop table #temp

    Reply
    • scarydba

      Yeah, that would eliminate the multiple rows, but I was going for multiple rows. The extra stuff coming back are the equality & non-equality columns for the index along with the include columns. If anything I should work on pivoting that data somehow but I was being lazy.

      If all you want is the performance metrics on the missing index information, I’ve got a simpler version of the query here that might help.

      Reply

  • In SQL Server, what is the best way to identify the most frequently executed queries that do not utilize an index? Can this be done with a query instead of an external tool?…

    It is possible to use DMOs to query the plans in cache. You can use sys.dm_exec_query_stats to see the most frequentnnly called queries because this DMO shows an aggregation of performance stats of the queries in cache. You can combine this with sys.dm…

    Reply

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.