A SQL Server synchronization mechanism that protects in-memory data structures (including buffer pool pages) from concurrent access. Latch waits occur when a thread must wait to acquire a latch held by another thread.
- Latches are lightweight, short-duration locks used internally by SQL Server: Unlike locks, they are not visible to users and protect internal data structures rather than user data.
- High latch wait times are a symptom of contention, not a root cause: DBAs use latch wait types to diagnose the underlying issue.
- Common latch contention scenarios include:
PAGELATCH_EXon PFS, GAM, or SGAM pages (TempDB allocation contention);PAGELATCHon the last index page (sequentialIDENTITYinserts); andLATCH_EXon memory structures. - TempDB latch contention is one of the most common SQL Server performance issues: It can be resolved by increasing the number of TempDB data files, generally one file per logical CPU core up to a maximum of eight.
- Sequential IDENTITY insert contention can be mitigated by: Switching to non-sequential keys such as
NEWSEQUENTIALID(), using multiple partitions, or redesigning the insert pattern. - Related terms: Wait Statistics, TempDB, Buffer Pool, In-Memory OLTP, CX Packet.
