如何检查 SQL Server 中的阻塞查询 [英] How to check blocking queries in SQL Server

问题描述

我有一台仓库服务器，它 24/7 从旧系统获取数据/同步，我注意到我的一些报告/sql 作业性能不确定，而且大部分时间我从 DBA 团队听说我的查询被阻塞 到其他同步过程.

I have one warehouse server which got data/sync from legacy system 24/7, I noticed some of my reports/sql jobs performance is uncertain and most of the time I heard from DBA team that my query is blocking to other sync process.

我从 DBA 团队了解到命令，即 EXEC SP_WHO2，通过它我可以通过查看列 BlkBy 来识别导致阻塞的查询 spid.

From DBA team I came to know command i.e. EXEC SP_WHO2 by which I can identify spid of query which cause blocking by looking into column BlkBy.

请建议我如何避免阻塞和其他方法来检查 SQL Server

Please suggest me how I can avoid blocking and other ways to check blocking in SQL Server

推荐答案

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

Apart from Sp_Who2 you can use following query to identify blocking in you 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 的详细信息.

Also can check detail of particular SPID by using following command.

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

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

这篇关于如何检查 SQL Server 中的阻塞查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！