Indexing is a fascinating topic in SQL Server, with so much to learn and explore. In this blog post, we will discuss the concept of redundant indexes and why they should be addressed.

Let’s imagine a conversation between two individuals, Jon and Mike, to understand the concept better.

Mike: What is a redundant index?

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

Mike: Can you provide some examples?

Jon: Certainly. Let’s consider the following indexes:

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

In this case, both indexes have the same columns as part of their definition, making them redundant.

Now, let’s look at another scenario:

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

Even though Index 3 and Index 4 have different numbers of columns, they are still considered redundant because the position of Col1 and Col2 is the same in both indexes.

However, there is another concept to consider before determining redundancy. Take a look at these indexes:

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

In this case, even though the initial positions of the columns are the same, the order of the columns is different. Therefore, these indexes are not redundant.

There is one more concept to consider before calling an index redundant, which is the concept of included columns. For example:

Index 7: Col1 ASC Included (Col2)
Index 8: Col1 ASC Included (Col3)

Although these indexes have the same initial column, the included columns are different. Therefore, they are not redundant.

Mike: So, redundant indexes are not good and should be dropped, right?

Jon: Yes, you are correct. In most cases, redundant indexes should be dropped. For example, in the case of Index 1 and Index 2, we should drop one of them. In the case of Index 3 and Index 4, if Index 4 has more columns and provides better coverage, we should keep it and drop the other one. In the case of Index 5 and Index 6, since they are different indexes, we should keep both. And in the case of Index 7 and Index 8, they are also different indexes.

Mike: Is there a script or guidance to detect redundant indexes?

Jon: Yes, there are scripts available to detect redundant indexes. However, it’s important to note that these scripts may not cover all scenarios, such as included columns or index order (ASC or DESC). It’s recommended to use them as a starting point and perform your own analysis before dropping any indexes.

Mike: Are there any special cases where an index may qualify as redundant but should not be dropped?

Jon: Absolutely. One example is the width of the column. I will explain this in detail in my upcoming blog post on Monday. Stay tuned!

Understanding redundant indexes is crucial for optimizing the performance of your SQL Server database. By identifying and removing redundant indexes, you can improve query performance and reduce storage overhead.

Thank you for reading this blog post. If you have any questions or would like to share your thoughts, please leave a comment below.