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.

| improve this 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 '15 at 23:28

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.

| 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.

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

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

| improve this answer | | | | |

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.

| improve this answer | | | | |

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