Which query is scanning that index?

re: Which query is scanning that index?

StatisticsIO — Sun, 27 Apr 2008 16:45:10 GMT

That is definately cool. I looked around for a way to do it myself and then added a connect item. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328184

Please vote.

microsoft » Blog Archive » Which query is scanning that index?

Anonymous — Sun, 27 Apr 2008 23:23:57 GMT

PingBack from http://microsoft-site2008yr.freehostia.com/?p=5895

Greg Linwood

Greg_Linwood — Sun, 27 Apr 2008 23:44:53 GMT

Hi StatisticsIO

Your Connect item talks about matching rows in the missing indexes DMV to that queries that triggered them.

UC actually provided an alternative version of this query that does this. Just replace the last few lines of the query I posted with the following code to achieve what you're after..

from qp.query_plan.nodes(
N'//s:Batch/s:Statements/s:StmtSimple/s:QueryPlan[1]//
s:RelOp[@PhysicalOp = ("Index Scan")]/*[local-name() = ("IndexScan")]/
s:Object[@Database = ("[DBNameHere]")
and @Table = ("[TableNameHere]")
and @Index = ("[IndexNameHere]")]'
) as ro(relop)
) as r
where exists(
select *
from sys.dm_db_missing_index_details as m
where m.database_id = db_id(parsename(r.DatabaseRef, 1))
and quotename(object_schema_name(m.object_id, m.database_id)) = r.SchemaRef
and quotename(object_name(m.object_id, m.database_id)) = r.TableRef);

Which queries are missing indexes?

Anonymous — Thu, 08 May 2008 21:06:59 GMT

One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several

How earn $ 1000 a week?

AleVaserman — Fri, 05 Mar 2010 18:05:55 GMT