What is Latch Wait? (4260764)

What is Latch Wait?

What is Latch Wait? Specifically, what do the following mean in PASS:

LATCH EX
PAGELATCH EX
PAGELATCH SH

Latch wait is time spent waiting for internal locks (latches) to be released.

There should be performance advisories pertaining to latch waits that are generated when latch waits occur (latches, database file configuration both show information on this topic). These advisories explain that incorrect/insufficient file configuations will cause latching in SQL Server. You may consider partitioning data in these cases, and/or adding data files on other drives as well as moving log files to other drives. The issue may also lie in drives that cannot read/write quickly enough to satisfy application requests.

The "EX" and the "SH" just indicates the mode in which the latches are obtained. (EX = exclusive; SH = shared)

Please see the following page for more information on the wait types for SQL Server:
http://msdn.microsoft.com/en-us/library/ms179984.aspx

The following are excerpts from the above link:

LATCH_EX - Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.

PAGELATCH_EX - Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.

PAGELATCH_SH - Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.