INF: Verstehen und Lösen von Blockierungsproblemen SQL Server

Gilt für: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Standard Edition

Zusammenfassung


In diesem Artikel bezieht sich der Begriff "Verbindung" auf eine einzelne angemeldete Sitzung der Datenbank. Jede Verbindung wird als eine Session-ID (SPID) angezeigt. Jede dieser SPIDs wird oft als ein Prozess bezeichnet aber kein separater Prozesskontext im üblichen Sinne. Stattdessen besteht jede SPID Serverressourcen und Datenstrukturen der Anfragen einer einzelnen Verbindung eines bestimmten Clients erforderlich. Eine einzelne Clientanwendung möglicherweise einen oder mehrere Anschlüsse. Aus der Sicht des SQL Server besteht kein Unterschied zwischen mehreren aus einer einzelnen Clientanwendung auf einen einzelnen Computer und mehrere Verbindungen von mehreren Clientanwendungen oder mehrere Clientcomputer. Eine Verbindung kann eine andere Verbindung, unabhängig davon, ob sie von derselben Anwendung oder separate Programme auf zwei verschiedenen Clientcomputern ausgehen blockieren.

Weitere Informationen


Blockieren ist eine unvermeidbare Eigenschaft alle Datenbank-Managementsystem (RDBMS) mit Sperre Parallelität. In SQL Server tritt blockiert eine SPID eine Sperre für eine bestimmte Ressource und eine zweite SPID versucht, einen widersprüchlichen Sperrentyp auf dieselbe Ressource. Der Zeitrahmen für die erste SPID die Ressource sperrt ist normalerweise sehr klein. Wenn sie die Sperre freigibt, kann die zweite Verbindung Sperren für die Ressource und die Verarbeitung fortsetzen. Dies ist normal und mehrmals im Laufe eines Tages nicht spürbar Systemleistung kommen.

Bereich Dauer und der Transaktionskontext einer Abfrage bestimmen, wie lange die Sperre und ihre Auswirkung auf andere Abfragen. Wenn die Abfrage nicht innerhalb einer Transaktions ausgeführt und keine Sperrhinweise verwendet für SELECT-Anweisung die Sperren nur findet eine Ressource gleichzeitig tatsächlich gelesen wird, nicht für die Dauer der Abfrage. Für INSERT, UPDATE und DELETE-Anweisungen finden die Sperren für die Dauer der Abfrage sowohl Datenkonsistenz und die Abfrage ggf. zurückgesetzt werden.

Bei Abfragen innerhalb einer Transaktion ausgeführt werden die Dauer, für die die Sperre hängen vom Typ der Abfrage, die Transaktionsisolationsebene und ob Sperren, Hinweise in der Abfrage verwendet. Eine Beschreibung der Sperren Sperrhinweise und der Transaktionsisolationsebenen finden Sie unter den folgenden Themen in der Onlinedokumentation zu SQL Server:
  • Sperren in der Datenbank-Engine
  • Anpassen von Sperren und Zeilenversionskontrolle
  • Sperrmodi
  • Lock-Kompatibilität
  • Zeile Versioning basierende Isolationsstufen in der Datenbank-Engine
  • Steuern von Transaktionen (Datenbankmodul)
Wenn Sperrung und Blockierung auf den Punkt sich nachteilig auf die Leistung des Systems, es ist normalerweise aus folgenden Gründen:
  • Eine SPID sperrt eine Reihe von Ressourcen für einen längeren Zeitraum vor der Freigabe. Diese Art Blockierung löst sich mit der Zeit aber kann zu Leistungseinbußen führen.
  • Eine SPID sperrt eine Gruppe von Ressourcen und niemals frei. Diese Art Blockierung löst sich nicht und verhindert den Zugriff auf die betroffenen Ressourcen unbegrenzt.
In der ersten oben genannten Szenario löst sich das Problem selbst mit der Zeit als die SPID die Sperre wieder freigibt. Die Situation möglich sehr unterschiedlich sein, da verschiedene SPIDs mit der Zeit unterschiedliche Ressourcen blockieren, sodass ein bewegliches Ziel. Aus diesem Grund können Situationen Fehlerbehebung mit SQL Server Enterprise Manager oder einzelnen SQL-Abfragen schwierig sein. Die zweite Situation führt in einem konsistenten Zustand, der leichter diagnostiziert werden kann.

Informationen blockieren

Um die Schwierigkeit der Problembehandlung von Blockierproblemen entgegenzuwirken, können ein Datenbankadministrator SQL-Skripts, die ständig den Status der Sperrung und Blockierung in SQL Server. Diese Skripts bieten Snapshots von bestimmten Instanzen im Laufe der Zeit zu einem Bild des Problems. Eine Überwachung mit SQL-Skripts finden Sie in folgenden Artikeln der Microsoft Knowledge Base:
271509 das Überwachen der Blockierung in SQL Server 2005 und SQL Server 2000

