Duplicate Indexes Advisor

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

DupeIndexesAdvisor2

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.


Enroll Today!
SteveStedman5
SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

4 Comments on “Duplicate Indexes Advisor

  1. Are you testing for things like an index being a PK (that would be in addition to it being unique) or on an FK column (in either direction)?

  2. Can you add checks for filtered index? I am getting duplicate indexes but the filters on them are defined differently.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

To prove you are not a robot: *