Optimistic index locking can resolve increased contention on some important resources, such as the spinlocks that guard address locks on the root page of an index partition.
Applications where this amount of contention might occur are typically those in which:
Access to a specified index constitutes a significant portion of the transaction profile, and many users are concurrently executing the same workload.
Different transactions, such as ad hoc and standardized queries, use the same index concurrently.
Optimistic index locking does not acquire an address lock on the root page of an index partition during normal data manipulation language (DML) operations. If your updates and insertions can cause modifications to the root page of the accessed index partition, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
Two stored procedures are changed by optimistic index locking:
sp_chgattribute enables or disables optimistic index locking; when enabled, setting an exclusive table lock on the table you specify.
sp_help includes a column that displays optimistic index lock.
For more information, see the Adaptive Server Reference Manual: Procedures.
