Behebung von Blockierungsproblemen, die durch eine Sperrenausweitung in SQL Server verursacht werden

SPRACHE AUSWÄHLEN SPRACHE AUSWÄHLEN
Artikel-ID: 323630 - Produkte anzeigen, auf die sich dieser Artikel bezieht
Dieser Artikel ist eine Übersetzung des folgenden englischsprachigen Artikels der Microsoft Knowledge Base:
323630 How to resolve blocking problems that are caused by lock escalation in SQL Server
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.
Alles erweitern | Alles schließen

Auf dieser Seite

Zusammenfassung

Eine Sperrenausweitung ist der Prozess, bei dem viele Sperren auf niedriger Ebene (wie zum Beispiel Zeilen- oder Seitensperren) zu Tabellensperren konvertiert werden. Microsoft SQL Server ermittelt dynamisch, wann eine Sperrenausweitung erforderlich ist. Beim Treffen dieser Entscheidung berücksichtigt SQL Server die Anzahl der Sperren für einen bestimmten Scan sowie die Anzahl der Sperren für die gesamte Transaktion und die Menge an Speicher, die im System insgesamt für Sperren benötigt wird. Typischerweise führt das standardmäßige Verhalten von SQL Server nur an den Punkten zu einer Sperrenausweitung, wo dies zu einer Verbesserung der Leistung führt oder wenn der für Sperren benötigte Systemspeicher auf ein angemessenes Maß reduziert werden muss. Das Design einiger Anwendungen oder Abfragen kann jedoch eine Sperrenausweitung zu einem unerwünschten Zeitpunkt auslösen. Durch eine so ausgelöste Tabellensperrung können andere Benutzer blockiert werden. In diesem Artikel wird erklärt, wie Sie feststellen können, ob eine Sperrenausweitung zu Blockierungsproblemen führt, und wie Sie bei einer unerwünschten Sperrenausweitung vorgehen können.

Weitere Informationen

Feststellen, ob eine Sperrenausweitung die Blockierung verursacht

Die meisten Blockierungsprobleme werden nicht durch eine Sperrenausweitung verursacht. Um festzustellen, ob es etwa zu dem Zeitpunkt, zu dem Blockierungsprobleme aufgetreten sind, zu einer Sperrenausweitung gekommen ist, starten Sie eine Ablaufverfolgung mit dem SQL Profiler, die das Ereignis Lock:Escalation enthält. Wenn Sie keine Ereignisse des Typs Lock:Escalation sehen, findet auf Ihrem Server keine Sperrenausweitung statt und die Informationen in diesen Artikel sind auf Ihre Situation nicht anwendbar.

Wenn eine Sperrenausweitung stattfindet, überprüfen Sie, ob die ausgeweitete Tabellensperre andere Benutzer blockiert.

Weitere Informationen dazu, wie Sie den Hauptblockierer und die durch diesen gehaltene Ressource identifizieren können, die andere Serverprozess-IDs (SPIDs) blockiert, finden Sie im folgenden Artikel der Microsoft Knowledge Base:
224453 INF: Verstehen und Lösen von Blockierproblemen in SQL Server 7.0 und SQL Server 2000
Handelt es sich bei der andere Benutzer blockierenden Sperre nicht um eine TAB-Sperre (auf Tabellenebene) mit dem Sperrmodus S (shared/freigegeben) oder X (exklusiv), ist die Sperrenausweitung nicht die Ursache des Problems. Insbesondere wenn es sich bei der TAB-Sperre um eine beabsichtigte Sperre handelt (zum Beispiel mit den Sperrmodi IS, IU oder IX), wurde das Problem nicht durch eine Sperrenausweitung verursacht. Falls Ihre Blockierungsprobleme nicht durch eine Sperrenausweitung verursacht werden, lesen Sie den Artikel Q224453, in dem entsprechende Schritte zur Problembehandlung beschrieben werden.

