列出SQL Server里的阻塞链

点击(此处)折叠或打开

1. ;WITH requests (session_id, start_time, status, blocking_session_id,
   
2.       database_name,
   
3.       command, sql_text)
   
4.      AS (SELECT session_id,
   
5.                 start_time,
   
6.                 status,
   
7.                 blocking_session_id,
   
8.                 Db_name(database_id),
   
9.                 command,
   
10.                 sql_text = Cast(text AS VARCHAR(max))
    
11.          FROM sys.dm_exec_requests WITH (nolock)
    
12.                 CROSS apply sys.Dm_exec_sql_text (sql_handle)
    
13.          WHERE status <> \'Background\'),
    
14.      blocking (session_id, start_time, status, blocking_session_id, command,
    
15.      sql_text, rownum, levelrow)
    
16.      AS (SELECT r1.session_id,
    
17.                 r1.start_time,
    
18.                 r1.status,
    
19.                 r1.blocking_session_id,
    
20.                 r1.command,
    
21.                 r1.sql_text,
    
22.                 Row_number()
    
23.                   OVER (
    
24.                     ORDER BY r1.session_id),
    
25.                 0 AS LevelRow
    
26.          FROM requests r1
    
27.                 INNER JOIN requests r2
    
28.                         ON r1.session_id = r2.blocking_session_id
    
29.          WHERE r1.blocking_session_id = 0
    
30.          UNION ALL
    
31.          SELECT r3.session_id,
    
32.                 r3.start_time,
    
33.                 r3.status,
    
34.                 r3.blocking_session_id,
    
35.                 r3.command,
    
36.                 r3.sql_text,
    
37.                 b.rownum,
    
38.                 b.levelrow + 1
    
39.          FROM requests r3
    
40.                 INNER JOIN blocking b
    
41.                         ON r3.blocking_session_id = b.session_id
    
42.          WHERE r3.blocking_session_id > 0)
    
43. SELECT *
    
44. FROM blocking
    
45. ORDER BY rownum,
    
46.           levelrow

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12172/viewspace-1409194/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12172/viewspace-1409194/