asked
SQL Server - How do redundant indexes impact query performance?
The indexes are affecting the Add/Update operations however i was wondering whether the redundant indexes will affect the 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 have redundant indexes, SQL server will pick the index that best fit your need, but if the statistics are out of date, you may get an incorrect plan.
You'll always have an overhead for the indexes in updates, even if they are used or not.
@Hakan Winther: Thanks. Can you please explain what do you mean by "if statistics are out of date"?
@yaqubonnet statistics are used by the query optimizer to best determine the query plan. The more current the stats are the more optimal query plan.
The statistics are uppdated during index rebuild or when 20% of the data in the index have changed. If you have large tables you can't count on automatic statistics update and need to rebuild index regulary and use update stats in between.