Skripts in diesem Artikel werden die folgenden Aufgaben ausführen. Gegebenenfalls wird die Methode zum Abrufen dieser Informationen aus SQL Server Management Studio angegeben.
  1. Identifizieren Sie die SPID (Session ID) zu Beginn der Blockierungskette und die SQL-Anweisung.
    Neben den Skripts in der oben erwähnten Knowledge Base, erkennen Sie die Beginn der Blockierungskette mit Funktionen, die durch SQL Server Management Studio bereitgestellt werden. Verwenden Sie hierzu eine der folgenden Methoden:
    • Mit der rechten Maustaste des Serverobjekts Berichte, Standardberichteerweitern und anschließend klicken Sie auf Aktivität-alle blockierenden Transaktionen. Dieser Bericht zeigt die Transaktionen am Anfang der Kette blockieren. Wenn Sie die Buchung erweitern, zeigt der Bericht Transaktionen Head Transaktion blockiert. Dieser Bericht zeigt auch die "blockieren SQL Statement" und "Blockiert SQL Anweisung."
    • Verwenden Sie DBCC INPUTBUFFER(<spid>) die letzte Anweisung gefunden, die von einer SPID gesendet wurde.
  2. Finden Sie die Transaktions-Schachtelungsebene und den Prozessstatus der blockierenden SPID.
    Die Transaktions-Schachtelungsebene einer SPID ist in der globalen Variable @@TRANCOUNT verfügbar. Jedoch kann es außerhalb der SPID bestimmt werden durch Abfrage der Tabelle Sysprocesses wie folgt:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    Der zurückgegebene Wert ist der Wert @@TRANCOUNT für die SPID. Zeigt die Transaktions-Schachtelungsebene der blockierenden SPID, was wiederum erklären kann, warum die Sperren gehalten werden. Beispielsweise ist der Wert größer als 0 (null) ist die SPID mitten in einer Transaktion (in diesem Fall dürfte behält bestimmte sperren, die es je nach Isolationsstufe der Transaktion verfügt).

    Sie können auch überprüfen, ob Transaktionen in der Datenbank vorhanden ist, mithilfe von DBCC OPENTRAN
    database_name.

SQL Server Profiler Ablaufverfolgungsinformationen sammeln

Zusätzlich zu den oben genannten Informationen ist es oft notwendig, die Aktivitäten auf dem Server gründlich untersuchen ein blockierendes Problem in SQL Server Profiler Spur aufnehmen. Wenn eine SPID mehrere Anweisungen in einer Transaktion ausgeführt wird, der letzten Statementthat vorgelegt werden im Bericht, Puffer oder Monitorausgang Aktivität angezeigt. Aber einer der früheren Befehle der Grund möglicherweise sperren noch immer gehalten werden. Trace Profiler können Sie alle Befehle von einer SPID innerhalb der aktuellen Transaktion ausgeführt. Die folgenden Schritte können Sie SQL Server Profiler einrichten, um eine Spur aufnehmen.
  1. Öffnen Sie SQL Server Profiler.
  2. Im Menü Datei auf neuund klicken Sie dann auf Spur.
  3. Geben Sie auf der Registerkarte Allgemein einen Trace und Dateinamen, Datenerfassung.

    Wichtig Die Ablaufverfolgungsdatei sollte auf eine schnelle lokale oder freigegebene Datenträger geschrieben werden. Vermeiden Sie auf langsam oder einem Netzlaufwerk. Außerdem sicherstellen Sie, dass Server Trace verarbeitet Daten aktiviert ist.
  4. Klicken Sie auf der Registerkarte Ereignisauswahl auf alle Ereignisse anzeigen und die Kontrollkästchen alle Spalten anzeigen .
  5. Fügen Sie auf der Registerkarte Ereignisauswahl aufgelisteten Ereignistypen in Tabelle 1 die Trace hinzu.

    Darüber hinaus können Sie weiteren Ereignistypen aufgeführt sind in Tabelle 2 einschließen, für Weitere Informationen. Beim Ausführen in einer Umgebung mit hohem Produktionsvolumen möglicherweise möchten verwenden Sie nur die Ereignisse in Tabelle 1 sind die meisten Blockierungsprobleme beheben normalerweise ausreichend. In Tabelle 2 zusätzliche Ereignisse einschließlich erleichtern die Ursache eines Problems schnell festzustellen oder diese Ereignisse möglicherweise die Ursache-Anweisung in einer Prozedur mit mehreren Anweisungen zu identifizieren. Jedoch auch Ereignisse in Tabelle 2 auch die Last auf dem System hinzufügen und Umfang der Ablaufverfolgungsergebnisse erhöhen.
Tabelle 1: Typen
ÜberschriftEreignis
Fehler und WarnungAusnahme
Fehler und WarnungAufmerksamkeit
SicherheitsüberwachungAudit Login
SicherheitsüberwachungAudit Logout
SessionsVorhandene Verbindung
Gespeicherte ProzedurenRPC:Starting
TSQLSQL:BatchStarting

Tabelle 2: Weitere Ereignistypen
ÜberschriftEreignis
TransaktionenDTCTransaction
TransaktionenSQLTransaction
Gespeicherte ProzedurenRPC:Completed
TSQLSQL:BatchCompleted
Gespeicherte ProzedurenSP:StmtStarting
Gespeicherte ProzedurenSP:StmtCompleted

Weitere Informationen zur Verwendung von SQL Server Profiler finden Sie SQL Server-Onlinedokumentation.

Identifizieren und Lösen allgemeiner Blockierungsprobleme

Anhand der obigen Informationen können Sie die Ursache für die meisten Blockierungsprobleme bestimmen. Der Rest dieses Artikels ist eine Beschreibung der Verwendung dieser Informationen identifizieren und lösen einige häufig auftretende Blockierungsszenarien. Vorausgesetzt, blockierenden Skripts in Artikel 271509 (früher verwiesen) Informationen auf den blockierenden SPIDs und haben eine Profiler-Verfolgung verwendet mit den oben beschriebenen Ereignissen.

Anzeigen der Blockierungsskriptausgabe

