Since an exclusive table lock blocks all access by other tasks to the entire table, you should thoroughly understand the user access patterns of your application before enabling optimistic index locking.
The following circumstances require an exclusive table lock:
Adding a new level to the root page
Shrinking the root page
Splitting or shrinking the immediate child of the root page, causing an update on the root page
Do not use optimistic index locking when:
You have small tables with index levels no higher than 3.
You envision possible modifications to the root page of an index
An exclusive table lock is an expensive operation, since
it blocks access to the entire table. Use extreme caution in setting
the optimistic index locking property.
Two stored procedures are changed by optimistic index locking:
sp_chgattribute adds an option that acquires an optimistic index lock on a table.
sp_help adds a column that displays optimistic index lock.
For more information on these stored procedures, see the Reference Manual.
