What is Consolidation of Index? – Interview Question of the Week #212

Question: What is Consolidation of Index?

Answer: In the recent SQL Server Performance Tuning Practical Workshop, I was asked this question and the answer to this question is really not as straight forward as want to have.

Here is an example of what I consider as the consolidation of an index.

For example on any table, if we have the following indexes, our challenge is to consolidate them.

Index 1: CREATE INDEX Index1 ON Table1 (Col1)
Index 2: CREATE INDEX Index2 ON Table1 (Col1, Col2)
Index 3: CREATE INDEX Index3 ON Table1 (Col1, Col3)
Index 4: CREATE INDEX Index4 ON Table1 (Col3, Col1)
Index 5: CREATE INDEX Index5 ON Table1 (Col2)
Index 6: CREATE INDEX Index6 ON Table1 (Col1) INCLUDES (Col2, Col3)
Index 7: CREATE INDEX Index7 ON Table1 (Col1) INCLUDES (Col4, Col5, Col6)
Index 8: CREATE INDEX Index8 ON Table1 (Col4) INCLUDES (Col1, Col3)
Index 9: CREATE INDEX Index9 ON Table1 (Col1, Col4)

Let us try to consolidate all the 9 indexes into as few indexes as them. Let us start making the group of indexes which can be potentially consolidated.

Indexes with First Column Same

Gather all the indexes where the first column of the index is the same.

Index 1: CREATE INDEX Index1 ON Table1 (Col1)
Index 2: CREATE INDEX Index2 ON Table1 (Col1, Col2)
Index 3: CREATE INDEX Index3 ON Table1 (Col1, Col3)
Index 6: CREATE INDEX Index6 ON Table1 (Col1) INCLUDES (Col2, Col3)
Index 7: CREATE INDEX Index7 ON Table1 (Col1) INCLUDES (Col4, Col5, Col6)
Index 9: CREATE INDEX Index9 ON Table1 (Col1, Col4)

Now let us collect all the key columns together and includes column together.

Index New1: CREATE INDEX IndexNew1 ON Table1 (Col1, Col2, Col3, Col4) INCLUDES (Col2, Col3, Col4, Col5, Col6)
(do not run the above index, it will throw an error)

Now from the index above remove the columns from Includes which are already available in the key column.

Index New1: CREATE INDEX IndexNew1 ON Table1 (Col1, Col2, Col3, Col4) INCLUDES (Col5, Col6)

There you go, you have consulted index 1, 2, 3, 6, 7 and 9 into a single index IndexNew1.

Now the question would be, what is the good order for key columns, Col1, Col2… or Col1, Col2, Col4 etc.

This is where things get tricky and there is no clear answer. It is important that we keep the Col1 as the first index and try our various indexes where the rest of the columns are in different order. The index which is the most optimal for your query will be utilized.

Additionally, my experience says it is important to have the second column with the highest cardinality level among all the other columns. This subject is very deep so I will write separately on this subject.

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