INF: Überwachen von SQL Server 7.0-Blockierungen

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=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

Artikelnummer: 251004 – Letzte Überarbeitung: 10.03.2003 – Revision: 1

Feedback