Wie Sie eine Sperrenausweitung verhindern können

Der einfachste und sicherste Weg, eine Sperrenausweitung zu verhindern, besteht darin, Transaktionen so kurz wie möglich zu halten und die Speicherauslastung durch umfangreiche Abfragen auf ein erforderliches Mindestmaß zu reduzieren, damit die Schwellenwerte für eine Sperrenausweitung nicht überschritten werden. Es gibt mehrere Wege, dieses Ziel zu erreichen, von denen einige nachstehend beschrieben werden:
  • Unterteilen Sie umfangreiche Stapelverarbeitungsvorgänge in mehrere kleinere Vorgänge. Nehmen Sie beispielsweise an, dass Sie die folgende Abfrage ausgeführt haben, um mehrere hunderttausend alte Datensätze aus einer AUDIT-Tabelle zu entfernen, und dass Sie dann festgestellt haben, dass diese Abfrage eine Sperrenausweitung ausgelöst hat, durch die andere Benutzer blockiert werden:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    Wenn Sie diese Datensätze in Blöcken von jeweils einigen Hundert entfernen, können Sie die Anzahl der Sperren pro Transaktion dramatisch reduzieren und eine Sperrenausweitung verhindern. Beispiel:
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Reduzieren Sie die für die Abfrage benötigte Speichermenge, indem Sie die Abfrage so effizient wie möglich gestalten. Umfangreiche Scans oder eine große Anzahl an Lesezeichensuchvorgängen können die Wahrscheinlichkeit für eine Sperrenausweitung erhöhen; außerdem erhöhen sie die Wahrscheinlichkeit von Deadlocks und wirken sich generell negativ auf Parallelität und Leistung aus. Nachdem Sie die Abfrage ermittelt haben, die die Sperrenausweitung verursacht hat, suchen Sie nach Möglichkeiten, neue Indizes zu erstellen oder Index- oder Tabellensuchvorgänge zu entfernen und die Effizienz von Indexsuchvorgängen zu verbessern. Sie könnten die Abfrage zum Beispiel in ein Abfragefenster im Query Analyzer einfügen, um eine automatische Indexanalyse durchführen zu lassen. Klicken Sie hierzu im Menü Abfrage auf Indexoptimierungs-Assistent (in SQL Server 2000) oder auf Indexanalyse ausführen (in SQL Server 7.0).

    Ein Ziel dieser Optimierung besteht darin, durch Indexsuchvorgänge so wenige Zeilen wie möglich zurückgeben zu lassen, um den Aufwand für Lesezeichensuchen zu minimieren (maximieren Sie die Selektivität des Indexes für die jeweilige Abfrage). Falls SQL Server schätzt, dass der logische Operator der Lesezeichensuche möglicherweise viele Zeilen zurückgeben wird, verwendet das Programm eventuell einen Vorabrufvorgang (PREFETCH), um die Lesezeichensuche durchzuführen. Wenn SQL Server keinen Vorabrufvorgang für die Lesezeichensuche verwendet, muss das Programm die Transaktionsisolationsstufe für einen Teil der Abfrage auf wiederholtes Lesen erhöhen. Dies bedeutet, dass das, was einer SELECT-Anweisung auf einer auf das Lesen beschränkten Isolationsstufe ähnelt, tausende von Schlüsselsperren bewirken kann (sowohl bei einem gruppierten als auch bei einem nicht gruppierten Index), was eventuell dazu führt, dass die Schwellenwerte für die Sperrenausweitung überschritten werden. Dies ist von besonderer Bedeutung, wenn Sie feststellen, dass es sich bei der ausgeweiteten Sperre um eine freigegebene Tabellensperre handelt, die jedoch bei einer standardmäßigen auf das Lesen beschränkten Isolationsstufe nur selten vorkommt. Falls eine Lesezeichensuche mit Vorabrufvorgangsklausel die Sperrenausweitung verursacht, sollten Sie eventuell dem nicht gruppierten Index, der im Abfrageplan im logischen Operator "Index Seek" oder "Index Scan" unter dem logischen Operator "Bookmark Lookup" erscheint, weitere Spalten hinzufügen. Es ist eventuell möglich, einen abdeckenden Index zu erstellen (einen Index, der alle Spalten in einer Tabelle enthält, die in der Abfrage verwendet wurden), oder zumindest einen Index, der die Spalten abdeckt, die für die Verknüpfungskriterien oder die WHERE-Klausel verwendet wurden, wenn nicht alles in die Spaltenauswahlliste aufgenommen werden kann.

    Eine Nested Loop-Verknüpfung kann ebenfalls einen Vorabrufvorgang verwenden, was das gleiche blockierende Verhalten zur Folge hat.

    Weitere Informationen finden Sie im folgenden Artikel der Microsoft Knowledge Base:
    260652 PRB: schachtelte Verknüpfung Schleife die A verwendet " BOOKMARK-NACHSCHLAG. .WITH-PREFETCH " längere Sperren aufrechterhalten kann
  • Es kann nicht zu einer Sperrenausweitung kommen, wenn eine andere SPID gegenwärtig eine inkompatible Tabellensperre aufrecht erhält. Bei einer Sperrenausweitung erfolgt die Ausweitung immer auf eine Tabellensperre, niemals auf Seitensperren. Schlägt der Versuch einer Sperrenausweitung fehl, weil eine andere SPID eine inkompatible Tabellensperre aufrecht erhält, blockiert die Abfrage, die die Ausweitung versucht hat, außerdem nicht, während sie auf eine Tabellensperre wartet. Stattdessen erwirbt sie weiterhin Sperren auf ihrer ursprünglichen, feineren Ebene (Zeile, Schlüssel oder Seite) und unternimmt in regelmäßigen Abständen weitere Ausweitungsversuche. Daher besteht eine Möglichkeit zur Verhinderung einer Sperrenausweitung für eine bestimmte Tabelle darin, eine Sperre für eine andere Verbindung zu erwerben und aufrecht zu erhalten, die mit dem ausgeweiteten Sperrentyp nicht kompatibel ist. Eine IX-Sperre (beabsichtigte und exklusive Sperre) auf Tabellenebene sperrt zwar keine Zeilen oder Seiten, ist aber trotzdem nicht mit einer ausgeweiteten S- (shared/freigegeben) oder X- (exklusiven) Tabellensperre kompatibel. Nehmen Sie zum Beispiel an, dass Sie einen Stapelverarbeitungsauftrag ausführen müssen, der eine große Anzahl an Zeilen in der Tabelle mytable modifiziert und die Blockierung verursacht hat, die aufgrund der Sperrenausweitung aufgetreten ist. Wenn dieser Auftrag immer in weniger als einer Stunde ausgeführt wird, könnten Sie einen Transact-SQL-Auftrag erstellen, der den folgenden Code enthält, und den neuen Auftrag so einplanen, dass er wenige Minuten vor dem Stapelverarbeitungsauftrag gestartet wird:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    Dieser Auftrag verhängt eine IX-Sperre für die Tabelle mytable und erhält sie für eine Stunde aufrecht, wodurch während dieses Zeitraums eine Sperrenausweitung auf die Tabelle verhindert wird. Dieser Stapelverarbeitungsauftrag modifiziert keine Daten und blockiert andere Abfragen nicht (sofern nicht die andere Abfrage eine Tabellensperre mit der Anweisung "TABLOCK" erzwingt oder ein Administrator Seiten- oder Tabellensperren mit einer gespeicherten Prozedur des Typs sp_indexoption deaktiviert hat).
