Welcome to the fifth part of our series on Redundant Indexes in SQL Server. If you haven’t read the previous parts, I highly recommend doing so to fully grasp the concepts discussed in this article.

In our production environment, we rely on embarcadero DB Optimizer for performance tuning and routine health checks. If you have any feedback on this tool, we would love to hear it.

Recap of Previous Parts

In Part 1, we introduced the concept of Redundant Indexes through a conversation between Mike and Jon. They discussed the fundamentals of Redundant Indexes.

In Part 2, Jon demonstrated a script where Redundant Indexes were found to be extremely useful with a smaller result set.

In Part 3, Jon showcased a script where Redundant Indexes were found to be extremely useful with a larger result set.

In Part 4, Jon presented an interesting case of Redundant Indexes, where the order of the index columns affected the behavior of the index.

If you haven’t read these previous parts, I encourage you to do so to fully understand the core points discussed in this article.

Exploring Duplicate Indexes

Mike poses an interesting question to Jon: “When I see two indexes with the same columns in the same order, are they duplicate indexes?”

Jon clarifies that if the included columns are also the same, then yes, they are indeed duplicate indexes. However, he introduces the concept of included column indexes, which Mike finds confusing.

Jon recommends reading an article on Included Column Indexes to gain a better understanding of the concept. He then proceeds to run a demo script to illustrate the concept.

Demo Script

Jon creates a table called SampleTable1 and sets up two non-clustered indexes:

• Index A1: Col1 ASC INCLUDE Col2 ASC
• Index A1: Col1 ASC INCLUDE Col3 ASC

He populates the table with data and runs two different queries on SampleTable1 to demonstrate the impact of included columns on index usage.

Observing the Execution Plan

Upon examining the execution plan, Jon points out that the first query is using the index IX_ST_Col1_Col2, while the second query is using the index IX_ST_Col1_Col3. This highlights how the included columns impact the usage of the index.

Mike expresses his confusion and decides to rely on a professional efficiency tool, embarcadero DB Optimizer, which Jon recommended in a previous conversation. He plans to download and explore the tool for better index usage insights.

Conclusion

Understanding redundant indexes and their impact on query performance can be complex. However, with the right tools and knowledge, you can optimize your SQL Server environment for better performance.

Stay tuned for more articles on SQL Server concepts and best practices. If you have any questions or topics you’d like us to cover, feel free to reach out.