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
