Duplicate Indexes Advisor

The duplicate index advisor shows you details on duplicate indexes found in your database.

Duplicate indexes are bad for the following reasons:

• Duplicate indexes slow down inserts and updates on the indexed table.
• Duplicate indexes may confuse or slow down the query optimizer when analyzing which index to use.
• Duplicate indexes add no value.
• Duplicate indexes take up more memory and disk space.
• Duplicate indexes increase the size of the backup file and transaction log files (transaction logs only when data changes).
• Add to the amount of data that replicated databases need to transfer.

When removing duplicate indexes be sure that that you delete the right indexes.  For instance if there is a duplicate index that is clustered and another that is non clustered, it usually would be safer to delete the non-clustered index and leave the clustered index in place.

You may not want to delete unique indexes without a better understanding of why there are unique.

Also consider the included columns in an index.  Although two indexes may have the same columns, they may additionally have different included columns.

It would be nice if SQL Server had a limit to the number of duplicate indexes that could add to a table.

The Duplicate Indexes Advisor is accessed from the Duplicate Indexes Report.