INF: 如何监视 SQL Server 7.0 阻止

注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。

点击这里察看该文章的英文版: 251004
本文已归档。它按“原样”提供,并且不再更新。
概要
本文介绍的可用于诊断阻塞和性能问题的存储过程设计和使用。有关如何理解并解决阻塞的说明,请参阅 Microsoft 知识库中下面的文章:
224453INF: 了解和解决 SQL Server 7.0 阻止问题
有关 SQL Server 2000 中的这些相同的问题,请参阅下面 Microsoft 知识库中相应的文章:
271509INF: 如何监视 SQL Server 2000 阻止
更多信息
这一节中描述了 sp_blocker_pss70 存储过程捕获下列信息:
  • 所需的起始时间 (根据运行 SQL Server 的计算机),以便此采样的阻塞可以是时间对齐,并如 Microsoft Windows NT 性能监视器日志或 SQL 事件探查器日志的其他性能信息。

  • 有关连接 SQL Server,通过查询 sysprocesses 系统表的信息。

  • 有关按查询 syslockinfo 系统表的锁资源的信息。

  • 有关资源等待由运行 DBCC 信息 SQLPERF(WAITSTATS)。

  • 当前被其他人的连接运行 SQL 批处理或通过运行 DBCC INPUTBUFFER 阻止其他人。

  • 将结束时间根据到计算机的正在运行 SQL Server。
创建存储的过程时使用下列优化以减少对性能和 $ 通过运行此存储的过程来阻止影响:
  • 生成除非至少一个连接正在等待一个资源上没有输出。

  • 若要提高性能,并防止此存储的过程变得阻止直接,查询 sysprocessessyslockinfo 系统表,从 master 数据库。此存储的过程所以特定于 Microsoft SQL Server 7.0。

  • 创建一个小的工作表以获取游标 DBCC INPUTBUFFER 输出 ; 这将 tempdb 数据库中有使用上没有明显的影响。

  • 因为时收集的信息,可以更改阻止,存在的快速模式,这提高了性能,通过减少结果以 sysprocessessyslockinfo 系统表的相关行。

  • 如果尝试跟踪非锁资源等待,闩锁模式存在,这会导致锁输出到被忽略。
此存储的过程可通过从任何查询工具运行它。但是,Microsoft 建议您按照这些步骤执行阻塞分析:
  1. 创建该存储的过程 sp_blocker_pss70,它是包含在本文末尾,从任何查询工具在您处于连接状态时与具有 SQL Server 服务器或 $ SQL Server 计划要监视的实例上 sysadmin 权限的登录。
  2. 使用以下查询在循环中运行该存储的过程创建的脚本文件。请注意,是 5 到 60 秒之间的延迟:
    WHILE 1=1BEGIN   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'ENDGO					
  3. 此输出是使用 Microsoft Windows NT 性能监视器日志和 SQL 事件探查器日志结合使用时非常有用的因此创建同时更新这两个相同的时间被建议。有关捕获,哪些事件探查器和 $ 性能监视器事件的信息,以及关于如何解释该结果的信息,请参阅 Microsoft 知识库中下面的文章:
    224453INF: 了解和解决 SQL Server 7.0 阻止问题
  4. 运行在第 2 步中创建从任一 Isql.exe 或 Osql.exe 查询工具从 Windows 命令提示符处运行 SQL Server 您要阻止的网络问题断开连接查询工具监视的计算机上的脚本文件。下面是一个示例命令行启动 Osql.exe,假定计算机正在运行 SQL Server,从运行时客户端和脚本文件被命名为 Checkblk.sql。 请确保要更正-S 参数"服务器"替换为您 SQL Server 的名称服务器正确-i 参数和替换"checkblk.sql"路径和脚本文件的名称与在步骤 2 中创建。
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000						
    注意必须使用其他命令行开关,原因如下:

    • 若要防止换行输出的文件中使其更易于阅读。
    • 若要将输出发送到一个文件使用-o 参数指定,而不是到屏幕,这样如果查询工具中有问题仍输出直到查询工具失败。
以下是可用于创建 sp_blocker_pss70 存储过程的脚本:

use masterGOif exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss70') and sysstat & 0xf = 4)   drop procedure dbo.sp_blocker_pss70GOcreate proc sp_blocker_pss70 (@latch int = 0, @fast int = 1)as --version 13set nocount ondeclare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @time3 datetimedeclare @dbname nvarchar(128)declare @IsDetached tinyintdeclare @IsEmergencyMode tinyintdeclare @IsInLoad tinyintdeclare @IsInRecovery tinyintdeclare @IsNotRecovered tinyintdeclare @IsOffline tinyintdeclare @IsShutDown tinyintdeclare @IsSingleUser tinyintdeclare @IsSuspect tinyintset @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 != 0x0000create 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 -- Allelse  print '7 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '    + convert(varchar(12), datediff(ms,@time,@time3)) + ' ' + ISNULL (@@servername,'(null)')GO 	
性能

警告:本文已自动翻译

属性

文章 ID:251004 - 上次审阅时间:12/05/2015 18:15:23 - 修订版本: 3.2

Microsoft SQL Server 7.0 标准版

  • kbnosurvey kbarchive kbmt kbhowto kbinfo kbsqlserv700 KB251004 KbMtzh
反馈