Microsoft KB Archive/271509

= How to monitor blocking in SQL Server 2005 and in SQL Server 2000 =

Article ID: 271509

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition

-



This article was previously published under Q271509



SUMMARY
If you are using Microsoft SQL Server 2000, you must apply SQL Server 2000 Services Pack 3a (SP3a) before you use the script that is described in this article.

For more information about how to obtain SQL Server 2000 Service Pack 3a, click the following article number to view the article in the Microsoft Knowledge Base:

290211 How to obtain the latest SQL Server 2000 service pack

This article is an update for SQL Server 2000 or later versions of SQL Server of the following Microsoft Knowledge Base article, which applies to Microsoft SQL Server 7.0:

251004 How to monitor SQL Server 7.0 blocking

This article documents the usage and design of a stored procedure that you can use to diagnose blocking and performance issues. For a description of how to understand and resolve blocking, see the following article in the Microsoft Knowledge Base:

224453 Understanding and resolving SQL Server 7.0 or 2000 blocking problems

In Microsoft SQL Server 2005, you can also use the Blocked Process Report event class in SQL Server Profiler to capture the information about a task that has been blocked for more than a specified amount of time.

For more information about the Blocked Process Report event class, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms191168.aspx



MORE INFORMATION
The following description of the sp_blocker_pss80 stored procedure captures this information. This information also applies to SQL Server 2005.
 * The starting time (according to the computer that is running SQL Server) so that this sampling of blocking can be time-aligned with other performance information, such as a Microsoft Windows NT Performance Monitor log or a SQL Profiler log.
 * Information about connections to SQL Server, by querying the sysprocesses system table.
 * Information about lock resources, by querying the syslockinfo system table.
 * Information about resource waits, by running DBCC SQLPERF(WAITSTATS).
 * The current running SQL Server Batch for connections that are blocked by others or blocking others, by running the DBCC INPUTBUFFER statement.
 * The ending time, according to the computer that is running SQL Server.

The stored procedure was created with the following optimizations to reduce the effect on performance and blocking by running this stored procedure:
 * There is no output generated unless at least one connection is waiting on a resource.
 * The sysprocesses and syslockinfo system tables from the master database are queried directly, to increase performance and to prevent this stored procedure from becoming blocked. Therefore, this stored procedure is specific to Microsoft SQL Server 2000 or later versions of SQL Server.
 * A small worktable is created by the cursor to obtain the DBCC INPUTBUFFER output; this should have no noticeable effect on the usage in the tempdb database.
 * Because the blocking can change while gathering the information, a fast mode exists, which increases performance by decreasing the results to relevant rows of the sysprocesses and syslockinfo system tables.
 * If trying to track non-lock resource waits, a latch mode exists, which causes the lock output to be omitted.

This stored procedure is useful by running it from any query tool. However, Microsoft suggests that you follow these steps to perform blocking analysis:  Create the stored procedure sp_blocker_pss80, which is included at the end of this article, from any query tool while you are connected with a login that has sysadmin privileges on the SQL Server server or the instance of SQL Server you plan to monitor.  Create a script file with the following query to run the stored procedure in a loop. Note that the delay should be between 5 and 60 seconds: WHILE 1=1 BEGIN EXEC master.dbo.sp_blocker_pss80 -- Or for fast mode -- EXEC master.dbo.sp_blocker_pss80 @fast=1 -- Or for latch mode -- EXEC master.dbo.sp_blocker_pss80 @latch=1 WAITFOR DELAY '00:00:15' END GO  This output is very useful when combined with a Microsoft Windows NT Performance Monitor log and a SQL Profiler log, so creating both at the same time is suggested. For information concerning which Profiler and Performance Monitor events to capture, and for information about how to interpret the results, see the following article in the Microsoft Knowledge Base:

224453 Understanding and resolving SQL Server 7.0 or 2000 blocking problems

  Run the script file created in step 2 from either Isql.exe, the Osql.exe query tool, or the Sqlcmd utility in a Windows command prompt on the computer that is running SQL Server for which you intend to monitor to prevent networking problems from disconnecting the query tool. Following is an example command line that you can use to start Osql.exe, which assumes that the client is run from the computer that is running SQL Server, and that the script file name is Checkblk.sql. Make sure to correct the -S parameter, and replace &quot;server&quot; with the name of your SQL Server server (or &quot;servername\instance&quot; if you are monitoring a named instance). Also correct the -i parameter, and replace &quot;checkblk.sql&quot; with the path and name to the script file created in step 2. osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000 Note that you must use the other command line switches for the following reasons:

 To prevent line wrapping in the output files which makes it easier to read. To send the output to a file, specified with the -o parameter, instead of to the screen so that if the query tool has problems, you still have output until the query tool fails.</ul> </li></ol>

