Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The indexes are affecting the ADD/UPDATE operations however I was wondering whether the redundant indexes will affect the SELECT query performance or not?

How the redundant indexes are handled by the SQL Server in case more than one indexes are eligible to use by the select query?

share|improve this question
add comment

migrated from stackoverflow.com Dec 1 '11 at 4:29

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

If you want to really split hairs, at the very least they affect the compilation time, since the optimizer has more indexes to consider. They also consume time to load and memory to store their metadata. It may seem a trivial amount, but consider the extreme case of an index with 16 columns over 1000 partitions: it requires 16000 metadata objects to read and store in memory, it may add up to quite a bit. And by simply existing, they add storage space which translates into larger/slower full backups, more space needed for ops, slower restores etc.

But, realistically, a redundant index does not affect query performance. I know of some examples when certain indexes may fool the optimizer into generating a bad (slow) plan, but that is a separate topic.

share|improve this answer
    
Can't there be a benefit to using a narrower index as well. As in a scenario where I have a wide covering index, and a narrow index on just one of the key fields - if the table is large enough would it not be quicker to use the narrower index on queries that only need that specific key? –  JNK Dec 1 '11 at 14:17
    
@JNK: I would argue that in your example the two indices are not redundant. –  Remus Rusanu Dec 1 '11 at 16:04
    
Specific example: IX_1 ON (id, lastname, firstname); IX_2 ON (id). IX_1 contains everything in IX_2, but if all you wanted to filter on was id would IX_2 not be quicker? –  JNK Dec 1 '11 at 16:08
    
@JNK: Yes, it will, I don't argue that. I'm saying that IX_1 and IX_2 are not redundant, as in the OP. They are two different indices with different role/purpose. –  Remus Rusanu Dec 1 '11 at 17:11
    
understood, thanks! –  JNK Dec 1 '11 at 17:39
add comment

The only overhead for indexes are on DML statements (INSERT, UPDATE, and DELETE). The reason this causes overhead is because it makes the database engine update the indexes due to the inserted/updated/deleted data.

Because SELECT doesn't modify data, there is no index overhead. Indexes are for the benefits of data retrieval.

As for how SQL Server chooses an index, it analyzes what the best index is given the fields and conditions for the SELECT.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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