Using sys.dm_db_index_usage_stats for unhelpful or unused indexes 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;
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!