死锁查询语句
1 WITH CTE_SID ( BSID, SID, sql_handle )
2 AS ( SELECT blocking_session_id ,
3 session_id ,
4 sql_handle
5 FROM sys.dm_exec_requests
6 WHERE blocking_session_id <> 0
7 UNION ALL
8 SELECT A.blocking_session_id ,
9 A.session_id ,
10 A.sql_handle
11 FROM sys.dm_exec_requests A
12 JOIN CTE_SID B ON A.SESSION_ID = B.BSID
13 )
14 SELECT C.BSID ,
15 C.SID ,
16 S.login_name ,
17 S.host_name ,
18 S.status ,
19 S.cpu_time ,
20 S.memory_usage ,
21 S.last_request_start_time ,
22 S.last_request_end_time ,
23 S.logical_reads ,
24 S.row_count ,
25 q.text
26 FROM CTE_SID C
27 JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
28 CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
29 ORDER BY sid