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.

I have always wondered what the effect indexes have on table modification in SQL Server. Will modification of a table will be slower with an increased number of indexes applied to that table?

My gut feeling says that this will induce overhead of re-indexing but I could be wrong.

share|improve this question
3  
DUpe of dba.stackexchange.com/questions/3831/… ? –  gbn Oct 3 '11 at 19:10
    
@gbn I don't know...that question assumes that performance is degraded, this one is asking how it's degraded. At least that's my understanding. –  Derek Downey Oct 4 '11 at 13:41
add comment

3 Answers

Every index that you add to a table will slow down insert/update/delete operations as there are now more physical changes which need to be made when the insert/update/delete operations happen.

share|improve this answer
    
Adding to @mrdenny's good answer...if you're worried about performance degradation, capture a sample database workload using Profiler. Replay this workload against a test database with/without indexes and compare performance metrics. This will tell you if the benefits of the index exceed the cost of maintenance. –  beargle Oct 3 '11 at 21:34
add comment

As @mrdenny said, every index will need to be changed with INSERT/UPDATE/DELETE operations.

If you are weary about an index is actually be utilized to its fully capacity, and that it's not just a performance degrade, you should query the sys.dm_db_index_usage_stats.

Here are some important fields that are returned:

  • user_seeks bigint Number of seeks by user queries.

  • user_scans bigint Number of scans by user queries.

  • user_lookups bigint Number of bookmark lookups by user queries.

  • user_updates bigint Number of updates by user queries.

  • last_user_seek datetime Time of last user seek

  • last_user_scan datetime Time of last user scan.

  • last_user_lookup datetime Time of last user lookup.

  • last_user_update datetime Time of last user update.

share|improve this answer
    
+1 for the DMV suggestion. This will work on versions 2005 and above. –  StanleyJohns Oct 4 '11 at 6:49
add comment

Actually the general answer - yes, it will degrade CUD operations performance with the possible benefit of select-queries

Worst case - bulk updates - this involving internal sort-spool operations which VERY painful for server

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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