Following is the script to create the sp_blocker_pss80 stored procedure: use master GO if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss80') and sysstat & 0xf = 4) drop procedure dbo.sp_blocker_pss80 GO create procedure dbo.sp_blocker_pss80 (@latch int = 0, @fast int = 1, @appname sysname='PSSDIAG') as --version 17SP3 if is_member('sysadmin')=0 begin print 'Must be a member of the sysadmin group in order to run this procedure' return end

set nocount on SET LANGUAGE 'us_english' declare @spid varchar(6) declare @blocked varchar(6) declare @time datetime declare @time2 datetime declare @dbname nvarchar(128) declare @status sql_variant declare @useraccess sql_variant

set @time = getdate declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,  ignore_app tinyint, primary key (blocked, spid, ecid)) insert @probclients select spid, ecid, blocked, waittype, dbid, case when convert(varchar(128),hostname) = @appname then 1 else 0 end from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B) begin set @time2 = getdate print '' print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))

insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients where blocked not in (select spid from @probclients) and blocked != 0

if (@fast = 1) begin print '' print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io, memusage, last_batch=convert(varchar(26), last_batch,121), login_time=convert(varchar(26), login_time,121),net_address, net_library, dbid, ecid, kpid, hostname, hostprocess, loginame, program_name, nt_domain, nt_username, uid, sid, sql_handle, stmt_start, stmt_end from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000 or spid in (select blocked from @probclients where blocked != 0) or spid in (select spid from @probclients where blocked != 0)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate))

print '' print 'SYSPROC FIRST PASS' select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0) begin print 'Blocking via locks at ' + convert(varchar(26), @time, 121) print '' print 'SPIDs at the head of blocking chains' select spid from @probclients where blocked = 0 and spid in (select blocked from @probclients where spid != 0) if @latch = 0 begin print 'SYSLOCKINFO' select @time2 = getdate

select spid = convert (smallint, req_spid), ecid = convert (smallint, req_ecid), rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, Type = case rsc_type when 1 then 'NUL' when 2 then 'DB' when 3 then 'FIL' when 4 then 'IDX' when 5 then 'TAB' when 6 then 'PAG' when 7 then 'KEY' when 8 then 'EXT' when 9 then 'RID' when 10 then 'APP' end, Resource = substring (rsc_text, 1, 16), Mode = case req_mode + 1 when 1 then NULL when 2 then 'Sch-S' when 3 then 'Sch-M' when 4 then 'S'                                       when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS' when 8 then 'IU' when 9 then 'IX' when 10 then 'SIU' when 11 then 'SIX' when 12 then 'UIX' when 13 then 'BU' when 14 then 'RangeS-S' when 15 then 'RangeS-U' when 16 then 'RangeIn-Null' when 17 then 'RangeIn-S' when 18 then 'RangeIn-U' when 19 then 'RangeIn-X' when 20 then 'RangeX-S' when 21 then 'RangeX-U' when 22 then 'RangeX-X'end, Status = case req_status when 1 then 'GRANT' when 2 then 'CNVT' when 3 then 'WAIT' end, req_transactionID As TransID, req_transactionUOW As TransUOW from master.dbo.syslockinfo s,              @probclients p            where p.spid = s.req_spid

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate)) end -- latch not set end else print 'No blocking via locks at ' + convert(varchar(26), @time, 121) print '' end -- fast set

else begin -- Fast not set print '' print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io, memusage, last_batch=convert(varchar(26), last_batch,121), login_time=convert(varchar(26), login_time,121),net_address, net_library, dbid, ecid, kpid, hostname, hostprocess, loginame, program_name, nt_domain, nt_username, uid, sid, sql_handle, stmt_start, stmt_end from master.dbo.sysprocesses

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate))

print '' print 'SYSPROC FIRST PASS' select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0) begin print 'Blocking via locks at ' + convert(varchar(26), @time, 121) print '' print 'SPIDs at the head of blocking chains' select spid from @probclients where blocked = 0 and spid in (select blocked from @probclients where spid != 0) if @latch = 0 begin print 'SYSLOCKINFO' select @time2 = getdate

select spid = convert (smallint, req_spid), ecid = convert (smallint, req_ecid), rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, Type = case rsc_type when 1 then 'NUL' when 2 then 'DB' when 3 then 'FIL' when 4 then 'IDX' when 5 then 'TAB' when 6 then 'PAG' when 7 then 'KEY' when 8 then 'EXT' when 9 then 'RID' when 10 then 'APP' end, Resource = substring (rsc_text, 1, 16), Mode = case req_mode + 1 when 1 then NULL when 2 then 'Sch-S' when 3 then 'Sch-M' when 4 then 'S'                                       when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS' when 8 then 'IU' when 9 then 'IX' when 10 then 'SIU' when 11 then 'SIX' when 12 then 'UIX' when 13 then 'BU' when 14 then 'RangeS-S' when 15 then 'RangeS-U' when 16 then 'RangeIn-Null' when 17 then 'RangeIn-S' when 18 then 'RangeIn-U' when 19 then 'RangeIn-X' when 20 then 'RangeX-S' when 21 then 'RangeX-U' when 22 then 'RangeX-X'end, Status = case req_status when 1 then 'GRANT' when 2 then 'CNVT' when 3 then 'WAIT' end, req_transactionID As TransID, req_transactionUOW As TransUOW from master.dbo.syslockinfo

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate)) end -- latch not set end else print 'No blocking via locks at ' + convert(varchar(26), @time, 121) print '' end -- Fast not set

