sql 闩锁 原因_如何识别和解决SQL Server中的热闩锁

sql 闩锁 原因

描述 (Description)

In SQL Server, internal latch architecture protects memory during SQL operations. It ensures the consistency of memory structures with read and write operation on pages. Rudimentarily, it has two classes, buffer latches, and non-buffer latches which perform lightweight synchronization in the SQL Engine.

在SQL Server中，内部闩锁体系结构可在SQL操作期间保护内存。 通过页面上的读写操作，可以确保内存结构的一致性。 简单地说，它具有两个类，即缓冲区锁存器和非缓冲区锁存器，它们在SQL Engine中执行轻量级同步。

The latch ensures memory consistency while Locks ensures logical transaction consistency. When multiple users or applications access the same data at the same time, locking prevents them from making simultaneous changes to the data. Locks are managed internally by the Microsoft SQL Server Compact Database Engine. While a user performs DML operations, locks are automatically acquired and released on resources. The latch ensures memory consistency on memory structures including indexes and data pages. Generally, SQL server uses buffer pool and IO latches to deal with synchronizing primitive manner in-memory structures. When there is a multiple thread concurrency load on the server then a latch result from an attempt to acquire an incompatible memory structure and in doing so, a latch contention issue can arise.

闩锁确保内存一致性，而锁确保逻辑事务一致性。 当多个用户或应用程序同时访问同一数据时，锁定会阻止他们同时更改数据。 锁由Microsoft SQL Server Compact数据库引擎在内部进行管理。 用户执行DML操作时，将自动获取锁并在资源上释放锁。 锁存器可确保包括索引和数据页在内的存储器结构上的存储器一致性。 通常，SQL Server使用缓冲池和IO锁存器来处理同步原始方式的内存结构。 当服务器上有多线程并发负载时，由于尝试获取不兼容的内存结构而导致闩锁 ，并且这样做会导致闩锁争用问题。

There are many types of SQL Server latches including buffer, non-buffer, and IO latches. For more clarification about the latch, kindly check out the article All about Latches in SQL Server by Nikola Dimitrijevic. Moreover, I will discuss in detail more about Hot latches, how to identify and resolve them.

SQL Server闩锁的类型很多，包括缓冲区，非缓冲区和IO闩锁。 有关闩锁的更多说明，请查阅Nikola Dimitrijevic撰写的文章“ SQL Server中的所有闩锁”。 此外，我将详细讨论热闩锁 ，以及如何识别和解决它们。

锁存模式和兼容性 (Latch modes and compatibility)

Basically, as we know, latches are acquired in 5 different modes KP (Keep latch), SH (Shared latch), UP (Update latch), EX (Exclusive latch), DT (Destroy latch). I have summarized the latch modes and their compatibility.

众所周知，基本上，锁存器是在5种不同的模式下获取的：KP（保留锁存器），SH（共享锁存器），UP（更新锁存器），EX（排他锁存器），DT（销毁锁存器）。 我总结了闩锁模式及其兼容性。

KP (Keep latch)
Keep latches ensure that the referenced structure cannot be destroyed.

KP（保持闩锁）
保持闩锁可确保引用的结构不会被破坏。

SH (Shared latch)
A Shared latch is required to read a page data structure. A Shared latch (SH) is compatible with an update (UP) or keep (KP) latch, but incompatible with a destroy latch (DT).

SH（共享闩锁）
需要共享锁存器才能读取页面数据结构。 共享锁存器（SH）与更新（UP）或保持（KP）锁存器兼容，但与销毁锁存器（DT）不兼容。

UP (Update latch)
Update latch is compatible with Keep latch and shared latch but no one can allow to write to its reference structure.

UP（更新闩锁）
更新锁存器与Keep锁存器和共享锁存器兼容，但是没有人可以写入其引用结构。

EX (Exclusive latch)
This latch blocks other threads from waiting or reading from a reference area.

EX（专用闩锁）
该锁存器阻止其他线程等待或从参考区域读取。

DT (Destroy latch)
This latch assigned to the content of the referenced structure before destroying the content.

DT （ 销毁闩锁 ）
在销毁内容之前，此锁存器已分配给引用结构的内容。

All of these latch modes are not compatible with each other. For example, when a thread attempts to acquire a possible latch and the mode is not compatible, then it is placed into the queue to wait for resource availability. For more clarification, kindly review latch mode compatibility chart below.

所有这些锁存模式都不相互兼容。 例如，当线程尝试获取可能的闩锁且模式不兼容时，则将其放入队列中以等待资源可用性。 欲了解更多信息，请查看下面的闩锁模式兼容性表。

闩锁等待类型 (Latch wait types)

With a concurrency load, due to latch mode incompatibilities, page contention can arise. We can figure out these content