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

Artikel
12/14/2024

Zusammenfassung

Die Sperreskalation ist der Prozess der Konvertierung vieler feinkörniger Sperren (z. B. Zeilen- oder Seitensperren) in Tabellensperren. Microsoft SQL Server bestimmt dynamisch, wann die Sperreskalation erfolgen soll. Wenn diese Entscheidung getroffen wird, berücksichtigt SQL Server die Anzahl der Sperren, die für eine bestimmte Überprüfung gehalten werden, die Anzahl der Sperren, die von der gesamten Transaktion gehalten werden, und den Speicher, der für Sperren im System als Ganzes verwendet wird. In der Regel führt das Standardverhalten von SQL Server dazu, dass die Sperreskalation nur zu zeiten auftritt, in denen sie die Leistung verbessern würde oder wenn Sie übermäßigen Systemsperrspeicher auf ein angemesseneres Niveau reduzieren müssen. Einige Anwendungs- oder Abfragedesigns lösen jedoch möglicherweise eine Sperreskalation zu einem Zeitpunkt aus, wenn diese Aktion nicht wünschenswert ist, und die eskalierte Tabellensperre blockiert möglicherweise andere Benutzer. In diesem Artikel wird erläutert, wie Sie ermitteln können, ob die Sperreskalation blockiert wird und wie Sie mit unerwünschter Sperreskalation umgehen.

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 323630

Bestimmen, ob die Sperreskalation das Blockieren verursacht

Die Sperreskalation verursacht die meisten Blockierungsprobleme nicht. Um festzustellen, ob die Sperreskalation zu oder in der Nähe des Zeitpunkts auftritt, zu dem Probleme beim Blockieren auftreten, starten Sie eine Erweiterte Ereignissitzung, die das lock_escalation Ereignis enthält. Wenn keine Ereignisse angezeigt lock_escalation werden, tritt die Sperreskalation nicht auf Ihrem Server auf, und die Informationen in diesem Artikel gelten nicht für Ihre Situation.

Wenn die Sperreskalation auftritt, überprüfen Sie, ob die eskalierte Tabellensperre andere Benutzer blockiert.

Weitere Informationen zum Identifizieren des Kopfblockers und der Sperrressource, die vom Headblocker gehalten wird, und das andere Serverprozess-IDs (SPIDs) blockiert, finden Sie unter INF: Grundlegendes und Beheben von PROBLEMEN beim Blockieren von SQL Server.

Wenn die Sperre, die andere Benutzer blockiert, nichts anderes als eine TAB-Sperre (Tabellenebene) mit einem Sperrmodus von S (freigegeben) oder X (exklusiv) ist, ist die Sperreskalation nicht das Problem. Wenn die TAB-Sperre eine Absichtssperre ist (z. B. ein Sperrmodus von IS, IU oder IX), wird dies nicht durch die Sperreskalation verursacht. Wenn Ihre Blockierungsprobleme nicht durch die Sperreskalation verursacht werden, lesen Sie in der INF: Grundlegendes und Beheben von SQL Server-Blockierungsproblemen bei der Problembehandlung.

Verhindern der Sperreskalation

Die einfachste und sicherste Methode, um die Sperreskalation zu verhindern, besteht darin, Transaktionen kurz zu halten und den Sperrbedarf von teuren Abfragen zu verringern, damit die Schwellenwerte für die Sperreskalation nicht überschritten werden. Es gibt mehrere Methoden, um dieses Ziel zu erreichen, einschließlich der folgenden Strategien:

Teilen Sie eine große Anzahl von Vorgängen in mehrere kleinere Vorgänge auf. Sie führen beispielsweise die folgende Abfrage aus, um 100.000 alte Datensätze aus einer Überwachungstabelle zu entfernen, und dann bestimmen Sie, dass die Abfrage eine Sperreskalation verursacht hat, die andere Benutzer blockiert hat:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Indem Sie diese Datensätze jeweils ein paar Hundert entfernen, können Sie die Anzahl der Sperren, die sich pro Transaktion ansammeln, erheblich reduzieren. Dadurch wird die Sperreskalation verhindert. Sie führen beispielsweise die folgende Abfrage aus:
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
Verringern Sie den Sperrbedarf der Abfrage, indem Sie die Abfrage so effizient wie möglich gestalten. Große Scans oder viele Lesezeichen-Nachschlagevorgänge können die Wahrscheinlichkeit einer Sperreskalation erhöhen. Darüber hinaus erhöhen diese die Wahrscheinlichkeit von Deadlocks und wirken sich negativ auf Parallelität und Leistung aus. Nachdem Sie ermittelt haben, dass die Abfrage, die eine Sperreskalation verursacht, nach Möglichkeiten zum Erstellen neuer Indizes oder zum Hinzufügen von Spalten zu einem vorhandenen Index suchen, um Index- oder Tabellenscans zu entfernen und die Effizienz der Indexsuche zu maximieren. Überprüfen Sie den Ausführungsplan, und erstellen Sie potenziell neue nicht gruppierte Indizes, um die Abfrageleistung zu verbessern. Weitere Informationen finden Sie unter Leitfaden zur Architektur und zum Entwurf von SQL Server-Indizes.

