I'm trying to discover indexes which may be in place which are only adding cost with no value to our database. I've come across sys.dm_db_index_usage_stats and from MSSQL Tips the following:
sys.dm_db_index_usage_stats
This view gives you information about overall access methods to your indexes. There are several columns that are returned from this DMV, but here are some helpful columns about index usage:
- user_seeks - number of index seeks
- user_scans- number of index scans
- user_lookups - number of index lookups
- user_updates - number of insert, update or delete operations
Does that mean if there is an index with very few / no seeks+scans+lookups but many updates, then the index is only costing us (have to update index when table is updated) but not gaining us anything?
Second question - Is the following query a decent way to discover this information?
SELECT DB_NAME([database_id]),
OBJECT_NAME(dm_db_index_usage_stats.[object_id], [database_id]),
TotalReads = (user_seeks + user_scans + user_lookups),
Writes = user_updates,
indexes.[name],
*
FROM sys.dm_db_index_usage_stats
LEFT JOIN sys.indexes on dm_db_index_usage_stats.[object_id] = indexes.[object_id] and dm_db_index_usage_stats.[index_id] = indexes.[index_id]
ORDER BY (user_seeks + user_scans + user_lookups) ASC, user_updates DESC;