Untersuchen Sie die Ausgabe Sys.sysprocesses an , um die Köpfe der Blockierungskette zu bestimmen
Schnellmodus für die Blockierung Skripts nicht angegeben haben, werden einem Abschnitt "SPIDs am Anfang von Blockierungsketten", der die SPIDs auflistet, die andere SPIDs in der Skriptausgabe blockieren.
SPIDs at the head of blocking chains
Wenn Sie die schnelle Option angegeben haben, können Sie weiterhin die Blockierungskopfteile ermitteln, Ausgabe Sys.sysprocesses an und die Hierarchie der SPID in der gesperrten Spalte angegeben ist.
Untersuchen Sie die Ausgabe Sys.sysprocesses an Informationen zu den SPIDs zu Beginn der Blockierungskette.
Es ist wichtig, die folgenden Sys.sysprocesses an Felder:

Status

Diese Spalte zeigt den Status einer bestimmten SPID. Ruhezustand Status gibt normalerweise an, dass die SPID abgeschlossen ist und darauf wartet, dass die Anwendung eine weitere Abfrage bzw. Batch sendet. Ausführbar, ausgeführtoder Sos_scheduler_yield Status zeigt an, dass die SPID aktuell eine Abfrage bearbeitet. Die folgende Tabelle enthält die verschiedenen Statuswerte kurz.
StatusBedeutung
HintergrundDie SPID wird ein Hintergrundprogramm, wie Deadlockerkennung ausgeführt.
RuhezustandDie SPID wird zurzeit nicht ausgeführt. Dies bedeutet normalerweise, dass die SPID Befehle der Anwendung wartet.
AusführenDie SPID läuft auf einem Planer.
AusführbarDie SPID ist in der ausführbaren Warteschlange einen Planer und Planer Zeit warten.
Sos_scheduler_yieldDie SPID ausgeführt wurde, aber ergab freiwillig die Zeitscheibe Planer zu einer anderen SPID Planer abrufen.
AngehaltenDie SPID wartet auf ein Ereignis eine Sperre oder eine Verriegelung.
RollbackSPID führt einen Rollback einer Transaktion.
DefwakeupGibt an, dass die SPID auf eine Ressource wartet, die gerade freigegeben wird. Feld Waitresource sollte die betreffende Ressource anzugeben.

Open_tran

Dieses Feld gibt Ihnen die Schachelungsebene der Transaktion der SPID an. Ist dieser Wert größer als 0 die SPID innerhalb einer offenen Transaktion und kann von jeder Anweisung innerhalb der Transaktion erworbene Sperren halten.

Waittime, Lastwaittype und Wartetyp

Feld Lastwaittype wird eine Stringdarstellung der Wartetyp Feld eine reservierte interne binäre Spalte ist. Wenn der Wartetyp 0 x 0000 ist, SPID wartet zurzeit nicht für alles und Lastwaittype Wert gibt den letzten Wartetyp , die die SPID. Wenn der Wartetyp nicht NULL ist, gibt der Wert Lastwaittype derzeitigen Wartetyp der SPID.

Eine kurze Beschreibung der verschiedenen Lastwaittype und Wartetyp Werte finden Sie in folgendem Artikel der Microsoft Knowledge Base:
822101 Beschreibung der Waittype und Lastwaittype Spalten in der Tabelle "master.dbo.sysprocesses" in SQL Server 2000 und SQL Server 2005

Weitere Informationen zu dm_os_wait_statsfinden Sie in der Onlinedokumentation zu SQL Server.

Wartezeit Wert kann verwendet werden, um zu bestimmen, ob die SPID fortgesetzt wird. Abfrage Table Sys.sysprocesses an einen Wert im Feld Wartezeit gibt, die kleiner als der Wert der Wartezeit aus einer vorherigen Abfrage Sys.sysprocesses an, dies bedeutet, dass die vorherige Sperre und veröffentlicht und nun auf eine neue Sperre wartet (wenn nicht null beträgt). Dies kann durch einen Vergleich der Waitresource zwischen Sys.sysprocesses an überprüft werden.

Waitresource

Dieses Feld gibt die Ressource, die eine SPID wartet. Die folgende Tabelle listet häufige Waitresource -Formate und ihre Bedeutung:
RessourceFormatBeispiel
TabelleDatabaseID:ObjectID:IndexIDREGISTERKARTE: 5:261575970:1
In diesem Fall ist Databaseid 5 die Beispieldatenbank Pubs und Objekt-ID 261575970 ist die Tabelle Titles und 1 gruppierten Index.
SeiteDatabaseID:FileID:PageIDSEITE: 5:1:104
In diesem Fall ist Databaseid 5 die Datenbank Pubs1 ist die primäre Datendatei und 104 ist eine Seite in der Tabelle Titles .

Um die Objekt-Id identifiziert, zu der die Seite gehört, verwenden Sie den Befehl DBCC-Seite (Dbid Fileid Pageid, Output_option) und die M_objId betrachten. Zum Beispiel:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
SchlüsselDatabaseID:Hobt_id (Hashwert für den Indexschlüssel)Schlüssel: 5:72057594044284928 (3300a4f361aa)

In diesem Fall ist Databaseid 5 die Datenbank Pubs, Hobt_ID 72057594044284928 entspricht nicht gruppierten Index_id 2 für Objekt-Id 261575970 (TabelleTitles ). Verwenden der Katalogansicht sys.partitions Hobt_id an einem bestimmten Index-Id und Objekt-Id zuordnen. Es gibt keine Möglichkeit, den Index Schlüsselhash einen bestimmten Index-Schlüsselwert aus dieser zu entfernen.
ZeileDatabaseID:FileID:PageID:Slot(row)ENTFERNEN: 5:1:104:3

