Each nonclustered index needs to be updated, for all locking schemes:
For each insert into the table
For each delete from the table
An update to the table that changes part of an index’s key requires updating just that index.
For tables that use allpages locking, all indexes need to be updated:
For any update that changes the location of a row by updating a clustered index key so that the row moves to another page
For every row affected by a data page split
For allpages-locked tables, exclusive locks are held on affected index pages for the duration of the transaction, increasing lock contention as well as processing overhead.
Some applications experience unacceptable performance impacts with only three or four indexes on tables that experience heavy data modification. Other applications can perform well with many more tables.
