Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

By running SET STATISTICS IO ON before running a SQL query, I can get an idea of the number of logical reads performed on each index after the query completes.

By looking at the view sys.dm_exec_requests, I can get an idea of the number of logical reads (total) that have been performed so far for any query that's in progress.

How can I get an idea of how many logical reads have been performed on each index so far for queries in progress?

share|improve this question
    
I get the feeling that the answer would be something like: "You can't" –  Michael J Swart Oct 16 '12 at 1:41

3 Answers 3

You can see some information about the indexes that are in use right now using the DMV sys.dm_db_index_usage_stats. An example:

declare @dbid int 
select @dbid = db_id() 
select db_name(db_id()) as [database]
, objectname=object_name(s.object_id)
, indexname=i.name
, i.index_id 
, reads=user_seeks + user_scans + user_lookups 
, writes =  user_updates 
from sys.dm_db_index_usage_stats s join sys.indexes i  on i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1 
and s.object_id = i.object_id  and s.database_id = @dbid  and i.index_id <> 1
order by reads

(sample code from MSDN).

Reads and writes columns will grow as soon as the specific index is used for data gathering.

share|improve this answer
3  
But these are operation counts, not page reads. And there is no way to correlate them to a specific query that is currently running (imagine a busy system where other queries are also affecting this table). –  Aaron Bertrand Oct 15 '12 at 15:07
    
Thanks Marian, The info would still be helpful. Assuming I could isolate the activity on those indexes to just the query I'm curious about. Unfortunately, it's very difficult (right now) to make that assumption. –  Michael J Swart Oct 15 '12 at 15:28
    
@AaronBertrand: off course you're right, but it's kind of the easiest option to see indexes in live action. Going to match them to a specific query is not an option, unfortunately, but it's useful for a first line eyeballing process where you can match high IO in the system, with queries that have high IO (seen in dmvs or profiler) and specific indexes that have high number of IO operations. It's not appropriate for a busy production system where you want a precise monitoring platform, but it's good for a dev/qa system where you might do some debugging. –  Marian Oct 15 '12 at 20:22

This is a great question, that has got me thinking.

Ok, 2 disclaimers. Firstly, this feels very dirty, and I can't believe there is not a better way. Secondly, DO NOT RUN THIS ON A PRODUCTION SERVER because a) it uses undocumented features, and b) it clears your buffer cache.

The premis I have come up with is to tear down the buffer cache, find the page numbers of the index, run the query and then count the pages that have been written back to memory.

The following is how to implement it:

--Get the Object ID
select * from sys.objects
where name = 'Name Of Index'

--Get The Index ID
select *
from sys.sysindexes
where id = objectid

--Get the Page IDs
dbcc ind('mydatabase','mytable',indexid)

--Drop Index Pages From Cache
DBCC DROPCLEANBUFFERS

--Run query
select * from mytable option(index(2))

--Look in the cache to see how many pages are there
select * from sys.dm_os_buffer_descriptors
where page_id in (PageID,
PageID)
and database_id = DB_ID('my_database')
share|improve this answer
up vote 0 down vote accepted

So it turns out the answer is you can't find logical reads per index performed so far on queries that are in progress.

It is clear that Microsoft does track this information in order to report on the logical reads after the query completes, but there are no trace flags or dmvs that help provide this information.

Number of scans/seeks performed and number of pages in memory are close to the mark, but don't quite cut it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.