SQL Server, Locks Object


The SQLServer:Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types. Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Minimizing locks increases concurrency, which can improve performance. Multiple instances of the Locks object can be monitored at the same time, with each instance representing a lock on a resource type.

This table describes the SQL Server Locks counters.

SQL Server Locks countersDescription
Average Wait Time (ms)Average amount of wait time (in milliseconds) for each lock request that resulted in a wait.
Lock Requests/secNumber of new locks and lock conversions per second requested from the lock manager.
Lock Timeouts (timeout > 0)/secNumber of lock requests per second that timed out, but excluding requests for NOWAIT locks.
Lock Timeouts/secNumber of lock requests per second that timed out, including requests for NOWAIT locks.
Lock Wait Time (ms)Total wait time (in milliseconds) for locks in the last second.
Lock Waits/secNumber of lock requests per second that required the caller to wait.
Number of Deadlocks/secNumber of lock requests per second that resulted in a deadlock.

SQL Server can lock these resources.

_TotalInformation for all locks.
AllocUnitA lock on an allocation unit.
ApplicationA lock on an application-specified resource.
DatabaseA lock on a database, including all objects in the database.
ExtentA lock on a contiguous group of 8 pages.
FileA lock on a database file.
Heap/BTreeHeap or BTree (HOBT). A lock on a heap of data pages, or on the BTree structure of an index.
KeyA lock on a row in an index.
MetadataA lock on a piece of catalog information, also called metadata.
ObjectA lock on table, stored procedure, view, etc, including all data and indexes. The object can be anything that has an entry in sys.all_objects.
PageA lock on an 8-kilobyte (KB) page in a database.
RIDRow ID. A lock on a single row in a heap.

Monitor Resource Usage (System Monitor)