Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
Of course it affects it. Do you have a specific question? –  JNK May 14 '12 at 15:15
1  
    
What are the fields in the four indices? MS SQL (what's your RDBMS, BTW?) will cheerfully recommend a zillion slightly different indices, where you may be fine with just one with some INCLUDEd columns. –  Jon of All Trades May 14 '12 at 23:04
add comment

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.

3 Answers

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

share|improve this answer
3  
"...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
    
+1 Correct. Updated answer to reflect this. –  Matt M May 14 '12 at 16:34
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
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
add comment

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