Introduction to the latch in SQL Server

In today's article I would like to talk about the more advanced, lightweight synchronization objects that SQL Server uses: latches (Latch). A latch is a lightweight synchronization object used by the SQL Server storage engine to protect multi-threaded access in-memory structures. Part 1th of the article I'll show you why a latch is needed in SQL Server, and in part 2nd I'll show you the various latch types and how you can troubleshoot them.

Why do we need a latch?

The latch was first introduced in SQL Server 7.0, while Microsoft introduced a row-level lock (Row-level locking) for the first time. It is important that the concept of a row-level lock introduce a latch, otherwise there will be a loss of updates (Lost updates) in memory. As I said, the latch is a lightweight synchronization object used by the storage engine and is used by SQL Server to protect the memory structure. Latches are nothing more than the so-called Critical sections (critcal section) concepts that are similar to multithreaded programming.

In traditional concurrent programming, a critical section is a code that runs only one thread at a time. The latch itself is a special version of a critical section, because it allows for multiple concurrent read operations. In the context of SQL Server This means that multiple threads can concurrently read a shared data structure, such as an in-memory page, but the write-shared structure must be a single thread.

A latch is used to coordinate the physical execution of multiple threads in a database, whereas a lock is based on the selected transaction isolation level, which is used to logically obtain the required isolation level. As a developer or DBA, you can affect locks in different ways-for example, through the isolation level in SQL Server, or through a variety of available lock hints. On the other hand the latch cannot be controlled in a direct manner. There is no latch-up prompt in SQL Server, and there is no latch isolation level available. The following table is a comparison between locks and latches:

Lock (Locks) latch (latches)　　　　　　　　　

• Control...... Transaction thread
• Protection...... Database content in-memory data structures
• Mode...... (shared), hold (Keep),

Updated (update), shared,

(Exclusive), Updated (update), exclusive (Exclusive),

Intentional (Intension) destruction (Destroy)

• Deadlock ... Detect and resolve (detection&resolution) through rigorous code to avoid
• Keep in ... Lock Manager Hash Table (Hashtable) protected data structure (Protected data Structure)

As you can see from the table, the latch supports finer-grained hold (Keep) and Destroy (Destroy) modes. The hold latch is used primarily for reference counting, such as when you want to know how many other latches are waiting on the specified latch. The destruction latch is one of the most restrictive (it even blocks the hold latch) and is used when the latch is destroyed, such as when the lazy writer (lazy writers) wants to free the in-memory page. We introduce the various latch modes and then the compatibility of each latch mode.

NL (empty latch):

• Internal
• Not used

KP (Hold latch):

• Can be held concurrently by multiple tasks
• Only blocked by a dt mode latch

SH (Shared latch):

• Use when reading a data page
• Can be held by multiple task colleagues
• Blocking the latch for ex mode and DT mode

Up (update latch):

• Used when writing system allocation pages and row versioning pages for tempdb
• A latch of this pattern can only be held by a single task

EX (exclusive latch):

• When writing to a data page, use the
• A latch of this pattern can only be held by a single task

DT (Destroy latch):

• Rarely used
• A latch of this pattern can only be held by a single task

In SQL Server, consistency cannot be obtained only with locks. SQL Server can also access shared data structures, such as headers, that are not protected by the lock manager. There are also other components of the SQL Server based on the latch Foundation, and there is a single thread code path. OK, let's go on to the various types of latches in SQL Server and how to troubleshoot them.

latch type and troubleshooting

SQL Server differentiates 3 different latch categories

• IO latch
• Buffer Latch (BUF)
• Non-buffer latch (NON-BUF)

Let's take a look at these 3 different categories. When the page read and write operations on the buffer pool are not completed-that is, when you read from/write to your storage subsystem (2 are not synchronized), SQL Server uses the IO latch (I/O latches). For these I/O latches, SQL Server is prefixed with Pageiolatch_ in the statistics. You can view the wait for these latch types in the DMV sys.dm_os_wait_stats .

1 SELECT *  from WHERE  like ' pageiolatch_% '

With these latches, SQL Server guarantees that those pages are not read into the cache pool more than once, and those pages are not ignored from the cache pool when those pages need to be queried for access.

In addition to these I/O latches, SQL Server supports the so-called buffer latches, which is used to protect pages in the buffer pool from being affected by concurrently running threads. These latches, which SQL Server uses to block lost updates in memory (Lost updates). Without such latches, there will be concurrent read and write pages in the cache pool, which can cause damage to the pages in the main memory. For these cache latches, SQL Server is prefixed with Pagelatch_ in the statistics. You can view the wait for these latch types in the DMV sys.dm_os_wait_stats . The most important thing here is that you are involved in the competition for main memory, because their wait type name does not contain IO words.

1 SELECT *  from WHERE  like ' pagelatch_% '

Finally, SQL Server internally uses so-called non-buffer latches (Non-buffer latches) to protect shared data structures except the buffer pool. For these non-cache latches, SQL Server is prefixed with Latch_ in the statistics. You can view the wait for these latch types in the DMV sys.dm_os_wait_stats .

1 SELECT *  from WHERE  like ' latch_% '

But in this DMV, these waits for non-buffer latches are just an overview of the individual latches used inside SQL Server, and you can find more detailed information in a separate DMV sys.dm_os_latch_stats .

1 SELECT *  from Sys.dm_os_latch_stats

SQL Server 2014 uses 163 latches internally to synchronize access to a shared data structure. One of the well-known latches is fgcb_add_remove, which is used to protect filegroups of filegroups from blocking (file Group control block (FGCB)), during the following specific operations:

• File Growth (manual or automatic)
• Add/Remove file Group files
• recalculate the fill specific gravity ( Recalculating proportional fill weightings)
• During a cyclic allocation, the file is recycled through the filegroup.

When you see this latch in the forefront is, you certainly have too many problems with autogrow operations because of the bad default configuration of your database. When a query attempts to read/write protected data structures and waits for a latch, the query goes into a pending state until the latch can be successfully obtained. So the entire query life cycle through the query consists of running (RUNNING), suspending (SUSPENDED), running (RUNNABLE), and finally running again (RUNNING) . Therefore, forcing shared data structure protection is meaningful when querying for long-time control latches. That's because changing the query state also means a context switch in the Windows system, which is an expensive operation based on the CPU cycles introduced.

It makes no sense to put a latch on a shared data structure that reads or writes frequently or in very short periods of time. In this case, the context switch required will kill the overall performance of SQL Server, it will take too much time to complete the entire query lifecycle ( RUNNING), Hang (SUSPENDED), Can be run (RUNNABLE)). That is the so-called spin Lock (Spinlocks)introduced by SQL Server. Lock Manager is a good example of this data structure: when locking or unlocking data objects (such as records, pages, etc.), only a single thread is required to access it. But when you look at the sys.dm_os_latch_stats , you will notice that there is no latch protection lock manager itself. The hash table used by the lock manager uses a spin lock to protect-theLock_hash spin lock . a spin lock must be obtained before locking and unlocking operations are performed through the lock manager. But today I do not want to explain the spin lock, because I will be in the special article in detail------------------

Summary

In this article, we explored the latch in SQL Server. As you can see, the latch is a lightweight synchronization object used by SQL Server to hold shared data structures in memory. SQL Server distinguishes 3 different latch types--io latches, buffer latches, and non-buffer latches. You also see how to troubleshoot the latch waits using the DMV sys.dm_os_wait_stats and sys.dm_os_latch_stats .

Thanks for your attention!

Introduction to the latch in SQL Server