Latches and Locks

This resource measures the time it takes for latches and locks to perform various operations.

Latches perform the task of thread synchronization. For example, if a thread is reading a page from disk and creating a memory structure to contain it, it creates one or more Latches to prevent corruption of these structures. Once the operation is complete, the Latches are released and other threads are able to access that page and memory structure again. For the most part, latches are transient, taking a few milliseconds to complete.

A Lock prevents different users from overwriting each others changes. Generally speaking, a lock lasts for the duration of the transaction.

The icons of the listed performance counters in the Statistic Name column change color to reflect the listed counter's current state. Clicking any performance counter in the Statistic Name column takes you to the Performance Counter Details page for that counter.

If the value of a listed performance counter approaches or crosses the Warning threshold, a yellow band will appear for the listed counter allowing plotted values that cross the threshold to easily be visualized against the yellow band. If the value of a listed performance counter approaches or crosses the Critical threshold, a red band will appear for the listed counter allowing plotted values that cross the threshold to easily be visualized against the red band. Hovering over any time period within the chart will display detailed information about that time period in the tooltip. Hovering over a counter will give you detailed information about that performance counter.

Counter Expert Knowledge

Latch Waits/sec

This performance counter displays the number of latch requests that could not be granted immediately.

Total Latch Wait Time/Latch Waits

 

This performance counter returns the ratio of Total Latch Wait Time, in milliseconds, for latch requests in the last second to amount of latches, in a one second period that had to wait. Latches are lightweight means of holding a very transient server resource, such as an address in memory.

This ratio should be less than 10. Monitoring the latches to determine user activity and resource usage can help you to identify performance bottlenecks.

Potential Issue: If the returned value is high, it is likely there is an I/O or memory bottleneck.

Resolution: Check your SQL server and verify its memory is being used efficiently. Applications other than SQL may be using a great deal of memory. Try and recover memory by closing unnecessary applications. Installing additional memory may also help.

Lock Requests/sec

 

This performance counter returns the number of requests for a type of lock, per second.

If the returned value is high, this can indicate that the queries are accessing large numbers of rows. If you notice a high Average Wait time, then this could be an indication of blocking.

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one Server Process ID (SPID) holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.


Resolution: High Read queries should be reviewed. The simple way to fix locking is just kill the connection that is causing the lock. Fixing locking, blocking, and deadlocking issues is often a matter of redesign. You should examine either the schema of your database and/or the way the application is designed. One of the most efficient ways to deal with locking is to ensure that the design of your system does not have to deal with excessive locks. The best way to do this is to normalize your tables, using more atomic structures that allow the query to get in and get out faster. Another method is to code with the locks in mind. If the design requires less normalization, you have to evaluate the programs to ensure that they have the right isolation levels.

Lock Waits/sec

 

This performance counter reports the number of times users waited to acquire a lock over the past second.

This counter only gets incremented only when you “wake up” after waiting on the lock.

Potential Issue: Non-zero values indicate that there is at least some level of blocking occurring. If you combine this with the Lock Wait Time counter, you can get some idea of how long the blocking lasted. A zero value for this counter can definitively rule out blocking as a potential cause.

Resolution: High Read queries should be reviewed.

Lock Timeouts/sec

 

This performance counter returns the number of lock requests per second that have timed out, including internal requests for NoWait locks.

This is the number of milliseconds that passes before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means to not wait at all and return a message as soon as a lock is encountered.

Potential Issue: If you see a value above 0 for this counter, your users experience problems as their queries are not completing.

Resolution: You should review your queries to determine which queries are causing this situation.

Lock Wait Time

This performance counter displays the total time spent waiting across all transactions, in milliseconds, to acquire a lock in the last second.

Though this counts how many milliseconds SQL Server is waiting on locks during the last second, this counter actually starts recording at the end of locking event. Peaks most likely represent one large locking event.

Potential Issue: If the returned value is greater than 60 seconds (60,000ms) then there may be extended blocking which could be an issue.

Resolution: Thoroughly analyze the blocking script output. Some applications are written for timing out after 60 seconds. Because SQL Server records a lock at the end of a locking event, remember that an application with large transactions may have inflated lock wait times while still performing as expected. For example, an application that issues multi-million record updates might have very long lock wait times while performing exactly as it was designed.

Average Latch
Wait Time

 

This performance counter reports the average latch wait time, in milliseconds, for latch requests that had to wait.

SQL Server two lightweight mutual exclusion solutions-Latches and Spinlocks-which are less robust than locks but are less resource intensive. The value of this counter should generally correlate to Latch Waits/sec and move up or down with it accordingly.

Potential Issue: If you see this number jump above 300, you may have contention for your server's resources. High values for this counter could potentially block other users.

Resolution: You should examine tasks currently waiting using the following command: sys.dm_os_waiting_tasks DMV.

Average Lock Wait Time

This performance counter reports the average amount of Wait time, in milliseconds, for each lock request that resulted in a wait.

Generally speaking, the lower the value, the better. This value should correlate to the Lock Waits/sec counter and move up or down with it accordingly.

Potential Issue: An average wait time longer than 500ms may indicate excessive blocking.

Resolution: You should determine which queries are generating locks to identify where the blocking is originating.

Total Latch
Wait Time

Total latch wait time (in milliseconds) for latch requests in the last second.

Short term light weight synchronization object. Latches are not held for the duration of a transaction. Typical latching operations during row transfers to memory, controlling modifications to row offset table, and so on.

Potential Issue: If high, check Perfmon Disk and Memory objects for:

  • I/O bottlenecks
  • Memory pressure

Resolution: This can be mitigated by adding more memory or I/O capacity.

Zooming

You can have the chart show a predetermined time period of data by clicking on any one of the three Zoom buttons in the head of the Zoom column. Alternatively, you can have the chart show a specific date range by dragging the mouse over an area of the chart. The time and date displayed at the top of the resource shows the amount of data currently loaded into the chart. This can be changed by clicking Edit from within the resource.

The Value from Last Poll column shows the most recent data available for the listed statistic.