http://support.microsoft.com/kb/271509/zh-cn
如何监视 SQL Server 2000 阻塞
概要
本文是以下 Microsoft 知识库文章的 Microsoft SQL Server 2000 版的更新，它适用于 Microsoft SQL Server...
本文是以下 Microsoft 知识库文章的 Microsoft SQL Server 2000 版的更新，它适用于 Microsoft SQL Server 7.0：
251004 (http://support.microsoft.com/kb/251004/ ) INF：如何监视 SQL Server 7.0 阻塞
本文介绍可用于诊断阻塞和性能问题的存储过程的用法和设计。有关如何了解和解决阻塞问题的说明，请参见以下 Microsoft 知识库文章：
224453 (http://support.microsoft.com/kb/224453/ ) INF：了解和解决 SQL Server 7.0 或 2000 阻塞问题
更多信息
对“sp_blocker_pss80”存储过程的下列描述可捕获此信息： 开始时间（视运行 SQL Server 的计算机而定），以便此阻塞采样在时间上可以与其他...
对“sp_blocker_pss80”存储过程的下列描述可捕获此信息：
- 开始时间（视运行 SQL Server 的计算机而定），以便此阻塞采样在时间上可以与其他性能信息（如 Microsoft Windows NT 性能监视器日志或 SQL 事件探查器日志）一致。
- 有关与 SQL Server 的连接的信息，通过查询“sysprocesses”系统表获取。
- 有关锁定资源的信息，通过查询“syslockinfo”系统表获取。
- 有关资源等待的信息，通过运行 DBCC SQLPERF(WAITSTATS) 获取。
- 用于连接（被其他连接阻塞或者阻塞其他连接）的当前正在运行的 SQL Server 批处理，通过运行 DBCC INPUTBUFFER 语句获取。
- 结束时间，视运行 SQL Server 的计算机而定。
创建该存储过程时使用了下列优化，从而降低了运行此存储过程对性能和阻塞的影响：
- 除非至少有一个连接在等待资源，否则不生成输出。
- 直接查询“master”数据库中的“sysprocesses”和“syslockinfo”系统表，以提高性能并防止此存储过程被阻塞。因此，此存储过程是特定于 Microsoft SQL Server 2000 的。
- 使用光标创建一个小工作表来获取 DBCC INPUTBUFFER 输出，这对在“tempdb”数据库中的使用应没有太大的影响。
- 由于收集信息时阻塞可以更改，因此存在一种快速模式，该模式可将所得到的结果降至“sysprocesses”和“syslockinfo”系统表的相关行，从而提高了性能。
- 如果您试图跟踪非锁定资源等待，则存在一种锁存模式，该模式可导致锁定输出被忽略。
此存储过程从任何查询工具运行都十分有用。但是，Microsoft 建议您按照下列步骤来执行阻塞分析：
- 当使用对计划要监视的 SQL Server 服务器或 SQL Server 实例具有“sysadmin”特权的登录信息进行连接时，从任何查询工具创建存储过程“sp_blocker_pss80”（在本文末尾提供）。
- 创建一个包含以下查询的脚本文件以循环运行该存储过程。请注意，延迟应在 5 秒和 60 秒之间：
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
- 此输出在与 Microsoft Windows NT 性能监视器日志和 SQL 事件探查器日志结合时非常有用，因此建议同时创建这两种日志。有关要捕获哪些事件探查器和性能监视器事件的信息，以及有关如何解释结果的信息，请参见以下 Microsoft 知识库文章：
224453 (http://support.microsoft.com/kb/224453/ ) INF：了解和解决 SQL Server 7.0 或 2000 阻塞问题
- 在运行 SQL Server 的计算机（您要对其进行监视以防止网络问题导致查询工具断开连接）上，在 Windows 命令提示符处从 Isql.exe 或 Osql.exe 查询工具运行在步骤 2 中创建的脚本文件。下面是可用于启动 Osql.exe 的示例命令行，它假定客户端从运行 SQL Server 的计算机运行，并且脚本文件名为 Checkblk.sql。请务必更正 -S 参数，并将“server”替换为 SQL Server 服务器的名称（如果您监视的是命名实例，则替换为“servername/instance”）。此外，还必须更正 -i 参数，并将“checkblk.sql”替换为在步骤 2 中创建的脚本文件的路径和名称。
osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
请注意，由于下列原因，您必须使用其他命令行开关：
- 为了防止输出文件中出现换行（换行可使输出文件更易于阅读）。
- 为了将输出发送到使用 -o 参数指定的文件，而不是发送到屏幕，以便在查询工具出现问题时，在查询工具失败之前仍得到输出。
下面是用于创建“sp_blocker_pss80”存储过程的脚本：
/*
Note: This script is meant to have 2 creations of the same stored procedure and one of them will fail
with either 207 errors or a 2714 error.
*/
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 16SP3
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 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..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..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 16
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 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..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..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
注意：在 SQL Server 2005 中，您可以配合使用 SQL 事件探查器中的“Blocked Process Report”事件类和“sp_configure”命令来配置“阻塞的进程阈值”选项。
回到顶端