Saturday, October 13, 2012

How to find index usage against table

If you want to find out index usage for user table then  sys.indexes and sys.dm_db_index_usage_stats that give you more details. You should look for user seeks counts on this result

Select OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,i.type_desc,
ius.user_seeks,ius.last_user_seek
From sys.indexes i
Inner Join sys.dm_db_index_usage_stats ius
on i.object_id = ius.object_id
and i.index_id = ius.index_id
and ius.database_id = DB_ID()
where ius.object_id = OBJECT_ID('dbo.Customers')
go