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)

Help and Information

Getting SQL Server 2005 Assistance