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?
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.
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?
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.
