Brief introduction to latch _mssql in SQL Server

In today's article I want to talk about the more advanced, lightweight synchronization objects that SQL Server uses: Latches (Latch). The latch is a lightweight synchronization object used by the SQL Server storage engine to protect multithreaded access to the internal structure of memory. The 1th part of the article I'll explain why the latch is needed in SQL Server, and in the 2nd section 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 the row-level lock (Row-level locking) for the first time. It is very important for the concept of a row-level lock to introduce a latch, otherwise there will be a loss of update (Lost Updates) in memory. As I said, the latch is a lightweight synchronization object used by the storage engine that SQL Server uses to protect the memory structure. Latches are nothing more than the so-called critical section (critcal) concept that is similar to multithreaded programming.

In traditional concurrent programming, a critical section is code that can run only one thread at a time. The latch itself is a special version of a critical section because it allows 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-share structure must be a single thread.

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

Lock (Locks) latch (latches)

Control...... Transaction threads
Protection...... Data structure in database content memory
Mode...... Shared, persisted (Keep),
Updates (update), sharing (shared),

Platoon it (Exclusive), update (update), platoon it (Exclusive),

Intent (Intension) destruction (Destroy)

Dead lock ... Detect and resolve (detection&resolution) through rigorous code to avoid
Remain in the ... Data structure of the lock manager's hash Table (Hashtable) protection (Protected data Structure)
As you can see from the table, the latches support finer granularity retention (Keep) and destruction (Destroy) modes. Latches are used primarily to reference counts, 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 latch lock) and is used when the latch is destroyed, for example, when the lazy writer (Lazy Writer) wants to free an in-memory page. Let's start with a variety of latch modes, and then talk about the compatibility of each latch mode.

NL (empty latch):

Internal
Not used

KP (latch Lock):

Can be held simultaneously by multiple tasks
Blocked only by a DT-mode latch

SH (Shared latch):

Use when reading a data page
Can be held by multiple task colleagues
Blocking ex-mode and DT-mode latches

Up (update latch):

Used when writing to the System allocation page and the row versioning page of tempdb
A latch of this mode can only be held by a single task

EX (exclusive latch):

Use when writing to a data page
A latch of this mode can only be held by a single task

DT (Destroy latch):

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

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

Latch type and troubleshooting
SQL Server distinguishes 3 different latch categories

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

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

Copy Code code as follows:

SELECT * from Sys.dm_os_wait_stats WHERE wait_type like ' pageiolatch_% '

With these latches, SQL Server guarantees that those pages will not be read into the cache pool multiple times, and that those pages will not be ignored from the cache pool when the pages need to be queried for access.

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

Copy Code code as follows:

SELECT * from Sys.dm_os_wait_stats WHERE wait_type like ' pagelatch_% '

Finally, the so-called non-buffer latch (Non-buffer latches) is used internally by SQL Server to protect shared data structures outside the buffer pool. For these non-cache latches, SQL Server appears as a Latch_ prefix in statistics. You can look at the DMV sys.dm_os_wait_stats for these latch types.

Copy Code code as follows:

SELECT * from Sys.dm_os_wait_stats WHERE wait_type like ' latch_% '

But in this DMV. These waiting for a non-buffer latch are just a profile of each latch used within SQL Server, and you can find more detailed information in a separate DMV sys.dm_os_latch_stats.

Copy Code code as follows:

SELECT * from Sys.dm_os_latch_stats

SQL Server 2014 uses 163 latches internally to synchronize access to shared data structures. One of the famous latches is Fgcb_add_remove, which is used to protect filegroup block blocking (file group control blocks (FGCB)) for filegroups during the following specific operations:

File Growth (manual or automatic)
Add/Remove Filegroup files
Recalculate the fill gravity (recalculating proportional fill weightings)
A file collection through a filegroup during a circular allocation.
when you see this latch at the top of the line, you certainly have too many problems with the automatic growth operation, because your database is a bad default configuration. When a query attempts to read/write protected data structures and waits for a latch, the query enters a pending state until the latch can be successfully fetched. So the entire query lifecycle that the query passes through includes running (RUNNING), suspending (suspended), running (RUNNABLE), and finally running again (RUNNING). Therefore, it makes sense to force shared data structure protection when querying for long control latches. That's because changing the query state also means having context switches in the Windows system, which is an expensive operation based on the CPU cycles introduced.

It does not make sense to put a latch on a shared data structure that is frequently read or written in a very short time. In this case, the required context switch kills the overall performance of SQL Server, and it takes too much time to complete the entire query lifecycle (run (RUNNING), suspend (suspended), 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, and so on) requires only a single thread of access. But when you look at Sys.dm_os_latch_stats, you will find no latch protection lock manager itself. The hash bucket corresponding to the hash table used by the lock manager uses a spin lock to protect the--lock_hash spin lock. A spin lock must be obtained before the lock and unlock operation is performed through the lock manager.

The above is the entire content of this article, I hope you can enjoy.