哪个会话引起阻塞并且它们在运行什么

最新推荐文章于 2025-12-31 19:19:25 发布

转载最新推荐文章于 2025-12-31 19:19:25 发布 · 63 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/6252164/2119044

文章标签：

#数据库

本文提供了一段SQL脚本，用于查询当前数据库中哪些会话因锁竞争而被阻塞，以及这些会话正在执行的具体SQL语句。通过该查询可以定位数据库中的死锁或长锁情况，帮助DBA或开发者快速解决问题。

--哪个会话引起阻塞并且它们在运行什么

SELECT DTL.[request_session_id] AS [session_id],
DB_NAME(DTL.[resource_database_id]) AS [Database],
DTL.resource_type,
CASE
WHEN DTL.resource_type IN('DATABASE', 'FILE', 'METADATA')
THEN DTL.resource_type
WHEN DTL.resource_type = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id, DTL.[resource_database_id])
WHEN DTL.resource_type IN('KEY', 'PAGE', 'RID')
THEN
(
SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id
)
ELSE 'Unidentified'
END AS [Parent Object],
DTL.request_mode AS [Lock Type],
DTL.request_status AS [Request Status],
DER.[blocking_session_id],
DES.[login_name],
CASE DTL.request_lifetime
WHEN 0
THEN DEST_R.TEXT
ELSE DEST_C.TEXT
END AS [Statement]
FROM sys.dm_tran_locks DTL
LEFT JOIN sys.[dm_exec_requests] DER ON DTL.[request_session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.[session_id]
INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]
OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_C
OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R
WHERE DTL.[resource_database_id] = DB_ID()
AND DTL.[resource_type] NOT IN('DATABASE', 'METADATA')
ORDER BY DTL.[request_session_id];