Index Usage DMV

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 [cc lang=”sql”] 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 [/cc]

Continue reading ...