如何有效抓取SQL Server的BLOCKING資訊

BLOCKING的資訊抓取有很多種方法。這裡羅列了幾種。並且對每種分析它的優缺點。以便我們選擇。在枚舉方法之前，我們先簡單示範一下BLOCKING. 

我們首先建立一個測試表:

DROP TABLE [TESTTABLE]GO CREATE TABLE [dbo].[TESTTABLE](      [ID] [int] NULL,      [NAME] [nvarchar](50) NULL)GO INSERT INTO TESTTABLE VALUES (1, 'aaaa')GO 

然後開啟一個查詢時段，執行下面的語句, 該語句修改一行資料，並等待3分鐘，然後在結束transaction

BEGIN TRANSACTIONUPDATE TESTTABLE SET [NAME] = 'bbbb' WHERE [ID] = 1WAITFOR  DELAY '00:03:00'COMMIT TRANSACTION

這時候，如果開啟另外一個查詢時段，執行下面的語句，下面的語句就會被BLOCK住。

UPDATE TESTTABLE SET [NAME] = 'cccc' WHERE [ID] = 1 

 

方法一, 抓取SQL Profiler

======================

SQL Profiler裡麵包含大量的資訊。其中有一個事件在Errors and Warnings->Blocked Process Report專門用來獲得blocking的情況。但是因為資訊量比較大，而且我們並不能很好的估算在什麼時候會產生blocking，另外在生產環境使用Profiler, 對效能可能會有影響，所以SQL Profiler並不是最合適的工具。我們在這裡並不對它贅述。

 

方法二, 執行查詢

================

如果我們檢查問題的時候，blocking還存在，那麼，我們可以直接可以運行幾個查詢，得知BLOCKING HEADER的資訊 

SELECT * FROM sys.sysprocesses where spid>50

 

上述查詢只是告訴我們，BLOCKING HEADER的頭是SPID=53, 但是並沒有告訴我們SPID=53在做什麼事情。我們可以用下面的查詢，得到SPID=53的資訊

DBCC INPUTBUFFER(53) 

 

我們可以把上述的兩個查詢合并起來，用下面的查詢: 

SELECT SPID=p.spid,       DBName = convert(CHAR(20),d.name),       ProgramName = program_name,       LoginName = convert(CHAR(20),l.name),       HostName = convert(CHAR(20),hostname),       Status = p.status,       BlockedBy = p.blocked,       LoginTime = login_time,       QUERY = CAST(TEXT AS VARCHAR(MAX))FROM   MASTER.dbo.sysprocesses p       INNER JOIN MASTER.dbo.sysdatabases d         ON p.dbid = d.dbid       INNER JOIN MASTER.dbo.syslogins l         ON p.sid = l.sid       CROSS APPLY sys.dm_exec_sql_text(sql_handle)WHERE  p.blocked = 0       AND EXISTS (SELECT 1                   FROM   MASTER..sysprocesses p1                   WHERE  p1.blocked = p.spid)

 

這樣，一次執行，就能告訴我們BLOCKING header的SPID資訊，以及該SPID在做的語句。我們可以進一步研究該語句，以理解為什麼該語句執行很慢。 

用這個方法有一個缺點，就是使用的時候，要求BLOCKING是存在的。如果BLOCKING已經消失了，那麼我們的方法就不管用了。 

 

方法三,長期執行一個BLOCKING SCRIPT

==================================

因為我們通常無法知道BLOCKING什麼時候會產生，所以通常的辦法是我們長期運行一個BLOCKING SCRIPT, 這樣，等下次發生的時候，我們就會有足夠的資訊。長期運行BLOCKING SCRIPT對效能基本上是沒有影響的。因為我們每隔10秒鐘抓取一次資訊。缺點是，如果問題一個月才發生一次，那麼，我們的BLOCKING日誌資訊會很大。所以這種方法適用於幾天之內能重現問題。 

運行方法如下: 

 

如果要停止運行，我們按ctrl+c就可以了。

BLOCKING的資訊存在log.out這個檔案中 

我們可以開啟log.out這個檔案, 會發現SPID 54被 SPID 53給Block住了。 

 

而隨後，我們可以看到SPID=53在做什麼事情: 

下面是BLOCKING SCRIPT的指令碼, 我們可以把它存為blocking.sql 

