Microsoft® SQL Server 2012 Unleashed

Identifying Unused Indexes

As mentioned previously in this chapter, each index on a table adds additional overhead for data modifications because the indexes also need to be maintained as changes are made to index key columns. In an OLTP environment, excessive indexes on your tables can be almost as much of a performance issue as missing indexes. To improve OLTP performance, you should limit the number of indexes on your tables to only those absolutely needed; you definitely should eliminate any unnecessary and unused indexes that may be defined on your tables to eliminate the overhead they introduce.

Fortunately, SQL Server provides a DMV that you can use to identify which indexes in your database are not being used: sys.dm_db_index_usage_stats ...