This section briefly introduces the latches in SQL Server and SQL Server.

This section briefly introduces the latches in SQL Server and SQL Server.

In today's article, I want to talk about more advanced and lightweight synchronization objects used by SQL Server: Latch ). The lock is a lightweight synchronization object used by the SQL Server storage engine to protect the multi-threaded access to the memory structure. In part 2 of this article, I will introduce why the locks are needed in SQL Server. In Part 2, I will introduce you to various types of locks and how you can troubleshoot them.

Why do we need a lock?
For the first time, the locks were introduced in SQL Server 7.0 and Microsoft introduced row-level locking for the first time ). It is very important to introduce locks for Row-level locks. Otherwise, Lost Updates will be Lost in the memory. As I said, latches are lightweight synchronization objects used by the storage engine and are used by SQL Server to protect the memory structure. The latch is just like the so-called Critcal Section in multi-threaded programming.

In traditional concurrent programming, the critical section is code that can run only one thread at the same time. The latch itself is a special version of the 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 pages in the memory, but the write shared data structure must be performed by a single thread.

The locks are used to coordinate the physical execution of multiple threads in the database. However, the locks are based on the selected transaction isolation level to obtain the required isolation level logically. As a developer or DBA, you can use different methods to affect the lock-for example, through the isolation level in SQL Server, or through various available lock prompts. On the other hand, the locks cannot be directly controlled. There are no latches in SQL Server, and there is no available latch isolation level. The following table compares the locks and latches:

Locks (Latches)

Control ...... Transaction Thread
Protection ...... Data Structure in memory of database content
Mode ...... Shared, Keep ),
Update, Shared ),

Exclusive, Update, Exclusive ),

Intention (Intension) Destruction (Destroy)

Deadlock ...... Detection and resolution (resolution) can be avoided through rigorous code
Keep in ...... The Data Structure Protected by the lock manager's hash table (Hashtable)
As you can see from the table, the locks support more fine-grained Keep and Destroy modes. The hold lock is mainly used to reference the count, for example, when you want to know how many other latches are waiting on the specified latch. The most restrictive one is to destroy the latch (it may even block and keep the latch). It is used when the latch is destroyed, for example, when the latch is Lazy Writer) to release pages in memory. First, we will introduce various latch modes, and then introduce the compatibility of various latch modes.

NL (empty locks ):

Internal
Unused

KP (keep the lock ):

Can be held by multiple tasks at the same time
It is blocked only by the locks in one DT mode.

SH (shared locks ):

Used to read data pages
Can be held by Multiple Task colleagues
Block the locks in EX mode and DT Mode

UP (update the lock ):

Used when writing to the system allocation page and the row-based version page of tempdb
A latch in this mode can only be held by a single task.

EX ):

Used when writing data pages
A latch in this mode can only be held by a single task.

DT (delete the locks ):

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

In SQL Server, consistency cannot be obtained only by locking. SQL Server can still access the shared data structure not protected by the lock manager, such as the page header. There are also other SQL Server plug-ins Based on latelock, with single-threaded code paths. Now, we will continue to explain the various types of latches in SQL Server and how to troubleshoot them.

Types and troubleshooting of Latches
SQL Server classifies three different lock categories

I/O locks
Buffer lock (BUF)
Non-BUF)

Let's take a closer look at these three different categories. The SQL Server usesI/O Latches). For these I/O latches, SQL Server uses PAGEIOLATCH _ as the prefix in the statistics. You can view the latch wait types in DMV sys. dm_ OS _wait_stats.

Copy codeThe Code is as follows: SELECT * FROM sys. dm_ OS _wait_stats WHERE wait_type LIKE 'pageiolatch _ %'

With these latches, SQL Server ensures that those pages are not concurrently read into the cache pool multiple times, and those pages are not ignored from the cache pool, when those pages need to be accessed by the query.

In addition to these I/O latches, SQL Server also supports the so-calledBuffer Latches)It is used to protect pages in the buffer pool from being affected by concurrent threads. These latches are used by SQL Server to blockLost Updates). Without such latches, there will be concurrent read/write pages in the cache pool, which will cause page corruption in the main memory. For these cache latches, SQL Server usesPAGELATCH_ Is a prefix. You can view the latch wait types in DMV sys. dm_ OS _wait_stats. The most important thing here is that you are involved in master memory competition because their wait type names do not contain the I/O words.

Copy codeThe Code is as follows: SELECT * FROM sys. dm_ OS _wait_stats WHERE wait_type LIKE 'pagelatch _ %'

Finally, SQL Server uses the so-called Non-Buffer Latches internally to protect the shared data structure except the Buffer pool. For these non-Cache latches, SQL Server uses LATCH _ as the prefix in the statistics. You can view the latch wait types in DMV sys. dm_ OS _wait_stats.

Copy codeThe Code is as follows: SELECT * FROM sys. dm_ OS _wait_stats WHERE wait_type LIKE 'latch _ %'

However, in this DMV, the waiting for non-Cache locks is only an overview of the various locks used in the SQL Server. You can go to the separate DMV sys. dm_ OS _latch_stats to find more detailed information.

Copy codeThe Code is as follows: SELECT * FROM sys. dm_ OS _latch_stats

SQL Server 2014 uses 163 internal latches to synchronize access to the shared data structure. One of the famous latches is FGCB_ADD_REMOVE, which is used to protect the File Group Control blocking (File Group Control Block (FGCB) of the File Group, during the following specific operations:

File growth (manual or automatic)
Add/delete file group files
Recalculate the filling proportion (Recalculating proportional fill weightings)
During the cyclic allocation, the file is recycled through the file group.
When you see that this latch is at the forefront, you must have too many problems with automatic growth operations, because your database has bad default configurations. When a read/write protection data structure is queried and you need to wait for a latch, the query will be suspended until the latch can be obtained successfully. Therefore, the entire query lifecycle includesRUNNING, SUSPENDED, and RUNNABLE)And then run again (RUNNING ). Therefore, it makes sense to force the shared data structure protection when the query holds the latch for a long time. This is because changing the query status also means performing context switching in Windows. The introduced CPU cycle is a very expensive operation.

Therefore, it is meaningless to move the latches on the shared data structure that reads and writes frequently or within a very short period of time. In this case, the context switch will kill the overall performance of SQL Server. It takes too much time to complete the entire query lifecycle.(RUNNING), suspension, RUNNABLE )). This is what SQL Server introduces.Spin locks). The lock manager is a good example of such a Data Structure: when you lock or unlock data objects (such as records, pages, etc.), you only need to access a single thread. But when you view sys. dm_ OS _latch_stats, you will find that there is no locks to protect the lock manager itself. The corresponding hash bucket in the hash table used by the lock manager uses the spin lock to protect the -- LOCK_HASH spin lock. The spin lock must be obtained before the lock and unlock operations are performed through the lock manager.

The above is all the content of this article, and I hope you will like it.