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