How do I find I.O. statistics (by index) for queries in progress

Question

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?

Answer 1

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.

Answer 2

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')

Answer 3

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.