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

文書翻訳 文書翻訳
文書番号: 251004 - 対象製品
この記事は、以前は次の 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=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
    					
  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 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 
	

プロパティ

文書番号: 251004 - 最終更新日: 2005年10月27日 - リビジョン: 3.0
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 7.0 Standard Edition
キーワード:?
kbhowto kbinfo kbsqlserv700 KB251004
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com