SELECT 
	xed.event_data.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
	xed.event_data.value('(data[@name="wait_type"]/text)[1]', 'varchar(25)') AS wait_type, 
	xed.event_data.value('(data[@name="duration"]/value)[1]', 'int')/1000/60.0 AS wait_time_in_min, 
	xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text, 
	xed.event_data.value('(action[@name="session_id"]/value)[1]', 'varchar(25)') AS session_id, 
	xData.Event_Data,
	fx.object_name
FROM sys.fn_xe_file_target_read_file ('system_health*.xel','system_health*.xem',null,null) fx
CROSS APPLY (SELECT CAST(fx.event_data AS XML) AS Event_Data) AS xData
CROSS APPLY xData.Event_Data.nodes('//event') AS xed (event_data)
WHERE fx.object_name = 'wait_info';

This is some amazing information, made better by the fact that it’s automatically collected. There are however a couple of downsides.

• The system health session only keeps so much information so on a busy system stuff can disappear pretty quickly.
• In the case of blocking it only shows the query that was blocked and the type of lock it was trying to get. It doesn’t tell you what query was actually blocking it, which somewhat limits resolving it.

 
Personally, I’m thinking about putting a process in place to store this information over time and summarize it. I can then use this as another place to look for low hanging fruit that needs to be fixed.

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'
]]>
					
					https://sqlstudies.com/2013/11/11/a-better-way-to-find-missing-indexes/feed/
			13
		
		
		1007
		
			sqlstudent144