In diesem Fall ist Databaseid 5 die Datenbank Pubs, 1 ist die primäre Datendatei 104 ist eine Seite in der Tabelle Titles und Steckplatz 3 gibt die Zeilenposition auf der Seite an.
KompilierenDatabaseID:ObjectID [kompilieren]Registerkarte: 5:834102012 [kompilieren] nicht sperren eine Tabellensperre jedoch eher eine Kompilierung einer gespeicherten Prozedur. Datenbank-ID 5 ist Pubs, Objekt-ID 834102012 gespeicherte Prozedur Usp_myprocedure. Weitere Informationen Blockierung verursacht durch Kompilieren Sperren finden Sie im Knowledge Base-Artikel 263889.
Andere Spalten

Die verbleibenden Sys.sysprocesses an Spalten bieten die Ursache für ein Problem. Ihre Nützlichkeit hängt die Begleitumstände des Problems. Beispielsweise können Sie ermitteln das Problem tritt nur bei bestimmten Clients (Hostname) auf bestimmte Netzwerkbibliotheken (net_library) auftritt, wenn der letzte Batch von einer SPID gesendet wurde (Last_batch).
Untersuchen Sie die Ausgabe von DBCC INPUTBUFFER.
Für jede SPID am Kopfteil einer Blockierungskette oder mit einem Wartetyp außer Null führt das Blockierungsskript DBCC INPUTBUFFER um die aktuelle Abfrage für diese SPID zu bestimmen.

In vielen Fällen ist dies die Abfrage, die die Sperren, die andere Benutzer verursacht statt blockieren. Jedoch ist die SPID innerhalb einer Transaktion, die Sperren möglicherweise von einer zuvor ausgeführten Abfrage nicht die aktuelle Datenbank erworben werden. Sie sollten daher die Profiler-Ausgabe für die SPID nicht nur die Eingabepuffer anzeigen.

Hinweis Da das Blockierungsskript mehrere Schritte umfasst, ist es möglich, dass eine SPID im ersten Abschnitt als ein Kopfteil einer Blockierungskette erscheinen jedoch zum Zeitpunkt der Ausführung der DBCC INPUTBUFFER-Abfrage es nicht mehr blockiert ist und der INPUTBUFFER nicht erfasst. Dies bedeutet, dass die Blockierung löst sich für diese SPID möglicherweise oder möglicherweise kein Problem sein. An diesem Punkt können entweder des Blockierungsskripts verwenden, um zu versuchen, die Eingabepuffer vor seinem (obwohl es immer noch keine Garantie) zu erfassen oder die Profilerdaten aus diesem Zeitrahmen zu bestimmen, welche Abfragen der SPID ausgeführt wurden.

Ansicht der Profilerdaten

Effiziente Auswertung der Profilerdaten ist äußerst wertvoll blockierenden Probleme. Wichtig zu erkennen, dass nicht alles suchen erfasst werden; Seien Sie wählerisch. Profiler bietet Funktionen, mit denen Sie effektiv die erfassten Daten. Im Dialogfeld Eigenschaften (Menü Datei klicken Sie auf " Eigenschaften"), Profiler können Sie die angezeigten Daten durch Entfernen von Datenspalten oder Ereignissen, Gruppierung (Sortierung) von Datenspalten und Filter einschränken. Sie können die gesamte Spur oder eine bestimmte Spalte nach bestimmten Werten suchen (klicken Sie auf im Menü Bearbeiten auf Suchen). Können die Profilerdaten auch in einer SQL Server-Tabelle speichern (klicken Sie im Menü Datei auf Speichern unter und klicken Sie dann auf Tabelle) und SQL-Abfragen ausführen.

Achten Sie darauf, dass Sie Filter nur auf eine bereits gespeicherte Ablaufverfolgungsdatei. Wenn dieser Schritte auf einem aktiven Spur durchführen verlieren Sie Daten, die seit dem Start Trace erfasst wurden. Eine aktive Spur in einer Datei speichern oder Tabelle zuerst (im Menü Datei auf Speichern unterklicken) und dann erneut öffnen (klicken Sie auf im Menü Datei auf Öffnen) bevor Sie fortfahren. Beim Arbeiten an einer gespeicherten Ablaufverfolgungsdatei der Filter dauerhaft entfernt nicht die ausgefilterten Daten, es nur zeigt nicht alle Daten. Sie können hinzufügen und Entfernen-Ereignisse und Datenspalten je nach Bedarf zu konzentrieren Ihre Suchvorgänge.

Was Sie suchen:
  • Welche Befehle hat die SPID am Kopfende einer Blockierungskette innerhalb der aktuellen Transaktion ausgeführt?
    Filtern die Daten auf eine bestimmte SPID, die am Kopfende einer Blockierungskette (Menü Datei klicken Sie auf Eigenschaften, dann auf der Registerkarte Filter geben den SPID-Wert). Sie können dann die Befehle überprüfen vor diese Blockierung einer anderen SPID ausgeführt hat. Wenn Sie die Transaktionsereignisse einschließen, können leicht herausfinden, wann eine Transaktion begonnen wurde. Andernfalls suchen Textspalte beginnen, speichern, COMMIT oder ROLLBACK TRANSACTION-Vorgängen. Verwenden Sie den Open_tran -Wert aus der Tabelle Sysprocesses sicherstellen, dass Sie alle Transaktionsereignisse erfassen. Kennen der ausgeführten Befehle und des Transaktionskontexts können Sie bestimmen, warum eine SPID Sperren hält.

    Beachten Sie, dass Ereignisse und Datenspalten zu entfernen. Anstatt sowohl und abgeschlossene Ereignisse. Den blockierenden SPIDs nicht gespeicherte Prozeduren Entfernen der
    SP: ab oder SP: abgeschlossen Ereignisse; SQLBatch und RPC -Ereignisse zeigen den Prozeduraufruf. Wenn diese Detailebene müssen die SP-Ereignisse nur anzeigen.
  • Was ist die Abfragen für SPIDs am Kopfteil von Blockierungsketten?
    Enthält die oben genannten abgeschlossenen Ereignisse zeigt die Spalte Dauer der Ausführung der Abfrage. Dadurch können Abfragen identifizieren, die Blockierung verursachen. Um festzustellen, warum die Abfrage sehr langsam ist, CPU, Lesenund schreibt Spalten sowie Ereignis Ausführungsplan anzeigen.

