The Microsoft® SQL Server™ dynamic locking strategy automatically chooses the best locking granularity for queries in most cases. In cases in which 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 simply 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 configured using the sp_indexoption system stored procedure.
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 optimizer automatically makes the correct determination. It is recommended that you do not override the choices the optimizer makes. Disallowing a locking level can adversely affect the concurrency for a table or index. For example, specifying only table-level locks on a large table accessed heavily by many users can significantly affect performance. Users must wait for the table-level lock to be released before they can access the table.