I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce overhead during batch.
Some of these does not make sense, due to PK columns being used in the middle of other indexes, etc. However, I was wondering how SQL Server handles multi column indexes (with overlapping column).
NONCLUSTERED INDEX Index1 (A, B, C) NONCLUSTERED INDEX Index2 (A, B)
Would SQL Server be able to use Index1 for the same rows as Index2 and Index2 is not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.
Can Index2 be removed without impacting performance?
As far as I have understood, indexes can also help with sorts in execution plan. Can Index1 be used for these as well?
In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren’t any included column differences between the two).
If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don’t think you would notice any perceptible differences in performance.