What is SQL Server Latch and Buffer Latch Contention?

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_EX or 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!

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 sys.dm_os_wait_stats, sys.dm_os_latch_stats, sys.dm_tran_locks and sys.dm_exec_sessions DMVs.

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).

[info]Take note that assigning an EX Super Latch is more expensive than acquiring an EX regular latch due to sending signal to all Sub Latches across. SQL Server automatically demote the Super Latch to regular Latch when a Super Latch is observed to use a pattern of heavy EX access, once the page is discarded from buffer pool.[/info]

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 PAGELATCH_* or PAGEIOLATCH_* wait types.
  • Non-Buffer Latches: Guarantees the consistency of any in-memory structure other than buffer pool data pages.

Conclusion

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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan