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

This performance counter returns the ratio of Total Latch Wait Time, in milliseconds, for latch requests in the last second to the 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.

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.

A high Average Wait time may indicate blocking, 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: Review high Read queries. A simple way to fix locking is to kill the connection causing the lock. Fixing locking, blocking, and deadlocking issues is often a matter of redesign. Examine either the schema of your database and/or the way the application is designed. One efficient way to deal with locking is to ensure that the design of your system does not involve 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, evaluate the programs to ensure that they have the right isolation levels.

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 may indicate that some level of blocking occurring. Combine this value with the Lock Wait Time counter to estimate how long the blocking lasted. A zero value for this counter eliminates blocking as a potential cause.

Resolution: Review high Read queries.

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.

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.

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.

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: Determine which queries generate locks to identify where the blocking is originating.