查询阻塞进程 

  1 -- ===========================================
  2 -- 获取阻塞的session_id 及阻塞时间
  3 DECLARE @tb_block TABLE(
  4 top_blocking_session_id smallint,
  5 session_id smallint,
  6 blocking_session_id smallint,
  7 wait_time int,
  8 Level int,
  9 blocking_path varchar(8000),
 10 PRIMARY KEY(
 11 session_id, blocking_session_id)
 12 )
 13 INSERT @tb_block(
 14 session_id,
 15 blocking_session_id,
 16 wait_time)
 17 SELECT
 18 session_id,
 19 blocking_session_id,
 20 wait_time = MAX(wait_time)
 21 FROM sys.dm_exec_requests
 22 WHERE blocking_session_id > 0
 23 GROUP BY session_id, blocking_session_id
 24 
 25 -- ===========================================
 26 -- 处理阻塞的session_id 之间的关系
 27 DECLARE
 28 @Level int
 29 SET @Level = 1
 30 
 31 INSERT @tb_block(
 32 session_id, top_blocking_session_id, blocking_session_id,
 33 Level, blocking_path)
 34 SELECT DISTINCT
 35 blocking_session_id, blocking_session_id, 0,
 36 @Level, RIGHT(100000 + blocking_session_id, 5)
 37 FROM @tb_block A
 38 WHERE NOT EXISTS(
 39 SELECT * FROM @tb_block
 40 WHERE session_id = A.blocking_session_id)
 41 WHILE @@ROWCOUNT > 0
 42 BEGIN
 43 SET @Level = @Level + 1
 44 UPDATE A SET
 45 top_blocking_session_id = B.top_blocking_session_id,
 46 Level = @Level,
 47 blocking_path = B.blocking_path 
 48 + RIGHT(100000 + A.session_id, 5)
 49 FROM @tb_block A, @tb_block B
 50 WHERE A.blocking_session_id = B.session_id
 51 AND B.Level = @Level - 1
 52 END
 53 
 54 -- ===========================================
 55 -- 如果只要显示阻塞时间超过多少毫秒的记录，可以在这里做一个过滤
 56 -- 这里假设阻塞时间必须超过1 秒钟(1000毫秒)
 57 DELETE A 
 58 FROM @tb_block A
 59 WHERE NOT EXISTS(
 60 SELECT * FROM @tb_block
 61 WHERE top_blocking_session_id =A.top_blocking_session_id
 62 AND wait_time >= 1000)
 63 
 64 -- ===========================================
 65 -- 使用DBCC INPUTBUFFER 获取阻塞进程的T-SQL 脚本
 66 DECLARE @tb_block_sql TABLE(
 67 id int IDENTITY,
 68 EventType nvarchar(30),
 69 Parameters int,
 70 EventInfo nvarchar(4000),
 71 session_id smallint)
 72 DECLARE
 73 @session_id smallint
 74 DECLARE tb CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
 75 FOR
 76 SELECT DISTINCT
 77 session_id
 78 FROM @tb_block
 79 OPEN tb
 80 FETCH tb INTO @session_id
 81 WHILE @@FETCH_STATUS = 0
 82 BEGIN
 83 INSERT @tb_block_sql(
 84 EventType, Parameters, EventInfo)
 85 EXEC(N'DBCC INPUTBUFFER(' + @session_id + ') WITH NO_INFOMSGS')
 86 IF @@ROWCOUNT > 0
 87 UPDATE @tb_block_sql SET
 88 session_id = @session_id
 89 WHERE IDENTITYCOL = @@IDENTITY
 90 
 91 FETCH tb INTO @session_id
 92 END
 93 CLOSE tb
 94 DEALLOCATE tb
 95 
 96 -- ===========================================
 97 -- 显示阻塞进程信息
 98 ;WITH
 99 BLK AS(
100 SELECT
101 A.top_blocking_session_id,
102 A.session_id,
103 A.blocking_session_id,
104 A.Level,
105 A.blocking_path,
106 SQL = B.EventInfo
107 FROM @tb_block A
108 LEFT JOIN @tb_block_sql B
109 ON A.session_id = B.session_id
110 )
111 SELECT
112 --    BlockPath = REPLICATE(' ', Level * 2 - 2)
113 --    + '|-- '
114 --    + RTRIM(session_id),
115 BLK.top_blocking_session_id,
116 BLK.session_id,
117 BLK.blocking_session_id,
118 BLK.Level,
119 wait_type = P.waittype,
120 wait_time = P.waittime,
121 last_wait_type = P.lastwaittype,
122 wait_resource = P.waitresource,
123 P.login_time,
124 P.last_batch,
125 P.open_tran,
126 P.status,
127 host_name = P.hostname,
128 P.program_name,
129 P.cmd,
130 login_name = P.loginame,
131 BLK.SQL,
132 current_sql = T.text,
133 current_run_sql = SUBSTRING(T.text,
134 P.stmt_start / 2 + 1,
135 CASE
136 WHEN P.stmt_end = -1 THEN LEN(T.text)
137 ELSE (P.stmt_end - P.stmt_start) / 2+1
138 END)
139 FROM BLK
140 -- 简省代码起见，直接引用sysprocess, 读者可以改为引用前述介绍的“查询进程"的脚本进行替换
141 INNER JOIN master.dbo.sysprocesses P
142 ON BLK.session_id = P.spid
143 OUTER APPLY sys.dm_exec_sql_text(P.sql_handle) T
144 ORDER BY BLK.top_blocking_session_id, BLK.blocking_path

 

posted @ 2016-09-26 16:33  maanshancss  阅读(782)  评论(0)  编辑  收藏  举报