Duplicate Indexes on your database can be very expensive from a number of different perspectives.
- Increases database size with no value.
- Increase database backup size with no value.
- Slows inserts, updates and deletes because there are duplicate indexes that need to be updated.
- Can slow queries as the query optimizer needs to consider both indexes when analyzing query performance and determining a plan.
- Increase Deadlocks.
- Bloated transaction log size.
- Add to the amount of data that replicated databases need to transfer.
With the Duplicate Index Report, you can track down the waste and eliminate it.
From the duplicate indexes page, you can jump to to the Duplicate Index Advisor by double clicking on the grid or on the chart.
On SQL Server 2008 and newer, filtered indexes are excluded from the Duplicate Indexes report.