Latch Wait

A latch is a short-term synchronization object that ensures data integrity on objects in SQL Server memory. There are two major classes of latches, buffer and non-buffer. Buffer latches serve the buffer pool and are the most common type of latch. Although less common, there are many types of non-buffer latches. You can see a list with the following query:

SELECT *

FROM sys.dm_os_latch_stats;

A latch wait is a delay associated with the latch, and is often caused by the I/O system not keeping up with requests so it is taking a long time to get pages from disk into memory. Buffer latch contention is one common reason for long latch waits.

If you view the number of patch latch waits then you can see where and when there are large number of waits. Accurate information from regular readings will help you understand and address latch wait problems.

Gail Shaw, Microsoft Certified Master: SQL Server 2008, MVP, offers some advice on dealing with latch waits. Large numbers of PAGEIOLATCH “could be because there are non-optimal queries or indexes and hence more reads are requested than actually necessary for the workload. It may also mean there’s latency in the IO subsystem. Solutions here include tuning queries or optimising the IO subsystem.”

Idera’s SQL Diagnostic Manager offers fine-grained data on latch waits, including page and non-page wait types. This kind of detailed data greatly simplifies the process of diagnosing latch wait issues.