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.