SQL Server Latches are lightweight synchronization technique used by the storage engine to make sure the in-memory structures such as index, data pages and internal structures like non-leaf pages in a B-Tree are consistent. SQL Server uses buffer latches to protect the buffer pool and I/O latches to protect pages not yet loaded into the buffer pool. To load a data page from disk into memory (Buffer Pool), the specific worker thread must acquire a buffer latch for that page.
The buffer pool has various types of latches for accessing the page, such as Exclusive Latch (
PAGELATCH_EX) and Shared Latch (
PAGELATCH_SH). Whenever the worker thread needs to access a page, which is not loaded into buffer pool yet needs to request Asynchronous I/O to load the page into buffer pool. The
PAGEIOLATCH_SH might be acquired to prevent another worker thread from loading same/another page with an incompatible latch mode if SQL Server requires to wait for the I/O Subsystem respond.
There are other latches that protecting structures out of buffer pool called non-buffer latches which are used to protect access to other internal memory structure such as SQLMGR, Plan Caches and etc.
Latch contention is very common in heavy workload OLTP systems with concurrent users, when multiple worker threads concurrently attempt to acquire incompatible latches to the same in-memory structure, the engine determines when to use them due to deterministic behavior of latches, remember that database schema design can affect this behavior.
Why and How SQL Server Use Latches?
SQL Server pages are with size of 8KB and can store multiple rows, depends on the row size. Buffer latches are held only for the duration of the physical operation on the page to increase concurrency and performance unlike locks that are held only during transaction.
Latches are guaranteeing the consistency of in-memory structures and controlled by SQL Server with very low performance cost. The lock only guaranteeing the consistency of transaction and can be controlled by user as well with high performance cost. You may explore more by using
Latch Modes and Compatibility
Some latches are not compatible with each other and they might cause latch contention, (I will write another blog post regarding Latch Contention and Symptoms). SQL Server enforces latch compatibility by requiring the incompatible latch requests to wait in a queue until outstanding latch requests are completed. A latch can be in 1 of 5 different modes, which relate to level of access and physical operation. The followings are the list of latch modes:
- KP: Keep Latch, makes sure that the associate structure cannot be destroyed by lazywriter process. used when one thread want to look at a buffer structure.
- SH: Shared Latch, requires to read a page structure.
- UP: Update Latch, is compatible with SH and KP, but no other modes and it will not allow an EX latch to write to the associated structure.
- EX: Exclusive Latch, blocks other workers from writing to or reading from the associated structure.
- DT: Destroy Latch, requires before destroying contents of the structure by Lazywriter process.
The following table is the latch modes compatibilities:-
Super Latches or Sub Latches
SQL Server 2005 introduced Super Latches or Sub Latches, which are effective only on hardware systems with 32 or more logical processors. this internal feature improves efficiency of the SQL Server engine for some highly concurrent OLTP workload patters. Page Split operation in B-Tree structure in heavy high concurrency OLTP workload is always increase, which degrade the performance. Super Latches can enable increased performance for accessing shared pages where multiple concurrently running worker threads require SH latches.
SQL Server automatically promote a latch on such a page to a Super Latch. A Super Latch partitions a single latch into an array of Sub Latch structures, and One (1) Sub Latch per partition per CPU core, where the main latch becomes a proxy and global state synchronization is not required for read-only latches. Therefore the worker needs to acquire the Shared Sub Latch from the local scheduler (CPU Core).
How to Find Problematic Latches
Basically SQL Server Wait Statistics or Wait Stats, will expose the information. The following wait types are related to Latches either Buffer Latch or Non-Buffer Latch:-
- Buffer Latches (BUF): Guarantees the consistency of data pages for user and system objects. Buffer Latches are reported as
- Non-Buffer Latches: Guarantees the consistency of any in-memory structure other than buffer pool data pages.
In highly concurrently systems, it is normal to have some active contention on hot data pages protected by latches in SQL Server. It is considered an issue when the contention and wait time associated with acquiring latch for a page is enough to reduce processor’s utilization which cause very low throughput.