SQL捕捉blocking信息

场景：

客户抱怨数据库慢，但是回去看的时候，可能已经不慢了，为了查出当时到底是什么原因导致数据慢，制作了下面的存储过程，然后每隔3分钟运行一遍，把blocking信息插入一个数据库中。

主要就是查询sys.processes这个dmv，然后根据sql handle获取sql text，把信息分别保存到2张表中。目前来看，工作比较顺利，找到了数据库中不少造成阻塞的信息，也给开发那边一个交代。

  1 USE [MonitorBlocking]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[checkBlocking]    Script Date: 8/16/2017 3:01:35 PM ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 ALTER PROCEDURE [dbo].[checkBlocking] 
  9 AS
 10 BEGIN
 11 SET NOCOUNT ON;
 12 ---返回有多少行受影响
 13 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 14 ---设置隔离级别，避免产生锁
 15 declare @Duration   int -- in milliseconds, 1000 = 1 sec
 16 declare @now        datetime
 17 declare @Processes  int
 18 select  @Duration = 100  -- in milliseconds, 1000 = 1 sec
 19 select  @Processes = 0
 20 select @now = getdate() 
 21 ---创建临时表
 22 CREATE TABLE #Blocks_rg(
 23       [spid] smallint,
 24       [kpid] smallint,
 25       [blocked] smallint,
 26       [waitType] binary(2),
 27       [waitTime] bigInt,
 28       [lastWaitType] nchar(32),
 29       [waitResource] nchar(256),
 30       [dbID] smallint,
 31       [uid] smallint,
 32       [cpu] int,
 33       [physical_IO] int,
 34       [memusage] int,
 35       [login_Time] datetime,
 36       [last_Batch] datetime,
 37       [open_Tran] smallint,
 38       [status] nchar(30),
 39       [sid] binary(86),
 40       [hostName] nchar(128),
 41       [program_Name] nchar(128),
 42       [hostProcess] nchar(10),
 43       [cmd] nchar(16),
 44       [nt_Domain] nchar(128),
 45       [nt_UserName] nchar(128),
 46       [net_Library] nchar(12),
 47       [loginName] nchar(128),
 48       [context_Info] binary(128),
 49       [sqlHandle] binary(20),
 50       [CapturedTimeStamp] datetime
 51 )     
 52       
 53 将阻塞信息插入临时表
 54 INSERT INTO #Blocks_rg  
 55 SELECT 
 56       [spid],
 57       [kpid],
 58       [blocked],
 59       [waitType],
 60       [waitTime],
 61       [lastWaitType],
 62       [waitResource],
 63       [dbID],
 64       [uid],
 65       [cpu],
 66       [physical_IO],
 67       [memusage],
 68       [login_Time],
 69       [last_Batch],
 70       [open_Tran],
 71       [status],
 72       [sid],
 73       [hostName],
 74       [program_name],
 75       [hostProcess],
 76       [cmd],
 77       [nt_Domain],
 78       [nt_UserName],
 79       [net_Library],
 80       [loginame],
 81       [context_Info],
 82       [sql_Handle],
 83       @now as [Capture_Timestamp]
 84 FROM master..sysprocesses where blocked <> 0 
 85 AND waitTime > @Duration      
 86 ----等待时间大于1s的会被捕捉
 87       
 88 SET @Processes = @@rowcount
 89 ---返回多少行，赋值给Processer
 90  
 91 ---加入捕捉时间
 92 INSERT into #Blocks_rg
 93 SELECT 
 94  
 95       src.[spid],
 96       src.[kpid],
 97       src.[blocked],
 98       src.[waitType],
 99       src.[waitTime],
100       src.[lastWaitType],
101       src.[waitResource],
102       src.[dbID],
103       src.[uid],
104       src.[cpu],
105       src.[physical_IO],
106       src.[memusage],
107       src.[login_Time],
108       src.[last_Batch],
109       src.[open_Tran],
110       src.[status],
111       src.[sid],
112       src.[hostName],
113       src.[program_name],
114       src.[hostProcess],
115       src.[cmd],
116       src.[nt_Domain],
117       src.[nt_UserName],
118       src.[net_Library],
119       src.[loginame],
120       src.[context_Info],
121       src.[sql_Handle]
122       ,@now as [Capture_Timestamp]
123 FROM  master..sysprocesses src inner join #Blocks_rg trgt
124        on trgt.blocked = src.[spid]
125  
126 if @Processes > 0
127 ---根据sql handle 依次查出SQL Text， 然后插入[Blocking_SqlText]表
128 BEGIN
129       INSERT [dbo].[Blocking_sysprocesses] 
130       SELECT * from #Blocks_rg
131       
132 DECLARE @SQL_Handle binary(20), @SPID smallInt;
133 DECLARE cur_handle CURSOR FOR SELECT sqlHandle, spid FROM #Blocks_rg;
134 OPEN cur_Handle
135 FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID
136 WHILE (@@FETCH_STATUS = 0)
137 BEGIN
138  
139 INSERT [dbo].[Blocking_SqlText]
140 SELECT      @SPID, CONVERT(nvarchar(4000), [text]) ,@now as [Capture_Timestamp] from ::fn_get_sql(@SQL_Handle)
141  
142 FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID
143 END
144 CLOSE cur_Handle
145 DEALLOCATE cur_Handle
146  
147 END
148  
149 DROP table #Blocks_rg
150  
151 END