use mastergowhile 1 =1beginprint 'Start time: ' + convert(varchar(26), getdate(), 121)Print 'Running processes'select spid, blocked, waittype, waittime, lastwaittype, waitresource, dbid, uid, cpu,physical_io, memusage, login_time, last_batch,open_tran, status, hostname, program_name, cmd, net_library, loginamefrom sysprocesses--where (kpid <> 0 ) or (spid < 51)-- Change it if you only want to see the working processesprint '*********lockinfor***********'select convert (smallint, req_spid) As spid,rsc_dbid As dbid,rsc_objid As ObjId,rsc_indid As IndId,substring (v.name, 1, 4) As Type,substring (rsc_text, 1, 16) as Resource,substring (u.name, 1, 8) As Mode,substring (x.name, 1, 5) As Statusfrom master.dbo.syslockinfo,master.dbo.spt_values v,master.dbo.spt_values x,master.dbo.spt_values uwhere master.dbo.syslockinfo.rsc_type = v.numberand v.type = 'LR'and master.dbo.syslockinfo.req_status = x.numberand x.type = 'LS'and master.dbo.syslockinfo.req_mode + 1 = u.numberand u.type = 'L'order by spidprint 'inputbuffer for running processes'declare @spid varchar(6)declare ibuffer cursor fast_forward forselect cast (spid as varchar(6)) as spid from sysprocesses where spid >50open ibufferfetch next from ibuffer into @spidwhile (@@fetch_status != -1)beginprint ''print 'DBCC INPUTBUFFER FOR SPID ' + @spidexec ('dbcc inputbuffer (' + @spid + ')')fetch next from ibuffer into @spidenddeallocate ibufferwaitfor delay '0:0:10'End 

這種方法的缺陷就是，log.out會比較巨大，會佔用很大的空間，如果blocking一個月甚至更長時間才發生一次，那我們的這個方法就不太適宜。 

 

方法四,我們用Agent Job來檢查BLOCKING

=====================================

長期運行一個BLOCKING SCRIPT的缺點是我們每隔一段時間，去查詢資訊，但是大多數收集的資訊是無用的。所以會導致記錄檔巨大，對於一個生產系統來講，磁碟空間滿可不是個好事情，另外，有一些客戶對於用命令列來長期運行TSQL指令碼有所顧忌，所以我們做了一個改進。這次，我們只收集有用的資訊。對於無用的資訊我們不關注。這樣能極大減少日誌大小。 

我們首先建立一個觀察資料庫，然後建立兩張表格 Blocking_sysprocesses和Blocking_SQLText, 建立一個預存程序和一個Job,該Job每隔一段時間去調用預存程序。只有發現有blocking的，我們才記錄到表格Blocking_sysprocesses和Blocking_SQLText這兩個表格中。如果跟blocking無關，我們就不對它進行記錄。下面是TSQL語句: 

CREATE DATABASE [MonitorBlocking]GO USE [MonitorBlocking]GO CREATE TABLE Blocking_sysprocesses(      [spid] smallint,      [kpid] smallint,      [blocked] smallint,      [waitType] binary(2),      [waitTime] bigInt,      [lastWaitType] nchar(32),      [waitResource] nchar(256),      [dbID] smallint,      [uid] smallint,      [cpu] int,      [physical_IO] int,      [memusage] int,      [login_Time] datetime,      [last_Batch] datetime,      [open_Tran] smallint,      [status] nchar(30),      [sid] binary(86),      [hostName] nchar(128),      [program_Name] nchar(128),      [hostProcess] nchar(10),      [cmd] nchar(16),      [nt_Domain] nchar(128),      [nt_UserName] nchar(128),      [net_Library] nchar(12),      [loginName] nchar(128),      [context_Info] binary(128),      [sqlHandle] binary(20),      [CapturedTimeStamp] datetime)GOCREATE TABLE [dbo].[Blocking_SqlText](      [spid] [smallint],      [sql_text] [nvarchar](2000),      [Capture_Timestamp] [datetime])GO CREATE PROCEDURE [dbo].[checkBlocking]ASBEGIN SET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @Duration   int -- in milliseconds, 1000 = 1 secdeclare @now        datetimedeclare @Processes  int select  @Duration = 100  -- in milliseconds, 1000 = 1 secselect  @Processes = 0 select  @now = getdate() CREATE TABLE #Blocks_rg(      [spid] smallint,      [kpid] smallint,      [blocked] smallint,      [waitType] binary(2),      [waitTime] bigInt,      [lastWaitType] nchar(32),      [waitResource] nchar(256),      [dbID] smallint,      [uid] smallint,      [cpu] int,      [physical_IO] int,      [memusage] int,      [login_Time] datetime,      [last_Batch] datetime,      [open_Tran] smallint,      [status] nchar(30),      [sid] binary(86),      [hostName] nchar(128),      [program_Name] nchar(128),      [hostProcess] nchar(10),      [cmd] nchar(16),      [nt_Domain] nchar(128),      [nt_UserName] nchar(128),      [net_Library] nchar(12),      [loginName] nchar(128),      [context_Info] binary(128),      [sqlHandle] binary(20),      [CapturedTimeStamp] datetime)         INSERT INTO #Blocks_rg SELECT      [spid],      [kpid],      [blocked],      [waitType],      [waitTime],      [lastWaitType],      [waitResource],      [dbID],      [uid],      [cpu],      [physical_IO],      [memusage],      [login_Time],      [last_Batch],      [open_Tran],      [status],      [sid],      [hostName],      [program_name],      [hostProcess],      [cmd],      [nt_Domain],      [nt_UserName],      [net_Library],      [loginame],      [context_Info],      [sql_Handle],      @now as [Capture_Timestamp]FROM master..sysprocesses where blocked <> 0AND waitTime > @Duration          SET @Processes = @@rowcount INSERT into #Blocks_rgSELECT       src.[spid],      src.[kpid],      src.[blocked],      src.[waitType],      src.[waitTime],      src.[lastWaitType],      src.[waitResource],      src.[dbID],      src.[uid],      src.[cpu],      src.[physical_IO],      src.[memusage],      src.[login_Time],      src.[last_Batch],      src.[open_Tran],      src.[status],      src.[sid],      src.[hostName],      src.[program_name],      src.[hostProcess],      src.[cmd],      src.[nt_Domain],      src.[nt_UserName],      src.[net_Library],      src.[loginame],      src.[context_Info],      src.[sql_Handle]      ,@now as [Capture_Timestamp]FROM  master..sysprocesses src inner join #Blocks_rg trgt       on trgt.blocked = src.[spid] if @Processes > 0BEGININSERT [dbo].[Blocking_sysprocesses]SELECT * from #Blocks_rg     DECLARE @SQL_Handle binary(20), @SPID smallInt;DECLARE cur_handle CURSOR FOR SELECT sqlHandle, spid FROM #Blocks_rg;OPEN cur_HandleFETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPIDWHILE (@@FETCH_STATUS = 0)BEGIN INSERT [dbo].[Blocking_SqlText]SELECT      @SPID, CONVERT(nvarchar(4000), [text]) ,@now as [Capture_Timestamp] from::fn_get_sql(@SQL_Handle) FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPIDENDCLOSE cur_HandleDEALLOCATE cur_Handle END DROP table #Blocks_rg ENDGO USE msdb;GO EXEC dbo.sp_add_job@job_name = N'MonitorBlocking';GO EXEC sp_add_jobstep@job_name = N'MonitorBlocking',@step_name = N'execute blocking script', @subsystem = N'TSQL',@command = N'exec checkBlocking',@database_name=N'MonitorBlocking';GO    EXEC sp_add_jobSchedule      @name = N'ScheduleBlockingCheck',      @job_name = N'MonitorBlocking',      @freq_type = 4, -- daily      @freq_interval = 1,      @freq_subday_type = 4,      @freq_subday_interval = 1 EXEC sp_add_jobserver @job_name = N'MonitorBlocking', @server_name = N'(local)' 

當Blocking發生一段時間後，我們可以查詢下面的兩個表格，以得知當時問題發生時的blocking資訊: 

use MonitorBlockingGO   SELECT * from Blocking_sqlTextSELECT * FROM Blocking_sysprocesses 

 

引自：http://blogs.msdn.com/b/apgcdsd/archive/2011/12/12/sql-server-blocking.aspx