识别阻塞查询和 SQL Server 阻塞原因的技术

由数据库对象锁定导致的进程阻塞是一个常见问题。锁定通过强制每个执行的 SQL Server 事务通过ACID测试来确保数据库中存储的数据的完整性。它认为每笔交易都必须满足以下要求：

• 原子性——事务要么在执行时完全完成，要么根本不执行
• 一致性——事务必须创建一个有效的新数据状态，或者如果发生任何故障，它必须将所有数据返回到事务执行/启动之前的实际状态
• 隔离性——正在执行且尚未提交的事务必须与所有其他事务隔离
• 持久性– 提交的数据必须以某种方式保存，如果事务在系统故障和重新启动时未完成，则可以将数据保持在正确的状态

作为 ACID 测试的一部分，隔离是通过锁定影响对象来执行的，因此，隔离可以影响一个或多个数据行，直到提交针对该对象执行的 SQL Server 事务为止。这意味着，实际上，通过锁定对象，SQL Server 将阻止任何其他进程对该对象中的数据进行任何更改，直到通过提交更改或回滚更改来删除锁定。

对对象施加锁定将导致对锁定对象施加请求的任何其他进程等待，直到锁定被删除，这将延迟该进程的执行。事务等待锁被移除或停止时的状态就是所谓的块。SQL Server 被设计成以这种方式工作，因此，块本身并不是有问题的。因此，只有当在 SQL Server 上检测到高级别块或发生需要过多时间才能解决的块时，才应将其视为潜在问题

SQL Server 阻塞的一个实际示例是，当事务 #1 尝试更新表 A 中的数据时，当事务 #1 仍在运行且未完成时，事务 #2 尝试在表 A 上放置新锁；如果应删除的行也是事务 #2 将更新的行，则事务 #2 将遇到阻塞。这是因为事务 #1 在事务运行时对表施加了排他锁

我们以下面的脚本为例

Script 1

BEGIN TRAN
UPDATE [dbo].[TableX]
   SET [ZAP_ID] = 5
 WHERE [ZAP_ID] = 9

COMMIT

Script 2a

DELETE FROM [dbo].[TableX]
    WHERE [ZAP_ID] = 9

Script 2b

SELECT COUNT(*) FROM [dbo].[TableX]

Script 2c

TRUNCATE TABLE [dbo].[TableX]

These scripts will be used for creating a SQL Server block

首先，执行脚本1，但只执行黄色突出显示的部分（不执行COMMIT命令）。该脚本将启动一个事务，并将锁定 TableX 中的特定行

现在，执行任何其他脚本，它们将无法完成，因为初始事务将在 TableX 中其他脚本尝试访问的相同行上持有其独占锁。因此，脚本必须等待事务完成（以执行事务的 COMMIT）

这个简单的示例说明了一种情况，即一个正在进行的事务可以阻止一个或多个进程，这些进程必须等待具有排他锁的事务完成或回滚

那么我们如何识别 SQL Server 阻塞的实例呢？在 SQL Server 中，有多种方法可以实现此目的：

• 使用活动监视器
• 使用 SQL Server DMV

使用活动监视器

活动监视器是一个 SQL Server Management Studio 工具，使用户能够查看现有 SQL Server 连接的详细信息，并且可用于显示有关 SQL Server 资源上打开的进程和锁定的信息。

要在 SQL Server Management Studio 中打开活动监视器，请右键单击对象资源管理器中的 SQL Server 名称，然后单击活动监视器

展开进程并找到正在等待的进程。找到进程后，查看“阻塞者”列以查找导致阻塞的进程 ID

现在，当识别出导致阻塞的进程时，它必须位于同一进程页面中

现在，当找到有关导致阻塞的进程的信息时，用户可能会选择终止该进程或允许其运行直至完成。要终止该进程，请右键单击它并选择“终止进程”

使用DMV

动态管理视图 sys.dm_exec_request 用于识别 SQL Server 块。它可用于通过执行以下查询来列出当前活动的块

USE [master]
GO
SELECT  session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolati,
on_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

从结果中可以看到哪个进程被阻塞（session_id）、哪个进程被阻塞（blocking_session_id）、涉及什么等待类型以及该阻塞的等待时间是多少以及涉及的资源

要终止阻塞会话（在本例中为 ID 74），请使用 KILL 命令

KILL 74
GO

但这些本机 SQL Server 方法只能用于检测和识别活动块。在许多情况下，当检测和识别出块时，重要的是要知道这是否构成正常行为以及它是否很少发生或仅发生一次。如果没有这些信息，就无法执行所需的故障排除，因为没有有关区块的历史数据，例如阻塞频率和阻塞程度

 引用

https://solutioncenter.apexsql.com/techniques-to-identify-blocking-queries-and-other-causes-of-sql-server-blocks/