Customizing Locking for an Index
The Microsoft SQL Server Database Engine uses a dynamic locking strategy that 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 CREATE INDEX and ALTER INDEX statements. In addition, the CREATE TABLE and ALTER TABLE statements can be used to set locking granularity on PRIMARY KEY and UNIQUE constraints. For backwards compatibility, the sp_indexoption system stored procedure can also set the granularity. 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 Database Engine query optimizer almost always chooses the correct locking granularity. 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.
See Also
Other Resources
ALTER INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE TABLE (Transact-SQL)
INDEXPROPERTY (Transact-SQL)
sp_indexoption (Transact-SQL)