INF:如何監視 SQL Server 7.0 封鎖

文章翻譯 文章翻譯
文章編號: 251004 - 檢視此文章適用的產品。
本文曾發行於 CHT251004
全部展開 | 全部摺疊

結論

本文介紹一個預存程序的設計與用法,您可以用它來診斷封鎖與效能問題。若要了解並解決封鎖問題,請參閱 Microsoft Knowledge Base 中的下列文件:
224453資訊:了解並解決 SQL Server 7.0 之鎖定問題

其他相關資訊

本節所述的 sp_blocker_pss70 預存程序,會擷取以下資訊:
  • 起始時間 (根據 SQL Server 電腦):以便對照這個封鎖取樣的時間與其他效能資訊的時間,如 Microsoft Windows NT「效能監視器」記錄,或 SQL Server Profiler 記錄。

  • SQL Server 連線資訊:由查詢 sysprocesses 系統資料表而來的。

  • 鎖定 (Lock) 資源資訊:由查詢 syslockinfo 系統資料表而來的。

  • 受封鎖或封鎖他人的連線目前所執行的 SQL 批次:由執行 DBCC INPUTBUFFER 而來。

  • 其他有關封鎖他人、但本身未受封鎖的連線的資訊:由執行 DBCC PSS 而來。

  • 結束時間:根據 SQL Server 電腦,若預存程序執行超過一秒以上。
預存程序建立時,是依據以下最佳化設定,以便執行此預存程序時,可以降低效能阻礙與封鎖情形:
  • 必須至少有一個連線在等待某個資源,這個程序才會產生輸出。

  • 資料庫的 sysprocessessyslockinfo 系統資料表是直接被查詢的,以便提高效能,並防止此預存程序本身被封鎖。因此,本預存程序只適用於 Microsoft SQL Server 7.0。

  • 資料指標建立了一個小型工作資料表,以取得 DBCC INPUTBUFFER 與 DBCC PSS 輸出;這應該不會對使用 tempdb 資料庫有明顯的影響。

  • 因為蒐集資訊時,封鎖會改變,可以使用快速模式 (fast mode),來降低 sysprocessessyslockinfo 系統資料表相關資料列的結果,進而提高效能。
您可以從任何查詢工具執行這個預存程序,因此它很有用。不過,我們建議您使用下列步驟來執行封鎖分析:
  1. 用下面的查詢建立指令檔,在迴圈中執行此預存程序。請注意:延遲時間應該介於 5 至 60 秒。
     DBCC TRACEON (3604)
     GO
     WHILE 1=1
     BEGIN
     EXEC sp_blocker_pss70
     -- Or for fast mode 
    --EXEC sp_blocker_pss70 1
     WAITFOR DELAY '00:00:15'
     END
     GO
  2. 從 ISQL 或 OSQL 執行上述指令碼,使用以下命令列參數來防止換行,並將輸出導向檔案而非螢幕:
    • -w2000

      - 與 -

      -o
    以下是一個可以啟動 OSQL 的命令列範例,它假設用戶端是從 SQL Server 電腦執行的,而指令檔名稱為 Checkblk.sql:
     osql -E -icheckblk.sql -ocheckblk.out -w2000
    若將本輸出與 Microsoft Windows NT「效能監視器」記錄,以及 SQL Server Profiler 記錄一起參照,對於進行分析很有幫助,Microsoft 建議您兩者同時建立。如需有關應該擷取哪些 Profiler 與「效能監視器」事件,以及如何解讀結果的詳細資訊,請參閱 Microsoft Knowledge Base 中的下列文件:
    • 224453資訊:了解並解決 SQL Server 7.0 之鎖定問題
您可以使用下面指令檔來建立 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 (@fast int = 0)
   as 
   set nocount on
   declare @spid varchar(6), @uid varchar(6), @blocked varchar(6)
   declare @tmpchar varchar(255)
   declare @time datetime

   select @time = getdate()

   select spid, blocked, waittype into #probclients from master..sysprocesses where blocked!=0 or waittype != 0x0000
   create unique clustered index pc on #probclients (blocked,spid)
   if exists (select spid from #probclients)
   begin
      select @tmpchar='Start time: ' + convert(varchar(26), @time, 113)
      print @tmpchar

      insert #probclients select distinct blocked, 0, 0x0000 from #probclients
      where blocked not in (select spid from #probclients) and blocked != 0

      print ' '
      if (@fast = 1)
      begin
         select spid, status, blocked, open_tran, waitresource, waittype, 
            waittime, cmd, lastwaittype, cpu, physical_io,
            memusage,last_batch=convert(varchar(26), last_batch,113),
            login_time=convert(varchar(26), login_time,113), 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)

         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
      end  -- fast set

      else  
      begin  -- Fast not set
         select spid, status, blocked, open_tran, waitresource, waittype, 
            waittime, cmd, lastwaittype, cpu, physical_io,
            memusage,last_batch=convert(varchar(26), last_batch,113),
            login_time=convert(varchar(26), login_time,113), net_address,
            net_library,dbid, ecid, kpid, hostname,hostprocess,
            loginame,program_name, nt_domain, nt_username, uid, sid
         from master..sysprocesses

         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)
         print ''

         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
      end -- Fast not set

      dbcc traceon(3604)
      Print ''
      Print ''
      Print '*********************************************************************'
      Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
      Print 'Print out DBCC PSS info only for SPIDs at the head of blocking chains'
      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
         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 ''
         exec ("print 'DBCC INPUTBUFFER FOR SPID " + @spid + "'")
         exec ("dbcc inputbuffer (" + @spid + ")")
         print ''
         if (@blocked = '0')
         -- if DBCC PSS is not required, comment the line above, remove the
         -- comment on the line below and run the stored procedure in fast 
         -- mode
         -- if (@blocked = '0' and @fast = 0)
         begin
            exec ("print 'DBCC PSS FOR SPID " + @spid + "'")
            exec ("dbcc pss (0, " + @spid +")")
            print ''
            print ''
         end
         fetch next from ibuffer into @spid, @blocked
      end
      deallocate ibuffer

      if datediff(millisecond, @time, getdate()) > 1000
      begin
         select @tmpchar='End time: ' + convert(varchar(26), getdate(), 113)
         print @tmpchar
      end

      dbcc traceoff(3604)
   end -- All
   go 

?考

本文件是根據 Microsoft Knowledgebase 文件編號 Q251004 翻譯的. 若要參考原始英文文件內容, 請至以下網址:

http://support.microsoft.com/support/kb/articles/Q251/0/04.asp

屬性

文章編號: 251004 - 上次校閱: 2000年10月21日 - 版次: 1.0
這篇文章中的資訊適用於:
  • Microsoft SQL Server 7.0 Standard Edition
關鍵字:?
kbhowto kbinfo kbsqlserv700 KB251004
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

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