INF: Überwachen von SQL Server 7.0-Blockierungen

SPRACHE AUSWÄHLEN SPRACHE AUSWÄHLEN
Artikel-ID: 251004 - Produkte anzeigen, auf die sich dieser Artikel bezieht
Dieser Artikel wurde zuvor veröffentlicht unter D251004
Dieser Artikel ist eine Übersetzung des folgenden englischsprachigen Artikels der Microsoft Knowledge Base:
251004 INF: How to Monitor SQL Server 7.0 Blocking
Alles erweitern | Alles schließen

Zusammenfassung

Dieser Artikel dokumentiert den Einsatz und die Gestaltung einer gespeicherten Prozedur, mit deren Hilfe Sie Blockierungs- und Leistungsprobleme diagnostizieren können. Informationen zum Verständnis und zur Behebung von Blockierungsproblemen finden Sie im folgenden Artikel der Microsoft Knowledge Base:
224453 INF: Understanding and Resolving SQL Server 7.0 Blocking Problem
Informationen zum selben Problem in SQL Server 2000 finden Sie in folgendem Artikel der Microsoft Knowledge Base:
271509 INF: How to Monitor SQL Server 2000 Blocking

Anmerkung: In diesem Artikel wird ein DBCC-Befehl (DBCC PSS) beschrieben, der nicht unterstützt wird. Dieser Befehl kann unter Umständen ein unerwartetes Verhalten hervorrufen. Microsoft kann nicht garantieren, dass Probleme, die von einer falschen Verwendung dieses DBCC-Befehls herrühren, behoben werden können. Benutzen Sie den DBCC-Befehl auf eigene Verantwortung. Dieser DBCC-Befehl ist in zukünftigen Versionen von SQL Server möglicherweise nicht verfügbar. Eine Übersicht über die in SQL Server unterstützten DBCC-Befehle finden Sie in der Online-Dokumentation von SQL Server unter dem Thema "DBCC (T-SQL)".

Weitere Informationen

Mit der gespeicherten Prozedur sp_blocker_pss70, die in diesem Abschnitt beschrieben wird, werden die folgenden Informationen eingeholt:
  • Die Startzeit (auf der Basis der Daten des Computers, auf dem SQL Server ausgeführt wird), damit dieses Blockierungsbeispiel mit anderen Leistungsinformationen abgeglichen werden kann, z.B. mit einem Protokoll des Microsoft Windows NT-Systemmonitors oder einem SQL Profiler-Protokoll.


  • Informationen zu Verbindungen mit SQL Server durch Abfragen der Systemtabelle sysprocesses.


  • Informationen zu LOCK-Ressourcen durch Abfragen der Systemtabelle syslockinfo.


  • Informationen zu Ressourcenwartezeiten durch Ausführen von DBCC SQLPERF(WAITSTATS).


  • Das aktuell ausgeführte SQL-Batch für Verbindungen, die durch andere Verbindungen blockiert sind oder andere blockieren, durch Ausführen von DBCC INPUTBUFFER.


  • Zusätzliche Informationen zu Verbindungen, die andere Verbindungen blockieren, aber nicht selbst blockiert sind, durch Ausführen von DBCC PSS.


  • Die Endzeit auf der Basis der Daten des Computers, auf dem SQL Server ausgeführt wird.
Die gespeicherte Prozedur wurde mit den nachstehend genannten Optimierungen erstellt, um die Auswirkungen auf Leistung und Blockierungen zu minimieren, die das Ausführen dieser Prozedur hat:
  • Es wird nur dann eine Ausgabe generiert, wenn mindestens eine Verbindung auf eine Ressource wartet.


  • Die Systemtabellen sysprocesses und syslockinfo aus der master-Datenbank werden direkt abgefragt, um die Leistung zu verbessern und zu verhindern, dass diese gespeicherte Prozedur blockiert wird. Aus diesem Grund ist diese gespeicherte Prozedur nur für Microsoft SQL Server 7.0 geeignet.


  • Durch den Cursor wird eine kleine Arbeitstabelle erstellt, um die Ausgabe für die Befehle DBCC INPUTBUFFER und DBCC PSS zu erhalten; dies hat keine nennenswerten Auswirkungen auf die Auslastung der Datenbank tempdb.


  • Da sich die Blockierung während des Einholens der Informationen ändern kann, gibt es einen Schnellmodus, der die Leistung verbessert, indem die Ergebnisse auf die wesentlichen Zeilen der Systemtabellen sysprocesses und syslockinfo beschränkt werden.


  • Wenn Sie nicht blockierende Ressourcenwartezeiten verfolgen möchten, gibt es dafür einen Latch-Modus, bei dem die Ausgabe für blockierende Ereignisse ausgelassen wird.
