SQL Server Locks

The 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 multiple 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.

For Microsoft SQL Server 2008/2012/2014/2016

Default Collection Interval — Every 15 minutes

Table 1-67 SQL Server Locks Metrics

Metric	Description
SQL Server Locks Counter Name (key column)	Performance metric name. See Table 1-68.
SQL Server Locks Instance Name (key column)	Instance for the SQL Server Locks Counter Name.
SQL Server Locks Counter Value	Performance metric value.

The SQL Server Locks Counter Name key column contains several metrics. Table 1-68 provides a list of these metrics and a description for each.

Table 1-68 SQL Server Locks Counter Name Metrics

Metric	Description
Average Wait Time (ms)	Average amount of wait time in milliseconds for each lock request that resulted in a wait.
Average Wait Time Base	Denominator ("base") of a fraction that the performance counter Average Wait Time ratio represents.
Lock Requests/sec	Number of new locks and lock conversions per second requested from the lock manager.
Lock Timeouts/sec	Number of lock requests per second that timed out, including internal requests for NOWAIT locks.
Lock Waits/sec	Number of lock requests per second that could not be satisfied immediately and required the caller to wait.
Lock Wait Time (ms)	Total wait time in milliseconds for locks in the last second.
Number of Deadlocks/sec	Number of lock requests per second that resulted in a deadlock.