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?

marked as duplicate by Nick Chammas, JNK, Leigh Riffel, Mark Storey-Smith May 27 '12 at 13:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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

  • 4
    "...will touch the clustered index, or heap, and every nonclustered index." Not necessarily. A filtered index will not always be affected. – Thomas Stringer May 14 '12 at 16:28

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.

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.

  • 2
    Indexes can also speed up a DELETE and UPDATE statements if the WHERE condition can make use of the index. – a_horse_with_no_name May 14 '12 at 15:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.