Diese gespeicherte Prozedur kann aus jedem Abfrageprogramm heraus ausgeführt werden. Microsoft empfiehlt jedoch, bei der Blockierungsanalyse wie folgt vorzugehen:
  1. Erstellen Sie die gespeicherte Prozedur sp_blocker_pss70 (die am Ende dieses Artikels abgedruckt ist) mit einem beliebigen Abfrageprogramm, während Sie mit einem Konto angemeldet sind, das über Berechtigungen des Typs "sysadmin" (Systemadministrator) für den SQL Server-Server oder die Instanz von SQL Server verfügt, die Sie überwachen möchten.
  2. Erstellen Sie eine Skriptdatei mit der folgenden Abfrage, um die gespeicherte Prozedur in einer Schleife ausführen zu lassen. Beachten Sie bitte, dass die Verzögerung zwischen 5 und 60 Sekunden beträgt.
    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. Diese Ausgabe ist sehr nützlich, wenn Sie mit einem Microsoft Windows NT-Systemmonitorprotokoll oder einem SQL Profiler-Protokoll kombiniert wird; sie sollten daher beide Protokolle gleichzeitig erstellen. Informationen dazu, welche Profiler- und Systemmonitor-Ereignisse protokolliert werden und wie die Ergebnisse zu interpretieren sind, finden Sie im folgenden Artikel der Microsoft Knowledge Base:
    224453 INF: Understanding and Resolving SQL Server 7.0 Blocking Problem
  4. Führen Sie die in Schritt 2 erstellte Skriptdatei entweder mit dem Programm "Isql.exe" oder dem Programm "Osql.exe" aus einer Windows-Eingabeaufforderung auf dem Computer aus, auf dem die zu überwachende Instanz von SQL Server ausgeführt wird, um mögliche Probleme wegen einer Trennung des Abfrageprogramms zu vermeiden. Es folgt ein Beispiel für eine Befehlszeile zum Starten von "Osql.exe", bei der davon ausgegangen wird, dass der Client von dem Computer ausgeführt wird, auf dem SQL Server ausgeführt wird, und dass der Name der Skriptdatei "Checkblk.sql" lautet. Achten Sie darauf, den Parameter - S zu korrigieren, ersetzen Sie "server" durch den Namen Ihres SQL Server-Servers, korrigieren Sie den Parameter - i und ersetzen Sie "checkblk.sql" durch Pfad und Namen für die in Schritt 2 erstellte Skriptdatei.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    Beachten Sie bitte, dass Sie die anderen Befehlszeilenoptionen aus den folgenden Gründen verwenden müssen:

    • Um Zeilenumbrüche in der Ausgabedatei zu vermeiden und diese dadurch besser lesbar zu machen.
    • Um die Ausgabe an eine mit dem Parameter - o angegebene Datei zu senden (und nicht auf den Bildschirm), sodass Sie bei etwaigen Problemen mit dem Abfrageprogramm wenigstens eine Ausgabe haben, die bis zum Zeitpunkt des Fehlschlagens des Abfrageprogramms reicht.
Mit dem folgenden Skript können Sie die gespeicherte Prozedur sp_blocker_pss70 erstellen:

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 = 0)
as 
--version 11
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @time3 datetime

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 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'
      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 'SPIDs at the head of blocking chains'
      if exists(select blocked from #probclients where blocked != 0)
      begin
         select spid from #probclients
         where blocked = 0 and spid in (select blocked from #probclients where spid != 0)
         if @latch = 0
         begin
            print ''
            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'

      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 'SPIDs at the head of blocking chains'
      if exists(select blocked from #probclients where blocked != 0)
      begin 
         select spid from #probclients
         where blocked = 0 and spid in (select blocked from #probclients where spid != 0)
         if @latch = 0 
         begin
            print ''
            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 ''

   dbcc traceon(3604)
   Print ''
   Print ''
   Print '***************************************************************************'
   Print 'Ausgabe von DBCC INPUTBUFFER for für alle blockierten oder blockierenden'
   Print 'Ausgabe von DBCC PSS-Info nur für SPIDs am Anfang der blockierenden Ketten'
   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 @latch = 1 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 ''
      print ''
      exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
      exec ('dbcc inputbuffer (' + @spid + ')')
      print ''
      if (@blocked = '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
   dbcc traceoff(3604)
   deallocate ibuffer

   Print ''
   Print ''
   Print '******************************************************************************************'
   Print 'Ausgabe von DBCC OPENTRAN für alle blockierter oder blockierender SPIDs aller Datenbanken'
   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 ''
      print ''
      exec ('print ''DBCC OPENTRAN FOR DBID ' + @spid + '''')
      exec ('dbcc opentran (' + @spid + ')')
      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print ''
      print 'DBCC OPENTRAN FOR tempdb database'
      exec ('dbcc opentran (tempdb)')
   end

   print ''
   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
GO 


Bitte beachten Sie: Bei diesem Artikel handelt es sich um eine Übersetzung aus dem Englischen. Es ist möglich, dass nachträgliche Änderungen bzw. Ergänzungen im englischen Originalartikel in dieser Übersetzung nicht berücksichtigt sind. Die in diesem Artikel enthaltenen Informationen basieren auf der/den englischsprachigen Produktversion(en). Die Richtigkeit dieser Informationen in Zusammenhang mit anderssprachigen Produktversionen wurde im Rahmen dieser Übersetzung nicht getestet. Microsoft stellt diese Informationen ohne Gewähr für Richtigkeit bzw. Funktionalität zur Verfügung und übernimmt auch keine Gewährleistung bezüglich der Vollständigkeit oder Richtigkeit der Übersetzung.

Eigenschaften

Artikel-ID: 251004 - Geändert am: Dienstag, 11. März 2003 - Version: 1.0
Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbhowto kbinfo kbsqlserv700 KB251004
Microsoft stellt Ihnen die in der Knowledge Base angebotenen Artikel und Informationen als Service-Leistung zur Verfügung. Microsoft übernimmt keinerlei Gewährleistung dafür, dass die angebotenen Artikel und Informationen auch in Ihrer Einsatzumgebung die erwünschten Ergebnisse erzielen. Die Entscheidung darüber, ob und in welcher Form Sie die angebotenen Artikel und Informationen nutzen, liegt daher allein bei Ihnen. Mit Ausnahme der gesetzlichen Haftung für Vorsatz ist jede Haftung von Microsoft im Zusammenhang mit Ihrer Nutzung dieser Artikel oder Informationen ausgeschlossen.

Ihr Feedback an uns

 

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