Select Page

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.

4 Comments

  1. TheSQLGuru

    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)?

    Reply
  2. SQLPerfMan

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

    Reply
    • Steve Stedman

      Good idea. I will work on removing filtered indexes from the duplicated indexes report..
      -Steve Stedman

      Reply
      • Steve Stedman

        Filtered indexes are not included as part of the duplicate check. This is to ensure that it doesn’t recommend deleting a filtered index that has the same columns as a non filtered index.

        -Steve Stedman

        Reply

Submit a Comment

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

To prove you are not a robot: * Time limit is exhausted. Please reload CAPTCHA.