Ein Ziel dieser Optimierung ist es, die Indexsuche so wenige Zeilen wie möglich zurückzugeben, um die Kosten für Lesezeichensuche zu minimieren (maximieren die Selektivität des Indexes für die Abfrage). Wenn SQL Server schätzt, dass ein logischer Textmarkenoperator viele Zeilen zurückgibt, kann eine Klausel zum Nachschlagen der Textmarke verwendet PREFETCH werden. Wenn SQL Server für eine Lesezeichensuche verwendet PREFETCH wird, muss die Transaktionsisolationsstufe eines Teils der Abfrage auf "wiederholbares Lesen" für einen Teil der Abfrage erhöht werden. Dies bedeutet, dass eine Anweisung auf einer Isolationsstufe mit Lese-Commit viele Tausende von Schlüsselsperren (sowohl für den gruppierten Index als auch für einen nicht gruppierten Index) erhalten kann SELECT . Dies kann dazu führen, dass eine solche Abfrage die Schwellenwerte für die Sperreskalation überschreitet. Dies ist besonders wichtig, wenn Sie feststellen, dass es sich bei der eskalierten Sperre um eine freigegebene Tabellensperre handelt, obwohl diese nicht häufig auf der Standardisolationsstufe "lesesichert" zu sehen sind. Wenn eine Bookmark Lookup WITH-Klausel PREFETCH die Eskalation verursacht, erwägen Sie das Hinzufügen von Spalten zum nicht gruppierten Index, der in der Indexsuche angezeigt wird, oder den logischen Operator "Indexscan" unterhalb des logischen Textmarken-Suchoperators im Abfrageplan. Es kann möglich sein, einen abgedeckten Index zu erstellen (ein 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 Verknüpfungskriterien oder in der WHERE-Klausel verwendet wurden, wenn es unpraktisch ist, alles in die Liste "Auswahlspalte" einzuschließen.

Eine geschachtelte Schleifenverknung kann auch verwendet PREFETCHwerden, und dies bewirkt das gleiche Sperrverhalten.
Die Sperreskalation kann nicht auftreten, wenn derzeit eine andere SPID eine inkompatible Tabellensperre enthält. Die Eskalation der Sperre wird immer zu einer Tabellensperre und nie zu einer Seitensperre eskaliert. Wenn ein Eskalationsversuch beim Sperren fehlschlägt, da ein anderer SPID eine inkompatible TAB-Sperre enthält, wird die Abfrage, die versucht hat, die Eskalation nicht blockiert, während sie auf eine TAB-Sperre warten. Stattdessen werden Sperren weiterhin auf der ursprünglichen, präziseren Ebene (Zeile, Schlüssel oder Seite) abgerufen, und es erfolgen zusätzliche Ausweitungsversuche in regelmäßigen Abständen. Daher besteht eine Methode zum Verhindern der Sperrenausweitung für eine bestimmte Tabelle im Abrufen und Halten einer Sperre für eine andere Verbindung, die mit dem ausgweiteten Sperrentyp nicht kompatibel ist. Eine IX-Sperre (Intent Exclusive) auf Tabellenebene sperrt keine Zeilen oder Seiten, ist aber immer noch nicht mit einer ausgeweiteten S- (freigegeben) oder X-TAB-Sperre (exklusiv) kompatibel. Gehen Sie beispielsweise davon aus, dass Sie einen Batchauftrag ausführen müssen, der viele Zeilen in der tabelle "mytable" ändert und die aufgrund der Sperreskalation blockiert wurde. Wenn dieser Auftrag immer in weniger als einer Stunde abgeschlossen ist, erstellen Sie möglicherweise einen Transact-SQL-Auftrag, der den folgenden Code enthält, und planen Sie, dass der neue Auftrag mehrere Minuten vor der Startzeit des Batchauftrags beginnt:
```
BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
Diese Abfrage erwirbt und hält eine IX-Sperre für eine Stunde lang auf meiner Tabelle. Dadurch wird die Sperreskalation auf der Tabelle während dieser Zeit verhindert. Dieser Batch ändert keine Daten oder blockiert andere Abfragen (es sei denn, die andere Abfrage erzwingt eine Tabellensperre mithilfe des TABLOCK-Hinweises oder wenn ein Administrator Seiten- oder Zeilensperren mit ALTER INDEX deaktiviert hat).
Beseitigen Sie die Sperreskalation aufgrund fehlender SARGability, einem relationalen Datenbankbegriff, der verwendet wird, um zu beschreiben, ob eine Abfrage Indizes für Prädikate und Verknüpfungsspalten verwenden kann. Weitere Informationen zur SARGability finden Sie in den Abfrageüberlegungen im Innenentwurfshandbuch. Beispielsweise kann eine ziemlich einfache Abfrage, die nicht viele Zeilen anfordert – oder vielleicht eine einzelne Zeile – dennoch eine gesamte Tabelle/einen Index scannen. Dies kann auftreten, wenn auf der linken Seite einer WHERE-Klausel eine Funktion oder Berechnung vorhanden ist. Solche Beispiele, die keine SARGability aufweisen, umfassen implizite oder explizite Datentypkonvertierungen, die ISNULL()-Systemfunktion, eine benutzerdefinierte Funktion mit der als Parameter übergebenen Spalte oder eine Berechnung für die Spalte, z WHERE CONVERT(INT, column1) = @a . B. oder WHERE Column1*Column2 = 5. In solchen Fällen kann die Abfrage den vorhandenen Index nicht suchen, auch wenn sie die entsprechenden Spalten enthält, da alle Spaltenwerte zuerst abgerufen und an die Funktion übergeben werden müssen. Dies führt zu einer Überprüfung der gesamten Tabelle oder des gesamten Indexes und führt zum Erwerb einer großen Anzahl von Sperren. Unter solchen Umständen kann SQL Server den Eskalationsschwellenwert für die Sperranzahl erreichen. Die Lösung besteht darin, die Verwendung von Funktionen für Spalten in der WHERE-Klausel zu vermeiden, um SARGable Bedingungen sicherzustellen.

Deaktivieren der Sperreskalation

Obwohl es möglich ist, die Sperreskalation in SQL Server zu deaktivieren, wird dies nicht empfohlen. Verwenden Sie stattdessen die Präventionsstrategien, die im Abschnitt "Verhindern der Sperreskalation " beschrieben werden.

Tabellenebene: Sie können die Sperreskalation auf Tabellenebene deaktivieren. Siehe ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Überprüfen Sie die T-SQL-Abfragen, um zu bestimmen, welche Tabelle als Ziel verwendet werden soll. Wenn dies nicht möglich ist, verwenden Sie erweiterte Ereignisse, aktivieren Sie das lock_escalation-Ereignis , und überprüfen Sie die object_id Spalte. Alternativ können Sie das Lock:Escalation-Ereignis verwenden und die ObjectID2 Spalte mithilfe von SQL Profiler untersuchen.
Instanzebene: Sie können die Sperreskalation deaktivieren, indem Sie entweder die Ablaufverfolgungskennzeichnungen 1211 oder 1224 oder beides für die Instanz aktivieren. Diese Ablaufverfolgungskennzeichnungen deaktivieren jedoch alle Sperreskalation global in der Instanz von SQL Server. Die Sperreskalation dient einem nützlichen Zweck in SQL Server, indem die Effizienz von Abfragen maximiert wird, die andernfalls durch den Mehraufwand beim Abrufen und Freigeben von mehreren Tausend Sperren verlangsamt werden. Sperrenausweitung trägt auch dazu bei, den erforderlichen Arbeitsspeicher zu minimieren, um Sperren nachzuverfolgen. Der Speicher, den SQL Server dynamisch für Sperrstrukturen zuordnen kann, ist endlich. Wenn Sie daher die Sperreskalation deaktivieren und der Sperrspeicher groß genug wird, schlägt jeder Versuch, zusätzliche Sperren für eine Abfrage zuzuweisen, fehl und generiert den folgenden Fehlereintrag:

Fehler: 1204, Schweregrad: 19, Status: 1
Sql Server kann derzeit keine LOCK-Ressource abrufen. Führen Sie Ihre Anweisung erneut aus, wenn weniger aktive Benutzer vorhanden sind, oder bitten Sie den Systemadministrator, die SQL Server-Sperr- und Speicherkonfiguration zu überprüfen.

Notiz

Wenn ein 1204-Fehler auftritt, wird die Verarbeitung der aktuellen Anweisung beendet und ein Rollback der aktiven Transaktion verursacht. Das Rollback selbst kann Benutzer blockieren oder eine lange Datenbankwiederherstellungszeit verursachen, wenn Sie den SQL Server-Dienst neu starten.

Sie können diese Ablaufverfolgungskennzeichnungen (-T1211 oder -T1224) mithilfe von SQL Server-Konfigurations-Manager hinzufügen. Sie müssen den SQL Server-Dienst neu starten, damit ein neuer Startparameter wirksam wird. Wenn Sie die DBCC TRACEON (1211, -1) Ablaufverfolgung DBCC TRACEON (1224, -1) oder Abfrage ausführen, wird das Ablaufverfolgungskennzeichnung sofort wirksam.
Wenn Sie jedoch den -T1211 oder -T1224 nicht als Startparameter hinzufügen, geht der Effekt eines DBCC TRACEON Befehls verloren, wenn der SQL Server-Dienst neu gestartet wird. Durch das Aktivieren des Ablaufverfolgungskennzeichnungs werden zukünftige Sperreskalationen verhindert, aber es werden keine Sperreskalationen rückgängig gemacht, die bereits in einer aktiven Transaktion aufgetreten sind.

Wenn Sie einen Sperrhinweis wie ROWLOCK verwenden, ändert dies nur den anfänglichen Sperrplan. Sperrhinweise verhindern keine Sperreskalation.

Schwellenwerte für die Sperrenausweitung

Die Sperreskalation kann unter einer der folgenden Bedingungen auftreten:

Der Speicherschwellenwert ist erreicht : Ein Speicherschwellenwert von 40 Prozent des Sperrspeichers wird erreicht. Wenn der Sperrspeicher 24 Prozent des Pufferpools überschreitet, kann eine Sperreskalation ausgelöst werden. Der Sperrspeicher ist auf 60 Prozent des sichtbaren Pufferpools beschränkt. Der Schwellenwert für die Sperreskalation wird auf 40 Prozent des Sperrspeichers festgelegt. Dies beträgt 40 Prozent von 60 Prozent des Pufferpools oder 24 Prozent. Wenn der Sperrspeicher den Grenzwert von 60 Prozent überschreitet (dies ist viel wahrscheinlicher, wenn die Sperreskalation deaktiviert ist), schlagen alle Versuche zur Zuweisung zusätzlicher Sperren fehl, und 1204 Fehler werden generiert.
Ein Sperrschwellenwert wird erreicht : Nachdem der Speicherschwellenwert überprüft wurde, wird die Anzahl der sperren, die in der aktuellen Tabelle oder dem aktuellen Index abgerufen wurden, bewertet. Wenn die Zahl 5.000 überschreitet, wird eine Sperreskalation ausgelöst.

Um zu verstehen, welcher Schwellenwert erreicht wurde, verwenden Sie erweiterte Ereignisse, aktivieren Sie das lock_escalation-Ereignis , und untersuchen Sie die spalten escalated_lock_count und escalation_cause . Verwenden Sie alternativ das Lock:Escalation-Ereignis, und überprüfen Sie den EventSubClass Wert, wobei "0 - LOCK_THRESHOLD" angibt, dass die Anweisung den Sperrschwellenwert überschritten hat, und "1 - MEMORY_THRESHOLD" gibt an, dass die Anweisung den Speicherschwellenwert überschritten hat. Überprüfen Sie außerdem die und IntegerData2 die IntegerData Spalten.

Empfehlungen

Die methoden, die im Abschnitt "Verhindern der Sperreskalation " erläutert werden, sind bessere Optionen als das Deaktivieren der Eskalation auf Tabellen- oder Instanzebene. Darüber hinaus erzeugen die präventiven Methoden in der Regel eine bessere Leistung für die Abfrage als die Deaktivierung der Sperreskalation. Microsoft empfiehlt, dieses Ablaufverfolgungsflagge nur zu aktivieren, um schwere Blockierungen zu vermeiden, die durch die Sperreskalation verursacht werden, während andere Optionen, wie die in diesem Artikel erläuterten, untersucht werden.

Freigeben über