Article ID: 251175
Article Last Modified on 10/3/2003
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q251175
SUMMARY
This article documents the use 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, refer to the following article in the Microsoft Knowledge Base:
162361 INF: Understanding and Resolving SQL Server Blocking Problems
'NOTE:' This article discusses a DBCC command (DBCC PSS) that is unsupported, and may cause unexpected behavior. Microsoft cannot guarantee that you can solve problems that result from the incorrect use of this DBCC command. Use this DBCC command at your own risk. This DBCC command may not be available in future versions of SQL Server. For a list of the supported DBCC commands, see the "DBCC" topic in the Transact-SQL Reference section of SQL Server Books Online.
MORE INFORMATION
The stored procedure, sp_blocker_pss65, captures the following information:
- The starting time according to the SQL Server computer 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 Trace Log.
- Information about connections to SQL Server by querying the system table sysprocesses.
- Information about lock resources by running the system stored procedure sp_lock.
- The current running SQL Server batch for connections that are blocked by others or blocking others by running DBCC INPUTBUFFER.
- Additional information about connections that are blocking others that are not themselves blocked by running DBCC PSS.
- The ending time according to the SQL Server computer if the stored procedure runs for more than 1 second.
The stored procedure was created with the following optimizations in order to decrease 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.
- Two system tables, sysprocesses and syslocks, from the master database are queried directly to increase performance and to prevent this stored procedure from becoming blocked. Hence, this stored procedure is specific to Microsoft SQL Server version 6.5.
- No logged operations for data modifications are performed.
- A small worktable is created by the cursor to obtain the DBCC INPUTBUFFER and DBCC PSS output that 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 that increases performance by decreasing the results to relevant rows of the sysprocesses and syslocks system tables.
This stored procedure is useful by running it from any query tool. However, it is suggested that you use the following steps to perform blocking analysis:
- Create the stored procedure sp_blocker_pss65, which is included at the end of this article, from any query tool while connected with the sa login on the SQL Server 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.
DBCC TRACEON(3604) GO WHILE 1=1 BEGIN EXEC sp_blocker_pss65 -- Or for fast mode -- EXEC sp_blocker_pss65 1 WAITFOR DELAY '00:00:15' END GO
- This output is very useful when you combine it with a Microsoft Windows NT Performance Monitor log and a SQL Trace log, so Microsoft suggests that you creating both at the same time.
Run the script file created in step 2 from the Isql.exe query tool from a Windows command prompt on the SQL Server computer you intend to monitor to prevent networking problems from disconnecting the query tool. Following is an sample command line to start Isql.exe, which assumes that the client is run from the SQL Server computer and that the script file is named Checkblk.sql. Be sure to correct the -S parameter, and replace "server" with the name of your SQL Server server as well as to correct the -i parameter, and replace "checkblk.sql" with the path and name to the script file created in step 2.
isql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
Note that the other command line switches are needed 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.
Here is a script to create the sp_blocking_pss65 stored procedure:
use master go if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss65') and sysstat & 0xf = 4) drop procedure dbo.sp_blocker_pss65 GO create proc sp_blocker_pss65 (@fast int = 0) as -- This stored procedure is built to run on Microsoft SQL Server 6.5 ONLY! -- A similar stored procedure has been written for later versions. set nocount on declare @spid varchar(6), @uid varchar(6), @blocked varchar(6) declare @tmpchar varchar(255) declare @n int declare @time datetime select @n = count(*) from master..sysprocesses where blocked!=0 or waittype != 0x0000 if @n > 0 begin select @time = getdate() select @tmpchar='Start time: ' + convert(varchar(26), @time, 113) print @tmpchar print ' ' if (@fast = 1) begin select spid, blocked, waittype, status, suid, hostname, program_name, hostprocess, cmd, cpu, physical_io, memusage, dbid, uid, gid, kpid, login_time=convert(varchar(26),login_time,113), last_batch=convert(varchar(26), last_batch,113), nt_domain, nt_username, net_address, net_library, 'sprocpss65', runtime=convert(varchar(26),@time,113) from master..sysprocesses where blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0) select spid, dbid, id, page, 'type' = case type when 257 then 'Ex_table-blk' when 258 then 'Sh_table-blk' when 259 then 'Ex_intent-blk' when 260 then 'Sh_intent-blk' when 261 then 'Ex_page-blk' when 262 then 'Sh_page-blk' when 263 then 'Update_page-blk' when 264 then 'Ex_extent-blk' when 265 then 'Update_extent-blk' when 266 then 'ShTab_ExIntent-blk' when 267 then 'Next_extent-blk' when 268 then 'Prev_extent-blk' when 269 then 'Insert_page-blk' when 270 then 'Link_page-blk' end, 'slockpss65', runtime=convert(varchar(26),@time,113) from master..syslocks where type > 256 end else begin select spid, blocked, waittype, status, suid, hostname, program_name, hostprocess, cmd, cpu, physical_io, memusage, dbid, uid, gid, kpid, login_time=convert(varchar(26),login_time,113), last_batch=convert(varchar(26),last_batch,113), nt_domain, nt_username, net_address, net_library, 'sprocpss65', runtime=convert(varchar(26),@time,113) from master..sysprocesses print '' print 'SPIDs at the head of blocking chains' select spid from master..sysprocesses where blocked = 0 and spid in (select blocked from master..sysprocesses where spid != 0) print '' --exec sp_lock select spid, dbid, id, page, 'type' = case type when 1 then 'Ex_table' when 2 then 'Sh_table' when 3 then 'Ex_intent' when 4 then 'Sh_intent' when 5 then 'Ex_page' when 6 then 'Sh_page' when 7 then 'Update_page' when 8 then 'Ex_extent' when 9 then 'Update_extent' when 10 then 'ShTab_ExIntent' when 11 then 'Next_extent' when 12 then 'Prev_extent' when 13 then 'Insert_page' when 14 then 'Link_page' when 257 then 'Ex_table-blk' when 258 then 'Sh_table-blk' when 259 then 'Ex_intent-blk' when 260 then 'Sh_intent-blk' when 261 then 'Ex_page-blk' when 262 then 'Sh_page-blk' when 263 then 'Update_page-blk' when 264 then 'Ex_extent-blk' when 265 then 'Update_extent-blk' when 266 then 'ShTab_ExIntent-blk' when 267 then 'Next_extent-blk' when 268 then 'Prev_extent-blk' when 269 then 'Insert_page-blk' when 270 then 'Link_page-blk' end, 'slockpss65', runtime=convert(varchar(26),@time,113) from master..syslocks end dbcc traceon(3604) Print '' Print '' Print '*********************************************************************' Print 'Print out DBCC INPUTBUFFER for all blocked or blocking spids.' Print 'Print out DBCC PSS info only for SPIDs at the head of blocking chains' Print '*********************************************************************' declare ibuffer insensitive cursor for select convert(varchar(6), spid) as spid, convert(varchar(6), uid) as uid, convert(varchar(6),blocked) as blocked from master..sysprocesses where (spid <> @@spid) and (blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0)) open ibuffer fetch next from ibuffer into @spid, @uid, @blocked while (@@fetch_status != -1) begin print '' print '' exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''') exec ('dbcc inputbuffer (' + @spid + ')') print '' if (@blocked = '0') -- can make skip PSS in fast mode if desired by changing if statement to -- make sure to add if to print statement above as well. -- if (@blocked = '0' and @fast = 0) begin exec ('print ''DBCC PSS FOR SPID ' + @spid + '''') exec ('dbcc pss (' + @uid + ', ' + @spid +')') print '' print '' end fetch next from ibuffer into @spid, @uid, @blocked end deallocate ibuffer Print '' Print '' Print '*******************************************************************************' Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.' Print '*******************************************************************************' declare ibuffer insensitive cursor for select distinct convert(varchar(6), dbid) from master..sysprocesses where (spid <> @@spid) and dbid != 0 and (blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0)) open ibuffer fetch next from ibuffer into @spid while (@@fetch_status != -1) begin print '' print '' exec ('print ''DBCC OPENTRAN FOR DBID ' + @spid + '''') exec ('dbcc opentran (' + @spid + ')') print '' if @spid = '2' select @blocked = 'Y' fetch next from ibuffer into @spid end deallocate ibuffer if @blocked != 'Y' begin print '' print '' exec ('print ''DBCC OPENTRAN FOR tempdb database''') exec ('dbcc opentran (tempdb)') end if datediff(millisecond, @time, getdate()) > 1000 begin select @tmpchar='End time: ' + convert(varchar(26), getdate(), 113) print @tmpchar end set nocount off dbcc traceoff(3604) end GO
Additional query words: performance
Keywords: kbinfo KB251175