sql-server - SQL Server 从阻塞会话链中获取主/父阻塞会话 ID

Question

我正在尝试从阻塞会话链中找到父阻塞会话，我从这里找到了以下内容：

;WITH CTE
AS
(
SELECT session_id, blocking_session_id
FROM SYS.DM_EXEC_REQUESTS
WHERE session_id > 49 and blocking_session_id <> 0
 
UNION ALL
 
SELECT X.blocking_session_id AS session_id, ISNULL(Y.blocking_session_id, 0) AS blocking_session_id
FROM CTE X
OUTER APPLY [fn_get_blocking_session](X.blocking_session_id) Y
WHERE X.blocking_session_id <> 0
)
 
SELECT DISTINCT sdes.session_id, host_name, program_name, sqltext.TEXT, dmv.wait_type, dmv.wait_resource
FROM CTE c
INNER JOIN SYS.dm_exec_sessions sdes ON c.session_id = sdes.session_id
LEFT JOIN sys.dm_exec_requests dmv ON c.session_id = dmv.session_id
OUTER APPLY sys.dm_exec_sql_text(dmv.sql_handle) AS sqltext
WHERE c.blocking_session_id = 0

上面的查询使用了一个名为 fn_get_blocking_session 的函数，但它似乎不是系统函数，因为 SQL Server 无法识别它。也许它是一个自定义功能？或者有人可以为我提供一种类似的方法来从阻塞会话链中获取父阻塞会话 ID？

标签： sql-server

Answer 1

我们将此查询用作视图。它有一些关于阻塞的信息。（可能不需要 NOLOCK-s）

SELECT d.name [db]
  , l.rsc_indid            [index_id]
  , l.req_spid             [spid]
  , p.blocked              [blocked_by]
  , p2.blocked             [blocked_by_by]
  ,CASE l.rsc_type
    WHEN 2 THEN 'Database'
    WHEN 3 THEN 'File'
    WHEN 4 THEN 'Index'
    WHEN 5 THEN 'Table'
    WHEN 6 THEN 'Page'
    WHEN 7 THEN 'Key'
    WHEN 8 THEN 'Extent'
    WHEN 9 THEN 'Row'
    WHEN 10 THEN 'Application' END [LockObjType]
  ,CASE l.req_mode
    WHEN 1 THEN 'Sch-S'
    WHEN 2 THEN 'Sch-M'
    WHEN 3 THEN 'S'
    WHEN 4 THEN 'U'
    WHEN 5 THEN 'X'
    WHEN 6 THEN 'IS'
    WHEN 7 THEN 'IU'
    WHEN 8 THEN 'IX'
    WHEN 9 THEN 'SIU'
    WHEN 10 THEN 'SIX'
    WHEN 11 THEN 'UIX'
    WHEN 12 THEN 'BU'
    WHEN 13 THEN 'RangeS_S'
    WHEN 14 THEN 'RangeS_U'
    WHEN 15 THEN 'RangeI_N'
    WHEN 16 THEN 'RangeI_S'
    WHEN 17 THEN 'RangeI_U'
    WHEN 18 THEN 'RangeI_X'
    WHEN 19 THEN 'RangeX_S'
    WHEN 20 THEN 'RangeX_U'
    WHEN 21 THEN 'RangeX_X' END [lock_type]
  ,CASE l.req_status
     WHEN 1 THEN 'G'
    WHEN 2 THEN 'C'
    WHEN 3 THEN 'W' END [status]
  ,CASE l.req_ownertype
    WHEN 1 THEN 'Transaction'
    WHEN 2 THEN 'Cursor'
    WHEN 3 THEN 'Session'
    WHEN 4 THEN 'ExSession' END [lock_ownertype]
  ,p.loginame
  ,p.hostname
  ,p.program_name
  ,l.req_refcnt
  ,p.cmd
  ,p.waittime
  ,p.sql_handle
  ,p2.sql_handle [sql_handle_blocking]

  ,l.rsc_objid   [objectID]
  ,l.rsc_dbid    [dbid]
FROM sys.syslockinfo l
JOIN sys.sysprocesses p ON l.req_spid=p.spid
LEFT JOIN sys.sysprocesses p2 ON p.blocked = p2.spid
JOIN sys.sysdatabases d ON d.dbid = l.rsc_dbid
LEFT OUTER JOIN sys.sysobjects o ON o.id = l.rsc_objid