Kategorisierung häufiger Blockierungsszenarien

Die unten aufgeführte Tabelle Symptome auf ihre Ursachen. Die Zahl im Feld Szenario entspricht die Anzahl der diesem Artikel im Abschnitt "Allgemeine blockieren Szenarien und Auflösung". Die Wartetyp, Open_Tranund Spalten finden Sie in Sysprocesses . Der löst? Spalte gibt an, ob die Blockierung von allein auflösen wird.

SzenarioWartetypOpen_TranStatusLöst?Weitere Symptome
1Wert ungleich null> = 0ausführbarAbfrage Ja, nach Abschluss der.Spalten Physical_IO, CPU und/oder Memusage werden mit der Zeit erhöhen. Dauer der Abfrage werden hoch abgeschlossen.
20x0000> 0RuhezustandNein, aber die SPID kann gelöscht werden.In der Verfolgung der Profiler möglicherweise ein Warnungssignal angezeigt werden, für diese SPID einen Abfragetimeout angibt oder Abbrechen aufgetreten.
30x0000> = 0ausführbarNein Wird nicht auflösen, bis der Client alle Zeilen abruft oder die Verbindung schließt. SPID kann gelöscht werden, aber es kann bis zu 30 Sekunden dauern.Wenn Open_tran = 0, und die SPID Sperren hält, während die Transaktionsisolationsebene standardmäßig (Lesen COMMMITTED) ist, ist dies wahrscheinlich.
4Variiert> = 0ausführbarNein Wird nicht auflösen, bis der Client Abfragen abbricht oder die Verbindung trennt. SPIDs können gelöscht werden, jedoch können bis zu 30 Sekunden dauern.Hostname -Spalte in Sysprocesses für die SPID am Kopfende einer Blockierungskette wird eine identisch sein wird.
50x0000> 0RollbackJa.Ein Warnungssignal möglicherweise sichtbar in der Verfolgung der Profiler für die SPID einen Abfragetimeout angibt oder Abbrechen aufgetreten oder einfach eine Rollback-Anweisung ausgegeben wurde.
60x0000> 0RuhezustandSchließlich. Windows NT feststellt, dass die Sitzung nicht mehr aktiv ist, werden die SQL Server-Verbindung unterbrochen.Der Wert liegt in Sysprocesses ist viel früher als die aktuelle Zeit.

Gemeinsame Blockierungsprobleme und Auflösung

