Which Queries used the index???

Question

Which Queries used the index???

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
August 10, 2010 at 5:11 am

#142345

Hi All
I am using this query to get index usage stats:
select object_name(a.object_id) Table_Name,name Index_name,a.* from sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b on a.object_id=b.object_id and a.index_id=b.index_id
where database_id=db_id()
It gives me the count for user_seeks,User_scans,User_lookups and User_updates. Now ,say the value for User_seeks is 50 for a particular index, is there a way to find out which queries caused these 50 seeks??
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

Not directly and not easily.

What you can do is an XQuery against the queries in cache to see which have accessed a given index, but the read values of the execution plan and the read values on the DMV's you're accessing aren't the same, so you can't exactly compare the two. Plus, this depends on the execution plan still being in cache.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

yeah, I was expecting tha it wont be direct. Can yo pls explain:

"XQuery against the queries in cache to see which have accessed a given index"

If try sys.sycacheObjects or sys.dm_exec_cached_plans, I dont see any mention of indexes there.

So how do I find index used?

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

The execution plan stores all the information of what is accessed. This information is stored as XML. So you can run an XML query against the execution plan to return indexes used.

I have an example on my web site where I query the execution plan to find missing index statements. It's not what you're looking for, but you can use it as a basis for what you need. You just need to look at the operators and identify any that are using the index in question.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

This should get your pretty close, but I am going to provide fair warning that it takes a long time to execute, depending on the size of your plan cache, and it returns a very large, basically unweildy, result set because every index operation performed in every plan gets split out of it as a result set.

WITH XMLNAMESPACES

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,

n.query('.'),

i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,

i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,

i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,

i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,

i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,

i.query('.'),

(SELECT DISTINCT cg.value('(@Column)[1]', 'VARCHAR(128)') + ', '

FROM i.nodes('./OutputList/ColumnReference') AS t(cg)

FOR XML PATH('')) AS output_columns,

(SELECT DISTINCT cg.value('(@Column)[1]', 'VARCHAR(128)') + ', '

FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicate//ColumnReference') AS t(cg)

FOR XML PATH('')) AS seek_columns,

i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate

FROM (

SELECT query_plan

FROM (

SELECT DISTINCT plan_handle

FROM sys.dm_exec_query_stats WITH(NOLOCK)

) AS qs

OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp

--SELECT @xml

) as tab (query_plan)

CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)

CROSS APPLY n.nodes('.//RelOp') as s(i)

WHERE i.exist('./IndexScan') = 1

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Excellent Jonathan. Thanks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Just a sample of what will be in my session on querying the plan cache at PASS Summit this year. 🙂

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

There is another version from Kendal Van Dyke that allows for the use of TOP N and thereby limits how nasty the query actually is. You can review this one here - http://kendalvandyke.blogspot.com/2010/07/finding-key-lookups-in-cached-execution.html

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ya I gave it a whirl on my prod server : 15 minutes for 32K rows... and it's not like we have a slow a busy server here!!!

Adiga One Orange Chip Points: 27284 More actions · Answer 9

Thanks Jonathan ! This one will surely head to the vault !

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 10

This is really good Jonathan...thanks so much....

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.