Mar 02 2009

More Refinements on the Missing Indexes Query

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'

Tags: , , ,

categories SQL Server 2005, SQL Server 2008, TSQL | | 02 March 2009 |

6 Comments

  • By Steve Schneider, March 9, 2009 @ 12:42 pm

    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

  • By scarydba, March 9, 2009 @ 12:54 pm

    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.

Other Links to this Post

  1. Tips for DBA: Missing indexes recommendations (SQL Server 2005) - Alexander Gladchenko — March 3, 2009 @ 10:13 am

  2. Tips for DBA: Missing indexes recommendations (SQL Server 2005) - Alexander Gladchenko — April 12, 2009 @ 1:39 pm

  3. Can You Dig It? – Missing Indexes | Strate SQL — December 14, 2010 @ 9:02 am

  4. Quora — August 19, 2011 @ 8:51 am

RSS feed for comments on this post. TrackBack URI

Leave a comment