INF:如何監視 SQL Server 7.0 封鎖

本文曾發行於 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 翻譯的. 若要參考原始英文文件內容, 請至以下網址:

performance
內容

文章識別碼:251004 - 最後檢閱時間:12/05/2015 18:15:23 - 修訂: 1.0

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbhowto kbinfo kbsqlserv700 KB251004
意見反應