Die unten aufgelisteten Szenarien müssen in der Tabelle oben aufgeführten Merkmale. Dieser Abschnitt enthält ggf. Weitere Informationen sowie Lösungswege.
  1. Blockierung aufgrund einer normal ausgeführten Abfrage mit langer Ausführungszeit

    Auflösung:
    Die Lösung für diese Art Blockierungsproblem ist Wege Optimierung die Abfrage. Eigentlich diese Art Blockierungsproblem möglicherweise nur ein Leistungsproblem und muss als ein solches angegangen. Informationen zur Problembehandlung einer bestimmten langsamen Abfrage finden Sie im folgenden Artikel der Microsoft Knowledge Base:
    243589 Behandlung von langsamen Abfragen auf SQL Server 7.0 oder höher

    Finden Sie für die gesamte Anwendung Leistung Problembehandlung in folgenden Knowledge Base-Artikel:
    224587 wie: Problembehandlung bei Leistung der Anwendung mit SQL Server

    Weitere Informationen finden Sie in SQL Server 2008-Onlinedokumentation Thema Performance Monitoring und Tuning-Themen auf der folgenden MSDN-Website:Haben Sie eine lange dauernde Abfrage, die andere Benutzer blockiert und nicht optimiert werden, sollten Sie von einer OLTP-Umgebung auf unterstützt verschoben.
  2. Blockierung durch eine SPID im Ruhezustand, die die Transaktionsschachtelungsebene verloren hat

    Diese Art Blockierung kann oft festgestellt werden durch eine SPID im Ruhezustand oder auf einen Befehl wartet noch deren Transaktionsschachtelungsebene (@@TRANCOUNT, Open_tran von Sysprocesses) größer als NULL ist. Dies kann auftreten, wenn die Anwendung ein Abfragetimeout auftritt oder ein Abbruch ohne auch die erforderliche Anzahl von ROLLBACK oder COMMIT-Anweisung gibt. Wenn eine SPID einen Abfragetimeout oder Abbruch erhält, es wird beendet die aktuelle Abfrage bzw. Stapelverarbeitung, aber nicht automatisch zurücksetzen oder einen commit für die Transaktion. Die Anwendung ist dafür verantwortlich, wie SQL Server übernehmen, einfach durch eine einzelne Abfrage abgebrochen eine gesamte Transaktion ein Rollback ausgeführt werden muss. Abfragetimeout oder Abbruch wird als Ereignis Signal Aufmerksamkeit für die SPID in der Verfolgung der Profiler angezeigt.

    Zur Veranschaulichung Thema Query Analyzer folgende einfache Abfrage:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2

    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    Während die Abfrage ausgeführt wird, klicken Sie auf die rote Schaltfläche Abbrechen . Nach Abbruch der Abfrage gibt SELECT @@TRANCOUNT, dass die Transaktionsschachtelungsebene eine. War dies eine DELETE- oder UPDATE-Abfrage oder HOLDLOCK verwendet worden wählen alle erworbenen Sperren immer noch gehalten werden. Trotz der obigen Abfrage einer anderen erworben hatte und Sperren in der Transaktion gehalten würden sie immer noch halten des oben genannten SELECT abgebrochen wurde.

    Auflösung:

    • Clientanwendungen müssen ordnungsgemäß verwalten Schachtelungsebenen Transaktion oder sie möglicherweise ein blockierendes Problem nach Abbruch der Abfrage so. Dies kann auf verschiedene Arten erfolgen:
      1. Senden Sie Fehlerhandler der Clientanwendung IF @@TRANCOUNT > 0 ROLLBACK TRAN nach jedem beliebigen Fehler, auch wenn die Clientanwendung eine Transaktion nicht glauben wird geöffnet. Dies ist erforderlich, da eine gespeicherte Prozedur aufgerufen, während der Batch eine Transaktion ohne Kenntnis der Clientanwendung gestartet haben könnte. Verhindert, dass Auflagen wie Abbruch der Abfrage die Prozedur selbst Rollbackcode weist die Prozedur Logik zum Überprüfen IF @@ERROR <> 0 und zum Abbruch der Transaktion, nicht so ausgeführt wird die aktuelle Anweisung hinaus ausgeführt Anfragen.
      2. Verwenden Sie SET XACT_ABORT ON für die Verbindung oder in gespeicherten Prozeduren Transaktionen beginnen und sind nicht nach einem Fehler bereinigen. Bei einem Laufzeitfehler wird diese Einstellung alle offenen Transaktionen abbrechen und die Steuerung an den Client zurück. Beachten Sie, dass T-SQL-Anweisungen nach der Anweisung verursacht den Fehler nicht ausgeführt werden.
      3. Wenn Verbindungspooling in einer Anwendung, die Verbindung und führt eine kleine Anzahl Abfragen verwendet wird vor Freigabe der Verbindung an den Pool wie eine Web-basierte Anwendung öffnet Vorübergehende Deaktivierung des Verbindungspoolings kann helfen, die Problem, bis die Clientanwendung Fehlerbehandlung entsprechend angepasst. Durch Deaktivierung des Verbindungspoolings verursachen Freigabe der Verbindung eine physische Abmeldung der SQL Server-Verbindung, was des Servers einen Rollback aller offenen Transaktionen.
      4. Wenn Verbindungspooling aktiviert und der Zielserver SQL Server 2000 ist kann die Aktualisierung des Clientcomputers auf MDAC 2.6 oder höher nützlich sein. Diese Version der MDAC-Komponenten hinzugefügt Code ODBC-Treiber und OLE DB-Provider, damit die Verbindung "zurückgesetzt" werden würde, bevor er wiederverwendet wird. Dieser Aufruf an Sp_reset_connection bricht Serverinitiierte Transaktionen (DTC­Transaktionen durch die Clientanwendung sind nicht betroffen), setzt die Standarddatenbank, Optionen und So weiter. Beachten Sie, dass die Verbindung nicht zurückgesetzt wird, bis es aus dem Verbindungspool wiederverwendet wird, so kann ein Benutzer eine Transaktion geöffnet und lassen die Verbindung an den Verbindungspool konnte jedoch möglicherweise für einige Sekunden lang nicht wiederverwendet werden die Transaktion wird geöffnet. Wenn die Verbindung nicht wiederverwendet wird, wird die Transaktion abgebrochen, wenn ist überschritten, und die Verbindung aus dem Verbindungspool entfernt. Daher ist es für die Clientanwendung Transaktionen in ihrer Fehlerbehandlung abzubrechen oder SET XACT_ABORT ON zum Vermeiden dieser potenziellen Verzögerung.
    • Eigentlich diese Art Blockierungsproblem möglicherweise ein Leistungsproblem auch als ein solches angegangen werden müssen. Wenn die Ausführungszeit der Abfrage reduziert werden kann, würde das Abfragetimeout oder-Abbruch nicht auftreten. Es ist wichtig, dass die Anwendung das Zeitlimit oder Abbrechen Szenarien treten sie aber profitieren Sie möglicherweise auch die Leistung der Abfrage zu untersuchen.
  3. Blockierung durch eine SPID, deren zugehörige Clientanwendung nicht alle Ergebniszeilen vollständig abrief

    Nach dem Senden einer Abfrage an den Server, müssen alle Programme sofort alle Ergebniszeilen vollständig abzurufen. Wenn eine Anwendung nicht alle Ergebniszeilen vollständig abruft, können Sperren auf Tabellen, die andere Benutzer blockieren bleiben. Wenn Sie eine, die transparent SQL-Anweisungen an den Server sendet Anwendung, muss die Anwendung alle Ergebniszeilen vollständig abruft. Wenn dies nicht der Fall (und dazu konfiguriert werden kann), Sie können möglicherweise das Problem beheben. Um das Problem zu vermeiden, können Sie mit unerwünschtem Verhalten Applikationen ein Bericht-oder Decision Support-Datenbanken beschränken.

    Auflösung:

    Die Anwendung muss umgeschrieben werden, um alle Ergebniszeilen vollständig abzurufen.
  4. Blockierung aufgrund eines verteilten Client-/Server-Deadlocks

    Im Gegensatz zu einem herkömmlichen Deadlock nutzt ein verteilter Deadlock nicht nachweisbar RDBMS sperren. Dies ist darauf zurückzuführen, dass nur eine der am Deadlock beteiligten Ressourcen eine SQL Server-Sperre ist. Die andere Seite des Deadlocks ist auf Anwendung, die SQL Server keine Kontrolle hat. Es folgen zwei Beispiele beschrieben und verschiedene Arten der Anwendung können vermeiden.

    1. Verteilter Client-/Server-Deadlock mit einen einzigen Client-Thread
      Verfügt der Client mehrere Verbindungen öffnen und einen einzelnen Ausführungsthread, kann die folgenden verteilten Deadlocks auftreten. Aus Platzgründen bezieht sich der Terminus "Dbproc" verwendet, hier Clientverbindungsstruktur.


      SPID1------blocked on lock------->SPID2
      /\ (waiting to write results
      | back to client)
      | |
      | | Server side
      | ================================|==================================
      | <-- single thread --> | Client side
      | \/
      dbproc1 <------------------- dbproc2
      (waiting to fetch (effectively blocked on dbproc1, awaiting
      next row) single thread of execution to run)
      Im obigen Fall hat ein einzelner Client Anwendungsthread zwei geöffneten Anschlüsse. Er sendet asynchron einen SQL-Vorgang unter dbproc1. Dies bedeutet, dass es nicht auf den Aufrufs zunächst warten. Die Anwendung sendet einen weiteren SQL-Vorgang in dbproc2 und wartet auf die Ergebnisse der Verarbeitung der zurückgegebenen Daten. Wenn die Daten anfangen zurückzukommen (gleichgültig, welcher Dbproc zuerst reagiert – nehmen ist dbproc1), verarbeitet Sie alle Daten unter diesem Dbproc zurückgegebenen. Sie ruft Ergebnisse von dbproc1 bis SPID1 (weil die beiden Abfragen asynchron auf dem Server ausgeführt werden) durch eine von SPID2 gehaltene Sperre blockiert wird. Zu diesem Zeitpunkt warten dbproc1 unbegrenzt weitere Daten. SPID2 wird nicht auf eine Sperre blockiert, versucht jedoch, Daten an seinen Client dbproc2 zu senden. Jedoch wird dbproc2 effektiv von dbproc1 auf der Anwendungsschicht blockiert einzelne Ausführungsthread für die Anwendung wird von dbproc1. Dies führt zu einem Deadlock, den SQL Server nicht feststellen oder auflösen, da nur eine der beteiligten Ressourcen eine SQL Server-Ressource ist.
    2. Verteilter Client-/Server-Deadlock mit einem Thread pro Verbindung

      Selbst wenn ein separater Thread für jede Verbindung des Clients besteht, kann eine Abwandlung dieses verteilten Deadlocks auftreten wie im folgenden Beispiel dargestellt.


      SPID1------blocked on lock-------->SPID2
      /\ (waiting on net write) Server side
      | |
      | |
      | INSERT |SELECT
      | ================================|==================================
      | <-- thread per dbproc --> | Client side
      | \/
      dbproc1 <-----data row------- dbproc2
      (waiting on (blocked on dbproc1, waiting for it
      insert) to read the row from its buffer)
      Dieser Fall ähnelt Beispiel A, dbproc2 und SPID2 Ausführen eine SELECT-Anweisung Zeile zur Zeit zu verarbeiten und jede Zeile über einen Puffer an dbproc1 für eine INSERT-, Update- oder-Anweisung für dieselbe Tabelle DELETE. Letztendlich wird SPID1 (INSERT, UPDATE oder DELETE ausgeführt) durch eine von SPID2 gehaltene Sperre blockiert wird (ggf. wählen). SPID2 schreibt eine Ergebniszeile an den Client dbproc2. Dbproc2 versucht dann, die Zeile in einem Puffer an dbproc1 zu übergeben, aber findet dbproc1 ausgelastet ist (es ist warten auf Beenden des aktuellen INSERT, der SPID2 blockiert SPID1 blockiert). Zu diesem Zeitpunkt ist dbproc2 auf der Anwendungsschicht durch dbproc1 blockiert, dessen SPID (SPID1) auf Datenbankebene durch SPID2 blockiert ist. Wieder führt dies zu einem Deadlock, den SQL Server nicht feststellen oder auflösen, da nur eine der beteiligten Ressourcen eine SQL Server-Ressource ist.
    Beide Beispiele A und B sind äußerst wichtige Probleme, denen Entwickler beachten müssen. Sie müssen Programme diese Fälle entsprechend code.

    Auflösung:

    Zwei Lösungen sind ein Abfragetimeout oder zusammengefasste Verbindung verwenden.

    • Abfragetimeout
      Wenn ein Abfragetimeout wurde Wenn verteilten Deadlocks, werden fehlerhafte Wenn Timeout ausgelöst. Anzeigen Sie DB-Library oder ODBC-Dokumentation weitere Informationen zur Verwendung eines Abfragetimeouts
    • Verbindung gebunden
      Dieses Feature ermöglicht einem Client mehrere Verbindungen zu binden in eine Transaktion, die Verbindungen nicht gegenseitig blockieren. Weitere Informationen finden Sie unter "Verwendung gebunden Connections" in SQL Server 7.0-Onlinedokumentation.
  5. Blockierung durch eine SPID in eine "Golden" oder Rollback Zustand

    Eine Datenmodifizierungsabfrage, die getötet oder außerhalb einer benutzerdefinierten Transaktion abgebrochen wird rückgängig gemacht werden. Dies kann auch als Nebeneffekt die Client-Computer neu gestartet und der Netzwerk-Sitzung trennen auftreten. Ebenso wird eine als Deadlockopfer ausgewählte Abfrage zurückgesetzt. Eine Datenmodifizierungsabfrage kann oft zurückgesetzt werden schneller als ursprünglich Änderungen wurden. Z. B. wenn Delete-, INSERT- oder UPDATE-Anweisung für eine Stunde ausgeführt wurden, konnte mindestens eine Stunde Rollback dauert. Erwartetes Verhalten, ist da Änderungen vollständig rückgängig gemacht werden müssen oder Transaktions- und physische Integrität in der Datenbank gefährdet Da dies stattfinden muss, markiert SQL Server die SPID in einem Status "golden" oder "Rollback (d. h. nicht getötet werden oder als Deadlockopfer ausgewählt). Dies kann oft festgestellt werden, anhand der Ausgabe von Sp_who, welche möglicherweise den ROLLBACK-Befehl angibt. Sys.sysprocesses an die Spalte Status zeigt einen ROLLBACK-Status, der ebenfalls in Sp_who Ausgabe oder in SQL Server Management Studio Aktivitäts-Anzeige erscheint.
    Auflösung:

    Sie müssen die SPID beendet Rollback vorgenommenen Änderungen warten.

    Wenn der Server in dieser Vorgang beendet und die Datenbank im Wiederherstellungsmodus beim Neustart werden nicht zugegriffen werden, bis alle offenen werden Transaktionen verarbeitet. Start Recovery dauert im Wesentlichen die gleiche Menge pro Transaktion Run-Time-Recovery und die Datenbank ist nicht verfügbar. Daher werden muss des Servers eine SPID in einem Rollback-Zustand zu häufig kontraproduktiv.

    Um diese Situation zu vermeiden, führen Sie großen Batch INSERT, UPDATE oder nicht LÖSCHVORGÄNGE während der Stoßzeiten von OLTP-Systemen. Sofern möglich, führen Sie Vorgänge Zeiten geringer Aktivität.
  6. Blockierung aufgrund einer verwaisten Verbindung

    Wenn die Client-Anwendung fallen oder der Client-Arbeitsstation neu gestartet wird, netzwerksitzung mit dem Server kann unter Umständen nicht sofort abgebrochen. Aus Sicht des Servers Client wird weiterhin vorhanden sein, und alle erfassten Sperren möglicherweise weiterhin beibehalten werden. Für Weitere Informationen klicken Sie auf die folgenden Artikelnummer der Microsoft Knowledge Base:

    137983 Behandlung von verwaisten Verbindungen in SQL Server


    Auflösung:

    Wenn die Clientanwendung ohne ordnungsgemäßes Bereinigen ihrer Ressourcen getrennt wurde, können Sie mithilfe des Befehls KILL SPID beenden. Der KILL-Befehl verwendet den SPID-Wert als Eingabe. Beispielsweise um kill SPID 9 einfach mit folgendem Befehl:

    KILL 9

    Hinweis Der KILL-Befehl kann durch das Intervall überprüft der KILL-Befehl abgeschlossen bis zu 30 Sekunden dauern.

