Sie sind zurzeit offline. Es wird auf die erneute Herstellung einer Internetverbindung gewartet.

INF: Überwachen von SQL Server 7.0-Blockierungen

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
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=1BEGIN   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'ENDGO
  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 masterGOif exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss70') and sysstat & 0xf = 4)   drop procedure dbo.sp_blocker_pss70GOcreate proc sp_blocker_pss70 (@latch int = 0, @fast int = 0)as --version 11set nocount ondeclare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @time3 datetimeset @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 != 0x0000create 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 -- AllGO 


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.
performance
Eigenschaften

Artikelnummer: 251004 – Letzte Überarbeitung: 03/11/2003 10:56:00 – Revision: 1.0

  • Microsoft SQL Server 7.0 Standard Edition
  • kbhowto kbinfo kbsqlserv700 KB251004
Feedback