场景：
客户抱怨数据库慢，但是回去看的时候，可能已经不慢了，为了查出当时到底是什么原因导致数据慢，制作了下面的存储过程，然后每隔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