print 'DBCC SQLPERF(WAITSTATS)' dbcc sqlperf(waitstats)

Print '' Print '*********************************************************************' Print 'Print out DBCC Input buffer for all blocked or blocking spids.' Print '*********************************************************************'

declare ibuffer cursor fast_forward for select distinct cast (spid as varchar(6)) as spid from @probclients where (spid <> @@spid) and ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))     or spid in (select blocked from @probclients where blocked != 0)) open ibuffer fetch next from ibuffer into @spid while (@@fetch_status != -1) begin print '' print 'DBCC INPUTBUFFER FOR SPID ' + @spid exec ('dbcc inputbuffer (' + @spid + ')')

fetch next from ibuffer into @spid end deallocate ibuffer

Print '' Print '*******************************************************************************' Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.' Print '*******************************************************************************' declare ibuffer cursor fast_forward for select distinct cast (dbid as varchar(6)) from @probclients where dbid != 0 open ibuffer fetch next from ibuffer into @spid while (@@fetch_status != -1) begin print '' set @dbname = db_name(@spid) set @status = DATABASEPROPERTYEX(@dbname,'Status') set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess') print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']' if @status = N'ONLINE' and @useraccess != N'SINGLE_USER' dbcc opentran(@dbname) else print 'Skipped: Status=' + convert(nvarchar(128),@status) + ' UserAccess=' + convert(nvarchar(128),@useraccess)

print '' if @spid = '2' select @blocked = 'Y'     fetch next from ibuffer into @spid end deallocate ibuffer if @blocked != 'Y'   begin print '' print 'DBCC OPENTRAN FOR DBID 2 [tempdb]' dbcc opentran ('tempdb') end

print 'End time: ' + convert(varchar(26), getdate, 121) end -- All else print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,getdate)) + ' ' + ISNULL (@@servername,'(null)') GO

create procedure dbo.sp_blocker_pss80 (@latch int = 0, @fast int = 1, @appname sysname='PSSDIAG') as --version 17 if is_member('sysadmin')=0 begin print 'Must be a member of the sysadmin group in order to run this procedure' return end

set nocount on declare @spid varchar(6) declare @blocked varchar(6) declare @time datetime declare @time2 datetime declare @dbname nvarchar(128) declare @status sql_variant declare @useraccess sql_variant

set @time = getdate declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,  ignore_app tinyint, primary key (blocked, spid, ecid)) insert @probclients select spid, ecid, blocked, waittype, dbid, case when convert(varchar(128),hostname) = @appname then 1 else 0 end from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B) begin set @time2 = getdate print '' print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))

insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients where blocked not in (select spid from @probclients) and blocked != 0

if (@fast = 1) begin print '' print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io, memusage,last_batch=convert(varchar(26), last_batch,121), login_time=convert(varchar(26), login_time,121), net_address, net_library, dbid, ecid, kpid, hostname, hostprocess, loginame, program_name, nt_domain, nt_username, uid, sid from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000 or spid in (select blocked from @probclients where blocked != 0) or spid in (select spid from @probclients where waittype != 0x0000)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate))

print '' print 'SYSPROC FIRST PASS' select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0) begin print 'Blocking via locks at ' + convert(varchar(26), @time, 121) print '' print 'SPIDs at the head of blocking chains' select spid from @probclients where blocked = 0 and spid in (select blocked from @probclients where spid != 0) if @latch = 0 begin print 'SYSLOCKINFO' select @time2 = getdate

