question

yaqubonnet avatar image
yaqubonnet 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?
sql-server-2008performanceindexesoptimizer
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Håkan Winther avatar image
Håkan Winther answered
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.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
You'll always have an overhead for the indexes in updates, even if they are used or not.
2 Likes 2 ·
sp_lock avatar image sp_lock commented ·
In addition... If indexes are not used as part SCAN/SEEK etc then there is a cost if the "redundant" indexes are updated.
0 Likes 0 ·
yaqubonnet avatar image yaqubonnet commented ·
@Hakan Winther: Thanks. Can you please explain what do you mean by "if statistics are out of date"?
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
@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.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.