Sql-server – How to find I.O. statistics (by index) for queries in progress

sql serversql-server-2008

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?

Best Answer

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.

Related Question