SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 1

Index never stops amazing me, there are so much to learn about Index that I never feel that there is enough knowledge out about this subject. If you are interested you can watch my Indexing Course on Pluralsight for further learning on this subject. On my production server I personally use embarcadero DB Optimizer for all performance tuning and routine health check up. I will be interested to know what is your feedback about the product.

Instead of going on the theory overload – let us start with this blog post as a conversation between two individuals – Jon and Mike. These are just random names. Jon is senior and experienced SQL Server Expert and Mike is beginner with SQL Server.

Mike – What is Redundant Index?

Jon – Indexes are redundant when they have similar columns as a part of a definition. Additionally, the indexes are considered redundant when their first few columns are in the same position with same order by direction are also considered as a redundant.

Mike – Would you please explain it with examples?

Jon – Sure, Let us assume we have two indexes:

Index 1: Col1, Col2, Col3
Index 2: Col1, Col2, Col3

Now if you look at them – they have the same columns as a part of their definition, so they are indeed redundant indexes. However, look at the following scenario:

Index 3: Col1, Col2
Index 4: Col1, Col2, Col3

In this case they are also considered as a redundant because the position of the Col1, Col2 are same in both of the index. It is quite commonly considered that if the initial positions of the columns are the same, they are redundant.

However, there is one more concept here to be looked at as well before we make certain about their redundancy. Look at the following indexes:

Index 5: Col1 ASC, Col2 DESC
Index 6: Col1 DESC, Col2 DESC, Col3 ASC

In this case if the initial positions are the same, they are not redundant as the order of the column is not the same.

There are lots more to discuss, but this is just to give you an initial idea. There is one more concept we should consider before calling any index redundant is Included Index. Here is the simple scenario for it.

Index 7: Col1 ASC Included (col2)
Index 8: Col1 ASC Included (col3)

You can notice they have same initial column, but the Included columns are totally different.

Mike – Thanks, I got it. It seems that Redundant Indexes are not good and they should be dropped correct.

In case of Index 1 and Index 2 I think we should drop either of the one.

In case of Index 3 and Index 4 I believe Index 4 has more columns and covering, so we should keep it and drop the other one.

In case of Index 5 and Index 6, they are both different indexes so we should keep both.

In case of Index 7 and Index 8, again, they are both different index in this case. They can be redundant if the included columns are overlapping to each other.

Am I correct to say this?

Jon – Very good analysis. You are very close to the understanding. Generally, redundant indexes are not good and they should be absolutely addressed. In most cases, redundant cases should be dropped. 

Mike – Ahha, so in the most cases indexes should be dropped. Ok, so is there any script or guidance to detect redundant indexes for the most cases.

Jon – Sure, here is the script which does that – however, this query just addresses the scenario of the Index 3 and Index 4. It does not talk about Included Columns or Index Order (ASC or DESC). Just use that for a start, but do your analysis on this subject before you drop your indexes. You still have to check in order of the index and included columns as well.

Mike – Perfect, I understand that the script is just for a quick start and not the complete solution. Now you mentioned “In Most Cases” – what are the special cases. What are the cases when an Index which absolutely qualify for the redundant index, but should not be dropped. Would you please explain the special cases?

Jon – Absolutely – there are always special cases. For example the width of the column matters.

Mike – Okey I would love to learn more about this – would you please explain.

Jon – Absolutely – I have a working example of it – Checkout Monday’s blog post. I will explain you in detail.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index
Previous Post
SQL SERVER – Wrap SQL Code in SSMS – SQL in Sixty Seconds #038 – Video
Next Post
SQL SERVER – Cursor, Truncate Log and More – Memory Lane #010

Related Posts

8 Comments. Leave new

  • Thanks Pinal,

    Would be nice to know more about order of the columns
    e.g. INDEX1: C1,C2,C3
    INDEX2: C2,C1,C3
    Do they come under redundant and can we drop one of them?

  • sp_BlitzIndex from Brent’s team is also a great tool for this.

  • In the case of Index 7 and Index 8, we can create a new covering Index 9: Col1 ASC Included (col2) (col3) then drop both 7 and 8, correct?

  • Nicely explained sir.. Loved reading it …:)

  • Nice try. I’ve never heard redundant defined that way and I’ve been doing this for a VERY long time. An index is redundant if it is superfluous, meaningless, unnecessary, etc. A duplicate index is redundant and duplicate indexes should be dropped. There is no “similar” or “part of” to this. Your first scenario is redundant, because it is a duplicate (they all use an ASC sort order since that is the default when you didn’t specify). None of the rest of the indexes are duplicate and the only way to determine if they are redundant is to find out if the Query Optimizer actually uses them. Not a single piece of an index has to be the same as any other index in a database and it can still be redundant. Similarity to any other index has nothing to do with it.

  • It is good one

  • Very well explained Pinal. Can you pls respond on Andre question?


Leave a Reply