Mistake: treating fragmentation as the cause of every index problem. sys.dm_db_index_usage_stats helps test that assumption by reporting a row for each used index since the Database Engine last cycled, including operation counts and last-use times. section 11.2
Compare seeks, scans, and lookups before changing indexes. External fragmentation harms scans but does not affect seeks or lookups, so a heavily seeked index may require investigation elsewhere. The DMV excludes memory-optimized indexes; use sys.dm_db_xtp_index_stats for those. section 11.12 section 11.2
Discover index usage by operation
Run this in the relevant context to connect usage statistics with index names and types: SELECT OBJECT_NAME(ius.object_id) AS TableName, i.name AS IndexName, i.type_desc, ius.user_seeks, ius.user_scans, ius.last_system_lookup, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i ON ius.index_id = i.index_id AND ius.object_id = i.object_id WHERE DB_NAME(database_id) = 'Marketing'; Treat the results as activity since the last Database Engine cycle, not permanent history. section 11.2
Listing 11.2 Discovering index usage by index operation
SELECT
OBJECT_NAME(ius.object_id) AS TableName
, i.name AS IndexName
, i.type_desc
, ius.user_seeks
, ius.user_scans
, ius.last_system_lookup
, ius.last_user_seek
, ius.last_user_scan
, ius.last_user_lookup
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i
ON ius.index_id = i.index_id
AND ius.object_id = i.object_id
WHERE DB_NAME(database_id) = 'Marketing' ;
Use the complete diagnostic listing to inspect index names, types, operation counts, and last-use timestamps. section 11.2
