Using sys.dm_db_index_usage_stats for unhelpful or unused indexes

Question

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;

Answer 1

Just throwing this out there, because I remember the bad old days of having a different script for every index ailment you read about on the internet.

I co-author a free stored procedure called sp_BlitzIndex that will tell you about a whole bunch of stuff going on with your indexes all at once.

Some examples:

• Aggressively locked indexes
• Duplicates (based on keys)
• Borderline duplicates (based on first key column)
• Unused (with differentiation based on number of writes
• High value missing indexes
• HEAPs
• And more!

The simplest example run is probably like this. Just change the database name.

EXEC sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4

There are a ton more ways to run it, just check out the docs at the GitHub link.

Hope this helps!