For all locking schemes, each nonclustered index needs to be updated for each insertion into, and each deletion from, the table
An update to the table that changes part of an index’s key requires only that index be updated.
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
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.
