Site icon IT Tutorial

SQL Server Index Usage Stats


SQL Server DBA should monitor all indexes and which of indexes are using and which are not.


If the index is not using or using very rare then SQL Server DBA should drop it to not provide extra load to the database.

You can find all indexes usage statistic with following script.


select objname = OBJECT_NAME(s.object_id),
index_id = i.index_id,
user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats as s
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where database_id = DB_ID('DEVECI')
and OBJECTPROPERTY(s.object_id,'IsUserTable')=1
order by (user_seeks + user_scans + user_lookups) desc;


Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

 1,846 views last month,  1 views today

Exit mobile version