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.

This question already has an answer here:

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

marked as duplicate by Michael Green, Paul White Mar 13 at 0:10

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.

    
@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
    
What do you mean by "table modification"? ALTER TABLE or CRUD without the "R"? –  mustaccio Mar 12 at 23:28

4 Answers 4

In the case of updates, I believe the updated column(s) would need to be involved in the Indexes before the Update would be slowed by indexes. I don't have direct testing on this theory, but it makes sense to me.

share|improve this answer

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

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

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. –  Bryan Eargle Oct 3 '11 at 21:34

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