Anwendung an Blockierungsprobleme

Möglicherweise eine Tendenz serverseitige optimieren und Plattform Probleme konzentrieren, wenn ein blockierendes Problem. Jedoch dies normalerweise führen nicht zu einer Lösung und kann Zeit und Energie besser in die Untersuchung der Clientanwendung und der Abfragen investiert aufnehmen. Egal welche Überblick über die Datenbankaufrufe die Anwendung offen legt, erfordert ein blockierendes Problem dennoch häufig die Überprüfung der genauen SQL-Anweisungen, die durch die Anwendung und der Anwendung genau übermittelt Verhalten hinsichtlich Abfragen abgebrochen, verbindungsmanagement abrufen alle Zeilen ergeben, usw. Das Entwicklungstool expliziten Steuerung verbindungsmanagement Abfragen abgebrochen, Abfragetimeout, Ergebnis abrufen, und nicht erlaubt, können blockierende Probleme nicht aufgelöst werden. Diese Möglichkeit sollte vor der Auswahl ein Anwendungsentwicklungstool für SQL Server speziell für geschäftskritische OLTP-Umgebung genau untersucht werden.

Unbedingt sorgfältig während der Entwurfs- und Konstruktionsphase der Datenbank und der Anwendung erfolgen. Insbesondere sollten der Ressourcenverbrauch Isolationsstufe und Transaktionspfadlänge für jede Abfrage ausgewertet werden. Jede Abfrage und Transaktion sollte so unkompliziert wie möglich sein. Eine gute Verbindungsverwaltungsdisziplin muss angewandt werden. Geschieht dies nicht, kann die Anwendung bei wenigen Benutzern eine akzeptablen Leistung haben anscheinend, dass möglicherweise deutlich die Anzahl der Benutzer verschlechtert die Leistung beeinträchtigen.

Microsoft SQL Server kann mit ordnungsgemäßer Entwurf Tausende von Benutzern gleichzeitig auf einem einzelnen Server mit wenig Sperren unterstützen.