Product has been added to your cart.

Duplicate Indexes

Your indexes have been diagnosed with multiple personality disorder. Ever feel like there’s more people in the room than need to be there? Our checks say that your indexes feel that way, too.

How do I read the results?

To understand which index has potential or actual duplicates, look at the index_definition column. Results in this section are sorted by alphabetical order of the list of keys and includes. This sorting means that potential duplicates will be next to each other.

There are other important things you need to know about the indexes. Because of this the index_definition is prefixed with codes. Here are the meaning of the codes:

  • [CX]: Clustered Index
  • [UNIQUE]: You see where this one’s going
  • [PK]: Primary Key
  • [KEYS]: The list of key columns in the index
  • [INCLUDES]: If the index contains included columns, this will be present (after the [KEYS] section) and will be followed by a list of the includes.
  • [DISABLED]: This index is disabled. Explanations of disabled indexes are explained here.

What’s so wrong with multiple index personalities?

NonClustered indexes are very powerful in SQL Server. You want to have the right nonclustered indexes in place to help support queries reading table and make them faster– that has a lot of benefits, like reducing IO, CPU, and memory usage.

But on the flip side, you can easily have too much of a good thing. Duplicate indexes take up double the room in SQL Server– and even if indexes are COMPLETELY identical, SQL Server may choose to use both of them.

Duplicate indexes essentially cost you extra IO, CPU, and Memory, just the things you were trying to SAVE by adding nonclustered indexes! And that’s a little bit crazy.

Duplicate keys

Indexes diagnosed with duplicate keys have exactly that: completely duplicate keys. (Not sure what keys are? Watch How to Think Like the Engine.)

Duplicate keys are a red flag, but don’t jump into action too soon. Although indexes may have duplicate keys, there are important things to be aware about.

  • Are the duplicates both nonclustered?You may have a case where one of your duplicates is the clustered index— and that means that index is the data in the table itself. You clearly wouldn’t want to go dropping that index unless you want to lose the whole table.
    • You can identify a clustered index easily– it is always index id 1, and the index_definition column will contain a “[CX]”. For example this index is a clustered index and a primary key: [CX] [PK] [KEYS]  BusinessEntityID
    • There are rare cases where it is useful to have a duplicate nonclustered index on the same column as the clustering key, but this is the exception rather than the rule.)
  • Is one of the duplicates serving as a primary key? One of your duplicates may be a primary key, in which case it is also likely worthy of special treatment. If an index is a primary key, the index_definition column will contain a “[PK]” (check out the example above).
  • Do the indexes have different included columns?  Included columns are listed in the index_definition column. They are prefixed by the term [INCLUDES]
  • How much is each index being used?  Find this from the index_usage_summary column. Even if the indexes are completely identical, SQL Server may choose to use them both and you may see usage on them. If they have different included columns, you may see a very big variation in index usage, however.

Remember, index tuning is complicated! sp_BlitzIndex gives you a lot of information to help you see everything at once, but it’s up to you to decide the right thing to do.

If you’re not sure about the right course of action, step back and learn more. Don’t put together a change plan to move forward until you’re certain about the choices you’re making.

Borderline duplicate keys

Borderline duplicate keys start with the same key column, but do not have completely identical keys.

In many cases, indexes with borderline duplicate keys are very easy to combine into a single index. However, before you assume this is the right course of action, look closely at the index types and usage as we describe above. It may change your decisions.