Will more indexes on a table affect performance? [duplicate]

Question

Possible Duplicate:
How do indices impact query performance?
How to know when/if I have too many indexes?

• Say ‘X’ report using some "where" condition in the statement so we are creating an Index ‘A’ on table ‘T’.
• Say ‘Y’ report using some "where" condition in the statement so we are creating an Index ‘B’ on table ‘T’..
• Say ‘Z’ report using some "where" condition in the statement so we are creating an Index ‘C’ on table ‘T’.
• Say ‘M’ report using some "where" condition in the statement so we are creating an Index ‘D’ on table ‘T’.

Now there are 4 indexes and this table has a huge volume of records.

1. When I insert or update rows in this table, do the indexes affect the performance of those operations?
2. If so, then how do I tune those queries to perform well?

Answer 1

An INSERT will touch the clustered index, or heap, and every nonclustered index. An UPDATE will touch the clustered index, or heap, and every nonclustered index that contains, at either the key or the leaf level, the column being updated. A DELETE will touch the clustered index, or heap, and every nonclustered index. An exception to these statements is when a record is filtered out of the index.

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be. This is why adding indexes for performance is a trade off, and must be balanced properly.

Hope this helps,

Matt

Answer 2

Yes, it absolutely does affect performance for INSERT\UPDATE\DELETE operations. When data has been manipulated, all of the affect indexes need to also reflect the update.

You can consider using a single index to span across the use of multiple queries. In other words, instead of creating 4 separate indexes for 4 separate queries, you should be able to cover multiple queries with a single index using key columns and non-key columns (included columns). This will alleviate some of the overhead during DML.

To get a pretty good idea how your current indexes are being used, reference the sys.dm_db_index_usage_stats DMV:

select *
from sys.dm_db_index_usage_stats

This will give you a good idea exactly how your indexes are being used. You can also leverage the Database Engine Tuning Advisor, but take those recommendations as suggestions not hard facts.

Answer 3

Indexes will degrade insert/delete performance since indexes have to be updated. In case of update it depends on whether you update indexed columns. If not, performance should not be affected.