Außerdem können Sie die Sperrenausweitung deaktivieren, indem Sie das Ablaufverfolgungsflag 1211 aktivieren. Dieses Ablaufverfolgungsflag deaktiviert jedoch jegliche Sperrenausweitung für die gesamte Instanz von SQL Server. Die Sperrenausweitung ist in SQL Server äußerst nützlich, weil sie die Effizienz von Abfragen optimiert, deren Ausführung andernfalls durch die Notwendigkeit des Erwerbs und der Aufrechterhaltung tausender Sperren verlangsamt würde. Die Sperrenausweitung hilft auch, den Speicherbedarf für die Verfolgung von Sperren zu reduzieren. Der Speicherplatz, den SQL Server für Sperrstrukturen dynamisch zuweisen kann, ist nicht unbegrenzt. Wenn Sie also die Sperrenausweitung deaktivieren und der Sperrspeicher groß genug wird, können Versuche zur Zuweisung weiterer Sperren fehlschlagen und es kann der folgende Fehler auftreten:

Fehler: 1204, Schweregrad: 19, Status: 1
SQL Server kann derzeit keine LOCK-Ressource erhalten. Führen Sie die Anweisung erneut aus, wenn weniger Benutzer aktiv sind, oder bitten Sie den Systemadministrator, die Konfiguration der Sperren und des Arbeitsspeichers von SQL Server zu überprüfen.
Hinweis: Wenn ein Fehler des Typs "1204" auftritt, stoppt er die Verarbeitung der gegenwärtigen Anweisung und verursacht ein Rollback der aktiven Transaktion. Das Rollback selbst kann Benutzer blockieren oder dazu führen, dass eine lange Zeitspanne für die Wiederherstellung der Datenbank benötigt wird, wenn Sie den SQL Server-Dienst neu starten.

