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