SQL Server – Overlapping NC Indexes, Drop Question

All we need is an easy explanation of the problem, so here it is.

I’m doing some index cleanup/tuning on a SQL ’14 DB and ran across a 6MM row, 16 field table with a sound-looking clustered index and two nonclustered, non-unique indexes having the following definitions:

  • IX_1 ON [Tbl1] ([field1])
  • IX_2 ON [Tbl1] ([field1], [field2]) INCLUDE ([field3], [field4])

The read/write data for the two indexes shows IX_1 currently with 5 million Seeks/Scans against it vs. 10k Seeks/Scans against IX_2, with equal updates for each. My index tuning sense says to drop IX_1 without a second thought as it’s fully covered by IX_2. But the the drastic preference of the optimizer to use IX_1 spooks me a little. Is there any reason to think twice about dropping IX_1 in a case like this? Thanks.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

When two indexes both satisfy a query, you’ll find that the smaller of the two is selected. Often, this means the narrower index, but it also holds true with fully duplicated, identical indexes, with the physically smaller one being preferred.

Unless you’re scanning the index and the full size matters, your index tuning Spidey Sense is right. Drop IX_1 and your queries will use IX_2. Those seeks will hardly notice.

Method 2

If you drop IX_1, all queries that were previously using it will now start using IX_2. With IX_2 being larger (one more key field and two included fields), it has:

  • More non-leaf level pages to support the second key column
  • More leaf level pages to store the INCLUDE column values for each row

Any queries that currently use IX_1 most likely don’t need these extra fields, otherwise they’d already be using IX_2. With IX_1 gone, they’re going to have to read a lot more data to be able to produce the same result set.

A single column index like IX_1 is typically very low impact to write workloads, depending on the datatype. I can’t see any compelling reason to drop it.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply