SQLServer会话阻塞树形展示

/*
 * SQLServer请求阻塞树形展示
   会话请求发生阻塞时，将相关联会话聚合在一起，以树形目录展示session_id之间关系

 * 注意拼出字段要前后类型一致
   SQLServer2012调试通过
 * 秦刚
 * 2017-10-27 15:00 向上搜索/从子节点向顶节点搜索
   2018-06-05 15:10 会话SQL文本哈希以找出相同SQL命令，显示各会话内存消耗
   2018-09-05 09:47 优化树算法
 */


--1.会话阻塞树
;WITH unit_all as 
(
    SELECT 
        session_id ID,blocking_session_id Parent_ID,    --int
        --connection_id ID,parent_connection_id Parent_ID,  --guid
        --CAST(session_id AS VARCHAR(100)) Name,
        command Name,
        cast(right('0000'+isnull(cast(session_id as varchar(36)),'A000'),4) as varchar(36)) TreeCode    --需要使用唯一不重复字段标识（否则树列表顺序会乱）
    FROM sys.dm_exec_requests(nolock) req   
    WHERE 1 = 1
        and req.session_id<>@@SPID      --排除当前会话
        and (
            session_id in (select blocking_session_id from sys.dm_exec_requests where blocking_session_id <> 0 )    
            or blocking_session_id <> 0 
            )
        --and req.sql_handle is not null    --有sql_handle的才进入   --**1.筛选会话范围
)
,unit_tree as (
    SELECT 
        *,
        cast(isnull(space((1-1)*4),'')+name as varchar(512)) TreeName,
        1 TreeLevel,
        '/'+left(cast(right('0000'+isnull(cast(id as varchar),'0000'),4) as varchar(max)),36) as TreePath,
        '/'+left(cast(TreeCode as varchar(max)),36) as TreePath2,
        cast('/'+Name as varchar(256)) TreePathName
    FROM unit_all 
    WHERE 1=1   --入口条件
        AND ID NOT IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)     --从子节点向顶节点搜索（子节点不能出现在顶节点中）
        --and id =205

    union all

    SELECT 
        m1.*,
        cast(isnull(space((m2.TreeLevel)*4),'')+m1.name as varchar(512)) TreeName,
        m2.TreeLevel+1 TreeLevel,
        m2.TreePath+'/'+left(cast(right('0000'+isnull(cast(m1.id as varchar),'0000'),4) as varchar(max)),36) TreePath,
        m2.TreePath+'/'+left(cast(m1.TreeCode as varchar(max)),36) TreePath2,
        cast(m2.TreePathName+'/'+m1.Name as varchar(256)) TreePathName
    FROM unit_all m1,unit_tree m2 
    WHERE m1.ID = m2.Parent_ID              --向上搜索
)
--SELECT * FROM unit_tree

SELECT
    --t.ID,t.Parent_ID,t.Name,t.TreeCode,t.TreeName,t.TreeLevel,t.TreePath,t.TreePath2,t.TreePathName,
    con.session_id,req.blocking_session_id blocking_id,t.TreePath,

    req.transaction_id,
    --con.connect_time,
    ses.login_name,
    con.client_net_address client_ip,con.client_tcp_port client_port,
    ses.host_name client_host,
    con.local_net_address srv_ip,con.local_tcp_port srv_port,   --会话客户端与服务器信息
    ses.program_name,db_name(ses.database_id) database_name,    --会话应用程序及目标数据库
    req.wait_type,req.wait_time,req.last_wait_type,req.wait_resource,
    req.transaction_isolation_level trans_level,req.lock_timeout,   --事务及锁超时信息
    ses.last_request_start_time,
    req.start_time,req.status,req.command,
    req.sql_handle,
    q.text sql_text,
    CHECKSUM(q.text) hash_sql_text,
    mem.grant_time,mem.requested_memory_kb,mem.granted_memory_kb,
    '||' tab, --**3.后面自行补充各种联接表字段
    ses.status,ses.cpu_time,ses.memory_usage*8 memory_usageKB,ses.last_request_start_time,ses.last_request_end_time,ses.logical_reads,ses.row_count
FROM unit_tree t
inner join sys.dm_exec_requests req
    on req.session_id=t.ID  
inner join sys.dm_exec_connections con
    on con.session_id=req.session_id
inner join sys.dm_exec_sessions ses
    on ses.session_id=con.session_id
cross apply sys.dm_exec_sql_text(req.sql_handle) q  --会导致范围集缩小（部分会话没有sql_handle）
left join sys.dm_exec_query_memory_grants mem
    on mem.session_id=t.ID

where 1=1   
    --and ses.login_name='bigscreen'
    --and TreePath<>TreePath2
    --and TreeLevel=1       --顶级阻根塞会话?
    --and login_name='local_user'
    --and CHECKSUM(q.text) = -2079051225
ORDER BY TreePath   --算法