列出SQL Server里的阻塞链
点击(此处)折叠或打开
-
;WITH requests (session_id, start_time, status, blocking_session_id,
-
database_name,
-
command, sql_text)
-
AS (SELECT session_id,
-
start_time,
-
status,
-
blocking_session_id,
-
Db_name(database_id),
-
command,
-
sql_text = Cast(text AS VARCHAR(max))
-
FROM sys.dm_exec_requests WITH (nolock)
-
CROSS apply sys.Dm_exec_sql_text (sql_handle)
-
WHERE status <> \'Background\'),
-
blocking (session_id, start_time, status, blocking_session_id, command,
-
sql_text, rownum, levelrow)
-
AS (SELECT r1.session_id,
-
r1.start_time,
-
r1.status,
-
r1.blocking_session_id,
-
r1.command,
-
r1.sql_text,
-
Row_number()
-
OVER (
-
ORDER BY r1.session_id),
-
0 AS LevelRow
-
FROM requests r1
-
INNER JOIN requests r2
-
ON r1.session_id = r2.blocking_session_id
-
WHERE r1.blocking_session_id = 0
-
UNION ALL
-
SELECT r3.session_id,
-
r3.start_time,
-
r3.status,
-
r3.blocking_session_id,
-
r3.command,
-
r3.sql_text,
-
b.rownum,
-
b.levelrow + 1
-
FROM requests r3
-
INNER JOIN blocking b
-
ON r3.blocking_session_id = b.session_id
-
WHERE r3.blocking_session_id > 0)
-
SELECT *
-
FROM blocking
-
ORDER BY rownum,
- levelrow
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12172/viewspace-1409194/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
没有了~
请登录后发表评论
登录
全部评论
