SQL Server通过动态视图里查找阻塞的会话

简介

对于阻塞我们可以通过动态视图dm_exec_requests、dm_tran_locks等关联通过构建递归式树形结构来查找出阻塞超过30秒的会话。

阻塞查询

WITH Blocked_Sessions AS (
       --     找到所有阻塞的会话
       SELECT sys.dm_exec_requests.blocking_session_id  AS lead_session_id,
             sys.dm_exec_requests.blocking_session_id  AS blocking_session_id , 0 Cnt
       FROM sys.dm_exec_requests
       WHERE blocking_session_id  <> 0
         AND blocking_session_id  NOT IN (SELECT session_id
                                          FROM sys.dm_exec_requests
                                          WHERE sys.dm_exec_requests.blocking_session_id  <> 0)
       UNION ALL
       -- 遍历所有的会话(构建树型关系)
       SELECT Blocked_Sessions.lead_session_id, sys.dm_exec_requests.session_id, 1 Cnt
       FROM sys.dm_exec_requests
       JOIN Blocked_Sessions
             ON Blocked_Sessions.blocking_session_id  = sys.dm_exec_requests.blocking_session_id 
    ),
       Blocked AS (
             SELECT lead_session_id, SUM(Cnt) AS sessions_blocked
             FROM Blocked_Sessions
             GROUP BY lead_session_id)
SELECT Blocked.*, DATEDIFF(s, Sess.last_request_start_time, getdate()) seconds_blocking,
    ISNULL(Req.status,'sleeping') [status], SqlText.text [sql_text],
    STUFF((SELECT DISTINCT ISNULL(', ' + db.name,'')
            FROM sys.databases db
            JOIN sys.dm_tran_locks lcks
                ON db.database_id = lcks.resource_database_id
            WHERE lcks.request_session_id = Sess.session_id
            ORDER BY ISNULL(', ' + db.name,'')
            FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
        ,1,2,'') AS database_list
        , Conn.client_net_address, Sess.login_name
FROM sys.dm_exec_connections Conn
LEFT OUTER JOIN sys.dm_exec_sessions Sess
       ON Conn.session_id = Sess.session_id
JOIN Blocked
       ON Blocked.lead_session_id = Sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Conn.most_recent_sql_handle) SqlText
LEFT JOIN sys.dm_exec_requests Req
       ON Req.session_id = Sess.session_id
WHERE Blocked.sessions_blocked >= 1
-- 过滤阻塞超过30秒的记录
  AND DATEDIFF(s, Sess.last_request_start_time, getdate()) > 30;