SQL Server - How do redundant indexes impact query performance?

Question

question

yaqubonnet asked Nov 30, '11

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-2008 performance indexes optimizer

10 |1200

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

Answer 1 · 2011-11-30T09:45:17Z

Håkan Winther answered Nov 30, '11

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

10 |1200

Attachments: 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 commented · Nov 30, 2011 at 11:27 AM

You'll always have an overhead for the indexes in updates, even if they are used or not.

2 ·

sp_lock commented · Nov 30, 2011 at 11:19 AM

In addition... If indexes are not used as part SCAN/SEEK etc then there is a cost if the "redundant" indexes are updated.

0 ·

yaqubonnet commented · Nov 30, 2011 at 07:57 PM

@Hakan Winther: Thanks. Can you please explain what do you mean by "if statistics are out of date"?

0 ·

Shawn_Melton commented · Nov 30, 2011 at 09:02 PM

@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 ·

Håkan Winther commented · Nov 30, 2011 at 11:19 PM

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 ·

Show more comments

question