阻塞者及阻塞数量

With T
As
(
Select ROW_NUMBER() over (order by spid) as id, spid,blocked From (select distinct spid,blocked from sys.sysprocesses where spid in
    (Select blocked from sys.sysprocesses where blocked <>0) and blocked=0) S
Union All
Select  id,TB.spid,TB.blocked  From sys.sysprocesses TB Inner Join T on TB.blocked=T.SPID  AND TB.blocked<>TB.spid
),
U
AS
(select distinct a.spid,last_batch,program_name,nt_username,loginame,db_name(a.dbid) as 'dbname',OBJECT_NAME(resource_associated_entity_id,a.dbid) as 'tablename',request_mode,request_type,open_tran,waittype,status,hostname,cmd,b.text as TSQL
from sys.sysprocesses a 
cross apply sys.dm_exec_sql_text(a.sql_handle) b 
inner join sys.dm_tran_locks c on a.spid=c.request_session_id and resource_type='OBJECT' and request_mode not like 'Sch%'
where spid in
    (Select blocked from sys.sysprocesses where blocked <>0) 
    and a.blocked=0
)
select U.spid,blockeds,last_batch,program_name,nt_username,loginame,dbname,tablename,request_mode,request_type,open_tran,waittype,status,hostname,cmd,tsql from U inner join (Select distinct COUNT(id) over (partition by id)-1 as blockeds,C.spid From T A cross apply (select TOP 1 SPID from T B WHERE A.id=B.id AND B.blocked=0)C) BlKS ON U.spid=BLKS.spid;
![](https://cache.yisu.com/upload/information/20200311/41/171157.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)