Die Verwendung eines Sperrhinweises wie ROWLOCK ändert nur den anfänglichen Sperrplan. Sperrhinweise verhindern eine Sperrenausweitung jedoch nicht.

Die anderen, an früherer Stelle in diesem Artikel beschriebenen Methoden, sind daher der Aktivierung des Ablaufverfolgungsflags vorzuziehen. Außerdem führen die anderen Methoden in der Regel zu einer besseren Leistung der Abfrage als es bei einer Deaktivierung der Sperrenausweitung für die gesamte Instanz der Fall ist. Microsoft empfiehlt die Aktivierung dieses Ablaufverfolgungsflags ausschließlich zur Abmilderung schwerwiegender Blockierungsprobleme, die durch eine Sperrenausweitung verursacht werden, während die anderen Optionen, wie die zuvor in diesem Artikel erläuterten, noch untersucht werden. Fügen Sie das Ablaufverfolgungsflag als Server-Startparameter hinzu, um es so zu aktivieren, dass es bei jedem Start von SQL Server aktiv ist.

Gehen Sie folgendermaßen vor, um einen Server-Startparameter hinzuzufügen: Klicken Sie im SQL Enterprise Manager mit der rechten Maustaste auf Eigenschaften, und klicken Sie dann auf der Registerkarte Allgemein auf Startparameter. Fügen Sie dann den folgenden Parameter hinzu (exakt wie folgt):
-T1211
Sie müssen den SQL Server-Dienst beenden und neu starten, damit der neue Startparameter wirksam wird. Wenn Sie im Query Analyzer die folgende Abfrage ausführen, wird das Ablaufverfolgungsflag sofort wirksam:
DBCC TRACEON (1211, -1)				
Wenn Sie den Startparameter -T1211 jedoch nicht hinzufügen, geht der Effekt des Befehls traceon verloren, wenn Sie den SQL Server-Dienst beenden und neu starten. Durch das Aktivieren des Ablaufverfolgungsflags werden zwar zukünftige Sperrenausweitungen verhindert, in der Vergangenheit in einer aktiven Transaktion vorgenommene Sperrenausweitungen werden jedoch nicht rückgängig gemacht.

Eigenschaften

Artikel-ID: 323630 - Geändert am: Donnerstag, 5. April 2007 - Version: 10.2
Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbinfo KB323630
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