Customizing Locking for an Index
The Microsoft® SQL Server™ 2000 dynamic locking strategy automatically chooses the best locking granularity for queries in most cases. In cases where access patterns are well understood and consistent, limiting the locking levels available for an index can be beneficial.
For example, a database application uses a lookup table that is refreshed weekly in a batch process. The most efficient locking strategy is to turn off page and row locking and allow all concurrent readers to get a shared (S) lock on the table, reducing overhead. During the weekly batch update, the update process can take an exclusive (X) lock, and then update the entire table.
The granularity of locking used on an index can be set using the sp_indexoption system stored procedure. To display the current locking option for a given index, use the INDEXPROPERTY function. Page-level locks, row-level locks, or a combination of page-level and row-level locks can be disallowed for a given index.
| Disallowed locks | Index accessed by |
| Page level | Row-level and table-level locks |
| Row level | Page-level and table-level locks |
| Page level and row level | Table-level locks |
For example, when a table is known to be a point of contention, it can be beneficial to disallow page-level locks, thereby allowing only row-level locks. Or, if table scans are always used to access an index or table, disallowing page-level and row-level locks can help by allowing only table-level locks.
Important The SQL Server query optimizer automatically makes the correct determination. It is recommended that you do not override the choices the optimizer makes. Disallowing a locking level can affect the concurrency for a table or index adversely. For example, specifying only table-level locks on a large table accessed heavily by many users can affect performance significantly. Users must wait for the table-level lock to be released before accessing the table.