INF: SQL Server 7.0 블로킹 모니터링 방법

기술 자료 번역 기술 자료 번역
기술 자료: 251004 - 이 문서가 적용되는 제품 보기.
이 문서는 이전에 다음 ID로 출판되었음: KR251004
모두 확대 | 모두 축소

요약

이 문서에서는 블로킹 및 성능 문제를 진단하는 데 사용할 수 있는 저장 프로시저의 사용 방법과 디자인을 설명합니다. 블로킹 문제를 이해하고 해결하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
224453 INF: SQL Server 7.0 블로킹 문제 파악 및 해결 방법
SQL Server 2000에서의 동일한 문제에 대한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
271509 SQL Server 2000 블로킹을 모니터링하는 방법

추가 정보

이 절에서 설명하는 sp_blocker_pss70 저장 프로시저는 다음 정보를 캡처합니다.
  • 이 블로킹 샘플링이 Microsoft Windows NT 성능 모니터 로그나 SQL 프로필러 로그 같은 다른 성능 정보와 동시에 이루어질 수 있도록 SQL Server를 실행 중인 컴퓨터를 기준으로 시작 시간을 캡처합니다.

  • sysprocesses 시스템 테이블을 쿼리하여 SQL Server와의 연결에 대한 정보를 캡처합니다.

  • syslockinfo 시스템 테이블을 쿼리하여 잠금 리소스에 대한 정보를 캡처합니다.

  • DBCC SQLPERF(WAITSTATS)를 실행하여 리소스의 대기 정보를 캡처합니다.

  • DBCC INPUTBUFFER를 실행함으로써 다른 것에 의해 블로킹되거나 다른 것을 블로킹하는 연결들에 대해 현재 실행되는 SQL 일괄 처리를 캡처합니다.

  • SQL Server를 실행하는 컴퓨터를 기준으로 종료 시간을 캡처합니다.
저장 프로시저의 실행으로 인해 성능과 블로킹에 미치는 영향을 줄이기 위해 아래와 같은 최적화를 사용하여 저장 프로시저를 만들었습니다.
  • 하나 이상의 연결이 리소스를 기다리고 있지 않으면 출력이 발생하지 않습니다.

  • 성능을 높이고 이 저장 프로시저가 블로킹되지 않도록 master 데이터베이스의 sysprocessessyslockinfo 시스템 테이블을 직접 쿼리합니다. 따라서 이 저장 프로시저는 Microsoft SQL Server 7.0에만 적용됩니다.

  • 작은 작업 테이블은 DBCC INPUTBUFFER 출력을 얻기 위해 커서에 의해 생성됩니다. 이는 tempdb 데이터베이스에서의 사용에 별다른 영향을 미치지 않습니다.

  • 정보를 수집하는 동안 블로킹이 변경될 수 있기 때문에 sysprocessessyslockinfo 시스템 테이블의 관련 행들로 결과를 축소함으로써 성능을 높이는 고속 모드(Fast Mode)를 사용합니다.

  • 잠금 상태가 아닌 리소스를 추적하려는 경우에는 래치 모드(Latch Mode)를 사용하며 이로 인해 잠금 출력이 생략될 수 있습니다.
이 저장 프로시저는 어떤 쿼리 도구에서나 실행할 수 있기 때문에 유용합니다. 그러나 블로킹 분석을 수행하려면 아래 단계를 따르는 것이 좋습니다.
  1. 사용자가 모니터링하려는 SQL Server 서버 또는 SQL Server 인스턴스에 대해 sysadmin 권한이 있는 로그인을 사용하여 연결되어 있는 동안 쿼리 도구에서 이 문서 끝에 포함된 sp_blocker_pss70 저장 프로시저를 만듭니다.
  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 기술 자료의 다음 문서를 참조하십시오.
    224453 INF: SQL Server 7.0 블로킹 문제 파악 및 해결 방법
  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 
	




Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹에 참여하시기 바랍니다.

속성

기술 자료: 251004 - 마지막 검토: 2005년 10월 19일 수요일 - 수정: 3.0
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft SQL Server 7.0 Standard Edition
키워드:?
kbhowto kbinfo kbsqlserv700 KB251004

피드백 보내기

 

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