More Refinements on the Missing Indexes Query

March 2, 2009 at 8:03 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , )


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'

4 Comments

  1. Tips for DBA: Missing indexes recommendations (SQL Server 2005) - Alexander Gladchenko said,

    […] программу «Помощник по настройке ядра СУБД». More Refinements on the Missing Indexes Query Published Wednesday, November 14, 2007 9:59 AM by gladchenko Filed under: SQL […]

  2. Steve Schneider said,

    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

  3. scarydba said,

    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.

  4. Tips for DBA: Missing indexes recommendations (SQL Server 2005) - Alexander Gladchenko said,

    […] «Помощник по настройке ядра СУБД». В тему: More Refinements on the Missing Indexes Query SQL Server 2005: Определение ценности предложенных […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: