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

Question: What is index consolidation? It means reducing overlapping indexes while preserving the access paths the workload actually needs. Combining their column lists is a starting point for investigation, not proof that one index replaces them all.

This question came up in my Performance Tuning Practical Workshop. The answer wasn’t as straightforward as the class hoped. Here is the same nine-index challenge, with the original INCLUDES spelling corrected to SQL Server’s INCLUDE syntax:

CREATE INDEX Index1 ON dbo.Table1 (Col1);
CREATE INDEX Index2 ON dbo.Table1 (Col1, Col2);
CREATE INDEX Index3 ON dbo.Table1 (Col1, Col3);
CREATE INDEX Index4 ON dbo.Table1 (Col3, Col1);
CREATE INDEX Index5 ON dbo.Table1 (Col2);
CREATE INDEX Index6 ON dbo.Table1 (Col1) INCLUDE (Col2, Col3);
CREATE INDEX Index7 ON dbo.Table1 (Col1) INCLUDE (Col4, Col5, Col6);
CREATE INDEX Index8 ON dbo.Table1 (Col4) INCLUDE (Col1, Col3);
CREATE INDEX Index9 ON dbo.Table1 (Col1, Col4);

Start by grouping Index1, Index2, Index3, Index6, Index7 and Index9. They all begin with Col1. Index4 begins with Col3, Index5 with Col2, and Index8 with Col4; those different leading keys may serve different predicates.

My original candidate was:

CREATE INDEX IndexNew1 ON dbo.Table1 (Col1, Col2, Col3, Col4)
INCLUDE (Col5, Col6);

The syntax is valid, and columns already in the key must not also appear in INCLUDE. But I originally went too far by saying we had consolidated those six indexes successfully. That still needs workload evidence.

For example, a query filtering Col1 and Col3 can seek both keys in Index3. In IndexNew1, Col2 sits between them. If the query doesn’t constrain Col2, Col3 may be evaluated as a residual predicate rather than providing the same efficient composite seek. Likewise, the new ordering does not automatically provide Index9’s Col1, Col4 order.

Coverage and ordering are different benefits. INCLUDE supplies values at the leaf level; it doesn’t make those columns part of the searchable, sorted key. A wider key also takes space and adds maintenance work, so a narrow Index1 might still be useful even when another index covers its columns.

Choose the key order from the actual predicates, joins and ordering requirements. Distinctness is useful information, but “put the highest-cardinality column second” is not a rule that overrides those requirements. Compare actual plans, logical reads and write overhead across the relevant workload before retiring an index. Uniqueness, filters and constraint dependencies also need separate consideration.

So the useful workshop answer is: six indexes are candidates for consolidation, but this list alone cannot tell us the final minimum. The remaining Col2-, Col3- and Col4-leading indexes should not disappear merely because their columns occur somewhere in the wider candidate.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.