Performance price for data modification

Each nonclustered index needs to be updated, for all locking schemes:

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