SQL Server阻塞查詢語句

sql server的阻塞查詢，主要來自sysprocesses。通常我們在處理時需要加入其它相關的檢視或表如dm_exec_connections，dm_exec_sql_text。透過幾個語句的查詢，可以找到阻塞的語句。
查詢阻塞
語句一
select bl.spid blocking_session,bl.blocked blocked_session,st.text blockedtext from (SELECT   spid ,blocked
   FROM (SELECT * FROM sys.sysprocesses WHERE   blocked>0 ) a
   WHERE not exists(SELECT *
                    FROM (SELECT *
                          FROM sys.sysprocesses
                          WHERE   blocked>0 ) b
                    WHERE a.blocked=spid)
   union SELECT spid,blocked
         FROM sys.sysprocesses
         WHERE   blocked>0) bl,(SELECT t.text ,c.session_id
         FROM sys.dm_exec_connections c 
         CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) st
 where bl.blocked = st.session_id

語句二
SELECT a.blocking_session_id, a.wait_duration_ms, a.session_id,b.text
FROM sys.dm_os_waiting_tasks a,
(SELECT t.text ,c.session_id
FROM sys.dm_exec_connections c 
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) b 
WHERE  a.session_id = b.session_id and a.blocking_session_id IS NOT NULL

語句三，包含阻塞與被阻塞的sql指令碼

select bl.spid blocking_session,bl.blocked blocked_session,st.text blockedtext,sb.text blockingtext
from
(SELECT   spid ,blocked
   FROM (SELECT * FROM sys.sysprocesses WHERE   blocked>0 ) a
   WHERE not exists(SELECT *
                    FROM (SELECT *
                          FROM sys.sysprocesses
                          WHERE   blocked>0 ) b
                    WHERE a.blocked=spid)
   union
 SELECT spid,blocked
         FROM sys.sysprocesses
         WHERE   blocked>0) bl,
(SELECT t.text ,c.session_id
         FROM sys.dm_exec_connections c 
         CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) st,
(SELECT t.text ,c.session_id
         FROM sys.dm_exec_connections c 
         CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) sb
 where bl.blocked = st.session_id and bl.spid = sb.session_id

查詢死鎖
select *
   from master..SysProcesses
  where db_Name(dbID) = '資料庫名'
    and spId <> @@SpId
    and dbID <> 0
    and blocked >0;