The following DMV query retrieves the usage statistics for existing indexes.
- User Seeks – A high number indicates a well utilized index.
- User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns
- User Lookups – Indicates a different index was used for criteria and the actual data was looked up from this index for the select list
- User Updates – Number of times the index was updated with additional records
SELECT
ObjectName = object_schema_name(idx.object_id) + '.' + object_name(idx.object_id)
,IndexName = idx.name
,IndexType = CASE
WHEN is_unique = 1 THEN 'UNIQUE '
ELSE '' END + idx.type_desc
,User_Seeks = us.user_seeks
,User_Scans = us.user_scans
,User_Lookups = us.user_lookups
,User_Updates = us.user_updates
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats us
ON idx.object_id = us.object_id
AND idx.index_id = us.index_id
AND us.database_id = db_id()
WHERE object_schema_name(idx.object_id) <> 'sys'
ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC
