0

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • Thanks AM, that was the confirmation I was looking for. Cheers.
    – MinnRick
    Mar 26, 2021 at 5:20
  • Makes sense that the optimizer would choose smaller over larger if other factors are equal. Also, in this case 99% of the index reads are seeks, which would seem to seal the deal.
    – MinnRick
    Mar 26, 2021 at 5:27
1

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.

Improve this answer
1
  • Thanks Dan. Fair point about IX_2 being larger but as both indexes are keyed with [field1] first, it's hard to see the seeks made against that one field (only) having to work much harder with IX_2. The server is under substantial CPU load a lot of the time too (should've mentioned that in the post) so removing even the comparatively light write workload imposed by a single column (fully covered) index seems like a good trade.
    – MinnRick
    Mar 26, 2021 at 5:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.