Consolidating Similar Indexes?

In looking through some of our databases, in the past we were perhaps a bit eager to take any suggestion from the DTA or the estimated query plan, and so we have a lot of indexes and many of them similar to others. By "similar" I mean they have the same key, but different or overlapping includes. Here are some examples of 3 indexes I would describe as "similar":

CREATE INDEX Index1 ON Table1 (Col1)

CREATE INDEX Index2 ON Table1 (Col1) INCLUDES (Col2)

CREATE INDEX Index3 ON Table1 (Col1) INCLUDES (Col3)

My expectation is that consolidating these 3 into 1 index (ie. CREATE INDEX Index4 ON Table1 (Col1) INCLUDES (Col2, Col3)) would clean things up and improve insert/update/delete on the table without harming query performance. Is that accurate? Is there any better or more efficient way to go about this than to drop the existing similar indexes and then creating the new index?

Thanks!

Consildation of overlapping indexes is typically a good practice.

• You'll see improved performance on inserts, due to fewer index write per insert.

• Deletes will be faster, as fewer indexes need to be deleted.

• In most cases, updates will be faster too, depending on the columns being updated.

• You consume less disk space due to less duplicating of data.

Those points alone should likely help over performance of the table, as those locks are held for shorter periods of time. A side effect of shorter locks could also possibly lead to improvement in performance of selects against the table, as they may be less likely to run into locks.

Additionally, the order of included columns doesn’t have an impact on performance, as these columns are not sorted in the index. In other words, a query attempting to read col3 from and index where col3 is the first included column would perform the same as reading from an index where it's the second included column.

In the example you provided, Index4 would likely be the best index to use for consolidation of the initial 3 indexes.

Other points to consider before consolidating indexes

• As you add more columns to an index, you do increase the size of the index, leading to potentially slower reads from the index.

• Review the data types of the columns being consolidated.

• Review the read/writes patterns against the table and the affected indexes.

• Review query plans before and after the change.

For example, if Index A may be read from 1,000 time per minute and have very few small columns, such as varchar(20). Index B may be read from once a month during reporting and have column with a data type of varchar(2000). In this case, consolidation would likely have a negative impact on overall performance during regular production query times, with little to no benefit on the monthly reporting job.

With that said, index consolidation still makes sense the majority of the time. But that's just a guideline, not a rule. You need to review all factors before making the final decision, and decide what you're goal is in that scenario. Brent Ozar often suggests a guideline of staring with no more than 5 columns per index, and no more than 5 indexes per table. Ronaldo actually linked to a good article in the comments from Brent Ozer on SQL Server Index Tuning Tip: Identify Overlaps

StackExchange Database Administrators Q#313872, answer score: 8