June 23, 2011 at 12:43 pm
I am trying to come up with DMV query that allows me to pull most expensive query with missing indexes. I tried the query from Pinal Dave to pull most expensive queries from DMV but when I join with query plan to pull associated missing index (from DMV) it failed with error
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Can anyone tell me what I am doing wrong?
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan,
convert(xml, qtp.query_plan),
(
SELECT
index_node.value('(../@Impact)[1]', 'float') as index_impact,
index_node.query('concat(
string((./@Database)[1]),
".",
string((./@Schema)[1]),
".",
string((./@Table)[1])
)') as target_object_name,
replace(convert(nvarchar(max), index_node.query('for $colgroup in ./sp:ColumnGroup,
$col in $colgroup/sp:Column
where $colgroup/@Usage = "EQUALITY"
return string($col/@Name)')), '] [', '],[') as equality_columns,
replace(convert(nvarchar(max), index_node.query('for $colgroup in ./sp:ColumnGroup,
$col in $colgroup/sp:Column
where $colgroup/@Usage = "INEQUALITY"
return string($col/@Name)')), '] [', '],[') as inequality_columns,
replace(convert(nvarchar(max), index_node.query('for $colgroup in .//sp:ColumnGroup,
$col in $colgroup/sp:Column
where $colgroup/@Usage = "INCLUDE"
return string($col/@Name)')), '] [', '],[') as included_columns
from (select convert(xml, qtp.query_plan) as xml_showplan) as t
outer apply t.xml_showplan.nodes('//sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex') as missing_indexes(index_node))
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, statement_start_offset, statement_end_offset) qtp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
June 23, 2011 at 2:26 pm
This might be interesting to look at.
http://kenj.blogspot.com/2009/07/anatomy-of-missing-index-what.html
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply