How to effectively capture blocking information of SQL Server

There are many methods to capture blocking information. Several types are listed here. And analyze the advantages and disadvantages of each type. So that we can choose. Before enumeration, let's briefly demonstrate blocking.

First, create a test table:

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

Open a query window and execute the following statement. This statement modifies a row of data and waits for 3 minutes before the end of transaction.

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

At this time, if you open another query window and execute the following statements, the following statements will be blocked.

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

 

Method 1,Capture SQL profiler

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

SQL profiler contains a large amount of information. One event is used in errors and warnings-> blocked Process report to obtain blocking. However, because of the large amount of information, we cannot estimate when blocking will be generated. In addition, using profiler in the production environment may affect the performance, therefore, SQL profiler is not the most suitable tool. We will not repeat it here.

 

Method 2,Execute Query

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

If blocking still exists when we check the problem, we can directly run several queries to learn the information about the blocking header.

SELECT * FROM sys.sysprocesses where spid>50

 

The above query only tells us that the header of the blocking header is spid = 53, but does not tell us what the spid = 53 is doing. We can use the following query to obtain the spid = 53.

DBCC INPUTBUFFER(53) 

 

We can combine the preceding two queries and use the following query:

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)

 

In this way, the spid information of the blocking header and the statement in which the spid is executed can be displayed. We can further study this statement to understand why it is slow to execute.

One disadvantage of using this method is that blocking is required to exist. If blocking has disappeared, our method will be useless.

 

Method 3,Execute a blocking script for a long time

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

Because we usually don't know when blocking will be generated, we usually run a blocking script for a long time, so that we will have enough information when it happens next time. The long-term running of blocking script has basically no impact on the performance. Because we capture information every 10 seconds. The disadvantage is that if the problem occurs only once a month, our blocking log information will be large. Therefore, this method can reproduce the problem within a few days.

The running method is as follows:

 

To stop running, press Ctrl + C.

Blocking information exists in the log. Out file.

We can open the log. Out file and find that spid 54 is blocked by spid 53.

 

Then, we can see what spid = 53 is doing:

The following is the script of blocking script. We can save it as 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 

The disadvantage of this method is that log. out will be huge and will occupy a lot of space. If blocking occurs once a month or even longer, then our method is not suitable.

 

Method 4,We use Agent jobTo check Blocking

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

The disadvantage of running a blocking script for a long time is that we query information at intervals, but most of the collected information is useless. As a result, log files are huge. For a production system, full disk space is not a good thing. In addition, some customers have scruples about using command lines to run tsql scripts for a long time, so we made an improvement. This time, we only collect useful information. We do not pay attention to useless information. This greatly reduces the log size.

First, create an observation database, create two tables blocking_sysprocesses and blocking_sqltext, and create a stored procedure and a job. The job calls the stored procedure at intervals. Only those with blocking are recorded in the blocking_sysprocesses and blocking_sqltext tables. If it has nothing to do with blocking, we will not record it. The following is a tsql statement:

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)' 

When blocking occurs for a period of time, we can query the following two tables to obtain the blocking information when the problem occurred:

use MonitorBlockingGO   SELECT * from Blocking_sqlTextSELECT * FROM Blocking_sysprocesses 

 

From: http://blogs.msdn.com/ B /apgcdsd/archive/2011/12/12/sql-server-blocking.aspx