13 January,2013 by Jack Vamvas
SQL Server Books online defines the SQL wait type LATCH_EX as “Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.”
1)The definition mentions “does not include buffer latches or transaction mark latches”. This suggests this wait type is not related to IO or data.
How to reduce LATCH_EX wait type
1) Investigate the DMV sys.dm_os_latch_stats for latch contention source. Most sources are esoteric and there is very little in-depth information available on most latch classes. A DBA with deep knowledge of SQL Server Internals can interpret the results .
2) Investigate for administration procedures which may cause an underlying contention. Latches are internal SQL Server and there is very little impact by the DBA. Latches are short lived and it’s difficult to find the correlation between processes and latch contention
One method of investigating is to monitor the sys.dm_exec_requests DMV while running a DBA procedure you think may cause latch contention. Monitor the wait_type column