select spid = convert (smallint, req_spid), ecid = convert (smallint, req_ecid), rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, Type = case rsc_type when 1 then 'NUL' when 2 then 'DB' when 3 then 'FIL' when 4 then 'IDX' when 5 then 'TAB' when 6 then 'PAG' when 7 then 'KEY' when 8 then 'EXT' when 9 then 'RID' when 10 then 'APP' end, Resource = substring (rsc_text, 1, 16), Mode = case req_mode + 1 when 1 then NULL when 2 then 'Sch-S' when 3 then 'Sch-M' when 4 then 'S'                                       when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS' when 8 then 'IU' when 9 then 'IX' when 10 then 'SIU' when 11 then 'SIX' when 12 then 'UIX' when 13 then 'BU' when 14 then 'RangeS-S' when 15 then 'RangeS-U' when 16 then 'RangeIn-Null' when 17 then 'RangeIn-S' when 18 then 'RangeIn-U' when 19 then 'RangeIn-X' when 20 then 'RangeX-S' when 21 then 'RangeX-U' when 22 then 'RangeX-X'end, Status = case req_status when 1 then 'GRANT' when 2 then 'CNVT' when 3 then 'WAIT' end, req_transactionID As TransID, req_transactionUOW As TransUOW from master.dbo.syslockinfo s,              @probclients p            where p.spid = s.req_spid

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate)) end -- latch not set end else print 'No blocking via locks at ' + convert(varchar(26), @time, 121) print '' end -- fast set

else begin -- Fast not set print '' print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io, memusage,last_batch=convert(varchar(26), last_batch,121), login_time=convert(varchar(26), login_time,121), net_address, net_library, dbid, ecid, kpid, hostname, hostprocess, loginame, program_name, nt_domain, nt_username, uid, sid from master.dbo.sysprocesses

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate))

print '' print 'SYSPROC FIRST PASS' select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0) begin print 'Blocking via locks at ' + convert(varchar(26), @time, 121) print '' print 'SPIDs at the head of blocking chains' select spid from @probclients where blocked = 0 and spid in (select blocked from @probclients where spid != 0) if @latch = 0 begin print 'SYSLOCKINFO' select @time2 = getdate

select spid = convert (smallint, req_spid), ecid = convert (smallint, req_ecid), rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, Type = case rsc_type when 1 then 'NUL' when 2 then 'DB' when 3 then 'FIL' when 4 then 'IDX' when 5 then 'TAB' when 6 then 'PAG' when 7 then 'KEY' when 8 then 'EXT' when 9 then 'RID' when 10 then 'APP' end, Resource = substring (rsc_text, 1, 16), Mode = case req_mode + 1 when 1 then NULL when 2 then 'Sch-S' when 3 then 'Sch-M' when 4 then 'S'                                       when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS' when 8 then 'IU' when 9 then 'IX' when 10 then 'SIU' when 11 then 'SIX' when 12 then 'UIX' when 13 then 'BU' when 14 then 'RangeS-S' when 15 then 'RangeS-U' when 16 then 'RangeIn-Null' when 17 then 'RangeIn-S' when 18 then 'RangeIn-U' when 19 then 'RangeIn-X' when 20 then 'RangeX-S' when 21 then 'RangeX-U' when 22 then 'RangeX-X'end, Status = case req_status when 1 then 'GRANT' when 2 then 'CNVT' when 3 then 'WAIT' end, req_transactionID As TransID, req_transactionUOW As TransUOW from master.dbo.syslockinfo

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate)) end -- latch not set end else print 'No blocking via locks at ' + convert(varchar(26), @time, 121) print '' end -- Fast not set

print 'DBCC SQLPERF(WAITSTATS)' dbcc sqlperf(waitstats)

Print '' Print '*********************************************************************' Print 'Print out DBCC Input buffer for all blocked or blocking spids.' Print '*********************************************************************'

declare ibuffer cursor fast_forward for select distinct cast (spid as varchar(6)) as spid from @probclients where (spid <> @@spid) and ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))     or spid in (select blocked from @probclients where blocked != 0)) open ibuffer fetch next from ibuffer into @spid while (@@fetch_status != -1) begin print '' print 'DBCC INPUTBUFFER FOR SPID ' + @spid exec ('dbcc inputbuffer (' + @spid + ')')

fetch next from ibuffer into @spid end deallocate ibuffer

Print '' Print '*******************************************************************************' Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.' Print '*******************************************************************************' declare ibuffer cursor fast_forward for select distinct cast (dbid as varchar(6)) from @probclients where dbid != 0 open ibuffer fetch next from ibuffer into @spid while (@@fetch_status != -1) begin print '' set @dbname = db_name(@spid) set @status = DATABASEPROPERTYEX(@dbname,'Status') set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess') print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']' if @status = N'ONLINE' and @useraccess != N'SINGLE_USER' dbcc opentran(@dbname) else print 'Skipped: Status=' + convert(nvarchar(128),@status) + ' UserAccess=' + convert(nvarchar(128),@useraccess)

print '' if @spid = '2' select @blocked = 'Y'     fetch next from ibuffer into @spid end deallocate ibuffer if @blocked != 'Y'   begin print '' print 'DBCC OPENTRAN FOR DBID 2 [tempdb]' dbcc opentran ('tempdb') end

print 'End time: ' + convert(varchar(26), getdate, 121) end -- All else print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,getdate)) + ' ' + ISNULL (@@servername,'(null)') GO

Keywords: kbhowto kbinfo KB271509

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.