What is SQL Server Latch and Buffer Latch Contention?

The estimated reading time for this post is 4 minutes

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

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.

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.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz