現在オフラインです。再接続するためにインターネットの接続を待っています

[INF] SQL Server 7.0 のブロッキングを監視する方法

この記事は、以前は次の ID で公開されていました: JP251004
概要
この資料では、ブロッキングとパフォーマンスの問題の診断で使用するストアド プロシージャの使用方法と設計について説明します。ブロッキングの説明と、それを解決する方法については、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照してください。
224453 [INF] SQL Server 7.0 または SQL Server 2000 のブロッキング問題と解決策
SQL Server 2000 における同様の問題については「サポート技術情報」 (Microsoft Knowledge Base) の次の資料を参照してください。
271509 SQL Server 2000 のブロッキングを監視する方法
詳細
ここで説明する sp_blocker_pss70 ストアド プロシージャは、次の情報を取得します。
  • 開始時刻 (SQL Server を実行しているコンピュータの時刻に依存します)。これにより、ブロッキングのサンプリングを、Microsoft Windows NT パフォーマンス モニタのログや SQL プロファイラのログなどの、その他のパフォーマンス情報の時刻に合わせることができます。

  • SQL Server への接続に関する情報。この情報は、sysprocesses システム テーブルにクエリすることによって得られます。

  • ロック リソースに関する情報。この情報は、syslockinfo システム テーブルにクエリすることによって得られます。

  • リソースの待機に関する情報。この情報は、DBCC SQLPERF(WAITSTATS) を実行することによって得られます。

  • 現在実行中の SQL バッチ。DBCC INPUTBUFFER を実行することによって、他からブロックされる接続または他をブロックする接続に使用します。

  • 終了時刻。SQL Server を実行しているコンピュータの時刻に依存します。
このストアド プロシージャの実行によるパフォーマンスやブロッキングへの影響を低減するために、このストアド プロシージャを作成する際に、以下の最適化が行われました。
  • 出力が生成されるには、少なくとも 1 つの接続がリソースに対して待ち状態である必要があります。

  • master データベースの sysprocesses システム テーブルと syslockinfo システム テーブルに対して直接クエリが行われます。これにより、パフォーマンスが向上し、このストアド プロシージャがブロックされることを防ぎます。そのため、このストアド プロシージャは、Microsoft SQL Server 7.0 に特化しています。

  • カーソルによって小さな作業テーブルが作成され、DBCC INPUTBUFFER の出力が取得されます。これにより、tempdb データベースの使用に大きな影響を与えることはありません。

  • 情報を収集中に、ブロッキングの状態が変化する場合があるため、高速モードが用意されています。高速モードにより、結果を sysprocesses システム テーブルと syslockinfo システム テーブルに関連する行にとどめることでパフォーマンスが向上します。

  • ロックされていないリソースの待機を追跡する場合は、ラッチ モードが用意されています。ラッチ モードにより、ロック出力が省略されます。
このストアド プロシージャは、任意のクエリ ツールから実行できるため便利です。しかし、ブロッキングの分析を実行する場合は、次の手順を実行することをお勧めします。
  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 Knowledge Base) を参照してください。
    224453 [INF] SQL Server 7.0 または SQL Server 2000 のブロッキング問題と解決策
  4. 監視する SQL Server を実行しているコンピュータの Windows コマンド プロンプトで、クエリ ツールの Isql.exe または Osql.exe のいずれかを使用して、手順 2. で作成したスクリプト ファイルを実行します。これは、ネットワークに関する問題によってクエリ ツールの接続が切断されるのを防ぐためです。以下に、Osql.exe を起動するためのコマンド ラインの例を示します。この例では、クライアントが SQL Server を実行しているコンピュータから実行され、スクリプト ファイルの名前が Checkblk.sql であることを前提としています。-S パラメータの "server" を、使用している 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 	
performance
プロパティ

文書番号:251004 - 最終更新日: 10/27/2005 07:42:09 - リビジョン: 3.0

  • Microsoft SQL Server 7.0 Standard Edition
  • kbhowto kbinfo kbsqlserv700 KB251004
フィードバック