如何监视 SQL Server 2000 阻塞

2022-07-15 80酷酷网 80kuku.com

　　server

概要
本文是以下 Microsoft 知识库文章的 Microsoft SQL Server 2000 版的更新，它适用于 Microsoft SQL Server 7.0：
251004 () 如何监视 SQL Server 7.0 阻塞
本文提供可用于诊断阻塞和性能问题的存储过程的用法和设计。有关如何了解和解决阻塞问题的说明，请参阅以下 Microsoft 知识库文章：
224453 () 了解和解决 SQL Server 7.0 或 2000 阻塞问题

更多信息
对 sp_blocker_pss80 存储过程的下列描述可捕获此信息： • 开始时间（视运行 SQL Server 的计算机而定），以便此阻塞采样在时间上可以与其他性能信息（如 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 建议您按照下列步骤来执行阻塞分析： 1. 当使用对计划要监视的 SQL Server 服务器或 SQL Server 实例具有 sysadmin 特权的登录信息进行连接时，从任何查询工具创建存储过程 sp_blocker_pss80（在本文末尾提供）。
2. 创建一个包含以下查询的脚本文件以循环运行该存储过程。请注意，延迟应在 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

3. 该输出在与 Microsoft Windows NT 性能监视器日志和 SQL 事件探查器日志结合时非常有用，因此建议同时创建这两种日志。有关要捕获哪些事件探查器和性能监视器事件的信息，以及有关如何解释结果的信息，请参阅下面的 Microsoft 知识库文章：
224453 () 了解和解决 SQL Server 7.0 或 2000 阻塞问题
4. 在运行 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 sp_blocker_pss80 (latch int = 0, fast int = 1)
as
--version 15SP3
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) = 'PSSDIAG' 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)

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()

            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 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 ''
      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)
      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 proc sp_blocker_pss80 (latch int = 0, fast int = 1)
as
--version 15
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

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

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

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()

            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)

      fetch next from ibuffer into spid, blocked
   end
   deallocate ibuffer

   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

--------------------------------------------------------------------------------

这篇文章中的信息适用于:
• Microsoft SQL Server 2000 Desktop Engine (Windows)
• Microsoft SQL Server 2000 Developer Edition
• Microsoft SQL Server 2000 Enterprise Edition
• Microsoft SQL Server 2000 Enterprise Edition
• Microsoft SQL Server 2000 Personal Edition Service Pack 3
• Microsoft SQL Server 2000 标准版

回到顶端

关键字： kbinfo KB271509

Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声明。所有该等文件及有关图形均"依样"提供，而不带任何性质的保证。Microsoft和/或其各供应商特此声明，对所有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下，在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中，Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、