I recently attended SQL Saturday #255 and specifically the performance tuning pre-con put on by Grant Fritchey. It was a great seminar and well worth the time (a very long day including travel) and money (not much). One of the many interesting discussions was on the missing index DMVs. It seems that these DMVs, while very helpful, are not cleared out on a regular basis. In fact no one in the room knew when/how/or even if these DMVs are cleared. This has at least two effects. First that when you create an index the entry(s) in the DMVs don’t clear and second entry(s) in the DMVs could be from queries that ran months ago and are no longer needed.

So what’s the solution? Well one is to pull the missing indexes directly out of the query plan. Now as it happens Grant has a great query to do just that. Unfortunately it wasn’t exactly what I needed. I needed a query that would list query and index side by side along with various helpful statistics.

So I started playing and not only got the query I wanted but found out something rather interesting. It turns out that when you view a query plan it shows only the first missing index in the plan. So if you look at the query plan for a batch with multiple queries and they have multiple “suggested” indexes you will only see the first one. I may have to go see if there is a connect entry for that one.

In case anyone finds it helpful here is the query I worked up. I’m afraid I’m not all that great with XML and xquery so if someone has a better/faster way of handling this I would love to hear it.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
AS sp)
SELECT DB_NAME(CAST(pa.value AS INT)) QueryDatabase
,s.sql_handle
,OBJECT_SCHEMA_NAME(st.objectid, CAST(pa.value AS INT)) AS ObjectSchemaName
,OBJECT_NAME(st.objectid, CAST(pa.value AS INT)) AS ObjectName
,SUBSTRING(st.text,s.statement_start_offset/2+1,
((CASE WHEN s.statement_end_offset = -1 THEN DATALENGTH(st.text)
ELSE s.statement_end_offset END) - s.statement_start_offset)/2 + 1)  AS SqlText
,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
-- query_hash is useful for grouping similar queries with different parameters
--,s.query_hash
--,cast (p.query_plan as varchar(max)) query_plan
,p.query_plan
,mi.MissingIndex.value(N'(./@Database)[1]', 'NVARCHAR(256)') AS TableDatabase
,mi.MissingIndex.value(N'(./@Table)[1]', 'NVARCHAR(256)') AS TableName
,mi.MissingIndex.value(N'(./@Schema)[1]', 'NVARCHAR(256)') AS TableSchema
,mi.MissingIndex.value(N'(../@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact
,ic.IndexColumns
,inc.IncludedColumns
FROM (-- Uncomment the TOP & ORDER BY clauses to restrict the data and
-- reduce the query run time.
SELECT --TOP 200
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.statement_start_offset
,s.statement_end_offset
--,s.query_hash
FROM sys.dm_exec_query_stats s
-- ORDER BY s.total_elapsed_time DESC
) AS s
CROSS APPLY sys.dm_exec_text_query_plan(s.plan_handle,statement_start_offset,statement_end_offset) AS pp
CROSS APPLY (SELECT CAST(pp.query_plan AS XML) AS query_plan ) 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')
AS mi (MissingIndex) 
CROSS APPLY (SELECT STUFF((SELECT ', ' + ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)')
FROM mi.MissingIndex.nodes('./sp:ColumnGroup')
AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
WHERE t1.ColumnGroup.value('./@Usage', 'NVARCHAR(256)') <> 'INCLUDE' 
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'), 1, 2, '') AS IndexColumns ) AS ic 
CROSS APPLY (SELECT STUFF((SELECT ', ' + ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)')
FROM mi.MissingIndex.nodes('./sp:ColumnGroup')
AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
WHERE t1.ColumnGroup.value('./@Usage', 'NVARCHAR(256)') = 'INCLUDE' 
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'), 1, 2, '') AS IncludedColumns ) AS inc 
CROSS APPLY sys.dm_exec_plan_attributes(s.plan_handle) pa
CROSS APPLY sys.dm_exec_sql_text (s.sql_handle) st
WHERE pp.query_plan LIKE '%MissingIndexes%'
  AND pa.attribute = 'dbid'

Filed under: Index, Microsoft SQL Server, Pass, Performance, Query Plans, SQLServerPedia Syndication, System Functions and Stored Procedures Tagged: microsoft sql server, Performance, system functions, XML, xQuery