创建该存储的过程 sp_blocker_pss70，它是包含在本文末尾，从任何查询工具在您处于连接状态时与具有 SQL Server 服务器或 $ SQL Server 计划要监视的实例上 sysadmin 权限的登录。
使用以下查询在循环中运行该存储的过程创建的脚本文件。请注意，是 5 到 60 秒之间的延迟：
WHILE 1=1
BEGIN
EXEC master.dbo.sp_blocker_pss70
-- Or for fast mode
-- EXEC master.dbo.sp_blocker_pss70 @fast=1
-- Or for latch mode
-- EXEC master.dbo.sp_blocker_pss70 @latch=1
WAITFOR DELAY '00:00:15'
END
GO
此输出是使用 Microsoft Windows NT 性能监视器日志和 SQL 事件探查器日志结合使用时非常有用的因此创建同时更新这两个相同的时间被建议。有关捕获，哪些事件探查器和 $ 性能监视器事件的信息，以及关于如何解释该结果的信息，请参阅 Microsoft 知识库中下面的文章：
use master
GO
if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss70') and sysstat & 0xf = 4)
drop procedure dbo.sp_blocker_pss70
GO
create proc sp_blocker_pss70 (@latch int = 0, @fast int = 1)
as
--version 13
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @time3 datetime
declare @dbname nvarchar(128)
declare @IsDetached tinyint
declare @IsEmergencyMode tinyint
declare @IsInLoad tinyint
declare @IsInRecovery tinyint
declare @IsNotRecovered tinyint
declare @IsOffline tinyint
declare @IsShutDown tinyint
declare @IsSingleUser tinyint
declare @IsSuspect tinyint
set @time = getdate()
select spid, ecid, blocked, waittype, dbid, 'ignore_app' = case
when convert(varchar(128),hostname) = 'PSSDIAG' then 1 else 0 end into #probclients
from master..sysprocesses where blocked!=0 or waittype != 0x0000
create unique clustered index pc on #probclients (blocked, spid, ecid)
set @time3 = getdate()
if exists (select spid from #probclients where ignore_app != 1 or waittype != 0x020B)
begin
set @time2 = getdate()
print ''
print '7 Start time: ' + convert(varchar(26), @time, 121) + ' '
+ convert(varchar(12), datediff(ms,@time,@time2)) + ' ' + convert(varchar(12), datediff(ms,@time,@time3))
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 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'
set @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' 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 'IS'
when 5 then 'SIU'
when 6 then 'IS-S'
when 7 then 'IX'
when 8 then 'SIX'
when 9 then 'S'
when 10 then 'U'
when 11 then 'IIn-Nul'
when 12 then 'IS-X'
when 13 then 'IU'
when 14 then 'IS-U'
when 15 then 'X'
when 16 then 'BU' end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end
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 -- blocking via locks
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
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'
set @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' 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 'IS'
when 5 then 'SIU'
when 6 then 'IS-S'
when 7 then 'IX'
when 8 then 'SIX'
when 9 then 'S'
when 10 then 'U'
when 11 then 'IIn-Nul'
when 12 then 'IS-X'
when 13 then 'IU'
when 14 then 'IS-U'
when 15 then 'X'
when 16 then 'BU' end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end
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)
end -- Fast not set
print ''
print 'DBCC SQLPERF(WAITSTATS)'
dbcc sqlperf(waitstats)
print ''
print ''
Print '*********************************************************************'
Print 'Print out DBCC INPUTBUFFER for all blocked or blocking spids.'
Print '*********************************************************************'
declare ibuffer cursor fast_forward for
select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
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, @blocked
while (@@fetch_status != -1)
begin
print ''
exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
exec ('dbcc inputbuffer (' + @spid + ')')
fetch next from ibuffer into @spid, @blocked
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)
select @IsDetached = DATABASEPROPERTY(@dbname,'IsDetached')
select @IsEmergencyMode = DATABASEPROPERTY(@dbname,'IsEmergencyMode')
select @IsInLoad = DATABASEPROPERTY(@dbname,'IsInLoad')
select @IsInRecovery = DATABASEPROPERTY(@dbname,'IsInRecovery')
select @IsNotRecovered = DATABASEPROPERTY(@dbname,'IsNotRecovered')
select @IsOffline = DATABASEPROPERTY(@dbname,'IsOffline')
select @IsShutDown = DATABASEPROPERTY(@dbname,'IsShutDown')
select @IsSingleUser = DATABASEPROPERTY(@dbname,'IsSingleUser')
select @IsSuspect = DATABASEPROPERTY(@dbname,'IsSuspect')
print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
if @IsDetached + @IsEmergencyMode + @IsInLoad + @IsInRecovery + @IsNotRecovered + @IsOffline
+ @IsShutDown + @IsSingleUser + @IsSuspect = 0
dbcc opentran(@dbname)
else
print 'Skipped: Status=ID=' + str(@IsDetached,1) + ' IEM=' + str(@IsEmergencyMode,1) + ' IIL='
+ str(@IsInLoad,1) + ' IIR=' + str(@IsInRecovery,1) + ' INR' + str(@IsNotRecovered,1) + ' IO='
+ str(@IsOffline,1) + ' ISD=' + str(@IsShutDown,1) + ' ISU=' + str(@IsSingleUser,1) + ' IS='
+ str(@IsSuspect,1)
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 ''
print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
print '7 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
+ convert(varchar(12), datediff(ms,@time,@time3)) + ' ' + ISNULL (@@servername,'(null)')
GO
回到顶端
