如何在SQL Server中检查阻塞查询

Question

我有一个仓库服务器，它全天候从旧系统获取数据/同步，我注意到我的一些报告/SQL作业的性能不确定，并且大多数时候我从DBA团队那里听到我的查询正在阻止其他同步过程。

从DBA团队那里我知道了一个命令，即EXEC SP_WHO2，通过它我可以通过查看BlkBy列来识别导致阻塞的查询的spid。

请建议我如何避免阻塞以及检查SQL Server中阻塞的其他方法。

- user6498540

Answer 1

除了 Sp_Who2 外，你还可以使用以下查询来识别 SQL 中的阻塞。

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

也可以使用以下命令查看特定SPID的详细信息。

DBCC INPUTBUFFER(56) — Will give you the Event Info.

KILL 56 -- Will kill the session of this id.

- Shakti Singh Chauhan

---

感谢您的快速响应，这非常有帮助。 - user6498540

此例程的更长描述可在 SQL Authority 的博客文章中查看。请注意，使用此解决方案存在风险和警告。 - yeOldeDataSmythe

很好，但如果我们能用DBCC INPUTBUFFER的信息替换RequestingText/BlockingTest会更好。 - mr R

Answer 2

这篇是一份非常全面的指南。然而，以下是一些基本准则：

• 避免使用SELECT ... INTO #temp模式，相反先创建一个表，然后使用INSERT INTO #Temp SELECT...
• 在可以容忍脏读取的查询中使用WITH (NOLOCK)
• 确保存在适当的索引
• 在WHERE子句中使用sargable谓词
• 与您的DBA讨论潜在启用READ_COMMITTED_SNAPSHOT隔离级别的可能性

- Ben Campbell

Answer 3

最简单的方法是使用Microsoft SQL Server Management Studio（SSMS）中的Activity Monitor查询。要从SSMS访问此查询：首先打开主窗口；然后在“工具”下单击“Activity Monitor”；然后使用“进程/会话”选项卡或在监视器窗口左上角的下拉菜单中专门选择“阻止进程”。这将显示所有当前运行的进程及其关联的会话ID，以及它们可能涉及的任何事务，例如被其他线程阻止的事务。

您还可以使用一些T-SQL脚本来检查正在工作的系统上的锁定行为。其中一个名为SP_WHO2的脚本是这样的，它显示有关SQL服务器实例上运行的所有数据库的活动用户连接和关联进程ID的锁定信息。--Cheers Mike B

- Mike Bennyhoff