Beheben von Blockierungsproblemen durch Sperrenausweitung in SQL Server

Artikel
04/03/2023

Zusammenfassung

Sperrenausweitung ist der Prozess, bei dem viele differenzierte Sperren (z. B. Zeilen- oder Seitensperren) in Tabellensperren konvertiert werden. Microsoft SQL Server bestimmt dynamisch, wann eine Sperrenausweitung durchzuführen ist. 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 Arbeitsspeicher, der für Sperren im gesamten System verwendet wird. In der Regel führt das Standardverhalten von SQL Server dazu, dass die Sperrenausweitung nur zu den Zeiten erfolgt, in denen die Leistung dadurch verbessert würde oder wenn Sie übermäßigen Systemsperrarbeitsspeicher auf ein angemesseneres Maß reduzieren müssen. Einige Anwendungs- oder Abfrageentwürfe können jedoch eine Sperrenausweitung zu einem Zeitpunkt auslösen, zu dem diese Aktion nicht erwünscht ist, und die eskalierte Tabellensperre könnte andere Benutzer blockieren. In diesem Artikel wird erläutert, wie Sie ermitteln können, ob die Sperrenausweitung eine Blockierung verursacht, und wie Sie mit unerwünschter Sperrenausweitung umgehen können.

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

Bestimmen, ob die Sperrausweitung eine Blockierung verursacht

Sperrenausweitung verursacht die meisten Blockierungsprobleme nicht. Starten Sie eine Sitzung mit erweiterten Ereignissen, die das lock_escalation Ereignis enthält, um zu bestimmen, ob die Sperrausweitung zu dem Zeitpunkt oder in der Nähe des Zeitpunkts auftritt, zu dem Blockierungsprobleme auftreten. Wenn keine lock_escalation Ereignisse angezeigt werden, erfolgt die Sperrausweitung auf Ihrem Server nicht, und die Informationen in diesem Artikel gelten nicht für Ihre Situation.

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

Weitere Informationen zum Identifizieren des Hauptblockers und der Sperrressource, die vom Hauptblocker gehalten wird und andere Serverprozess-IDs (SPIDs) blockiert, finden Sie unter INF: Grundlegendes und Beheben von problemen mit SQL Server Blockieren.

Wenn die Sperre, die andere Benutzer blockiert, etwas anderes ist als eine TAB-Sperre (Tabellenebene), die den Sperrmodus S (freigegeben) oder X (exklusiv) aufweist, ist die Sperrenausweitung nicht das Problem. Insbesondere wenn die TAB-Sperre eine Absichtssperre ist (z. B. ein Sperrmodus von IS, IU oder IX), wird dies nicht durch eine Sperrenausweitung verursacht. Wenn Ihre Blockierungsprobleme nicht durch eine Sperrenausweitung verursacht werden, lesen Sie die Schritte in INF: Grundlegendes und Beheben von SQL Server Blockierungsproblemen zur Problembehandlung.

Sperrenausweitung verhindern

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

Unterteilen Sie große Batchvorgänge in mehrere kleinere Vorgänge. Sie führen beispielsweise die folgende Abfrage aus, um mehr als 100.000 alte Datensätze aus einer Überwachungstabelle zu entfernen, und stellen dann fest, dass die Abfrage eine Sperrausweitung verursacht hat, die andere Benutzer blockiert hat:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Indem Sie diese Datensätze ein paar Hundert gleichzeitig entfernen, können Sie die Anzahl der Sperren, die pro Transaktion anfallen, erheblich reduzieren. Dadurch wird eine Sperrenausweitung 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;
```
Reduzieren Sie den Sperrspeicherbedarf der Abfrage, indem Sie die Abfrage so effizient wie möglich gestalten. Große Scans oder viele Lesezeichensuchvorgänge können die Wahrscheinlichkeit einer Sperrenausweitung erhöhen. Darüber hinaus erhöhen diese die Wahrscheinlichkeit von Deadlocks und beeinträchtigen die Parallelität und Leistung. Nachdem Sie ermittelt haben, dass die Abfrage, die eine Sperrenausweitung verursacht, können Sie nach Möglichkeiten suchen, neue Indizes zu erstellen oder einem vorhandenen Index Spalten hinzuzufügen, um Index- oder Tabellenscans zu entfernen und die Effizienz von Indexsuchen zu maximieren. Überprüfen Sie den Ausführungsplan, und erstellen Sie möglicherweise neue nicht gruppierte Indizes, um die Abfrageleistung zu verbessern. Weitere Informationen finden Sie unter SQL Server Indexarchitektur und Entwurfshandbuch.

Ein Ziel dieser Optimierung besteht darin, Indexsuchen so wenige Zeilen wie möglich zurückzugeben, um die Kosten für Lesezeichen-Lookups zu minimieren (maximiert die Selektivität des Index für die Abfrage). Wenn SQL Server schätzt, dass ein logischer Lesezeichensucheoperator viele Zeilen zurückgibt, kann eine PREFETCH -Klausel für die Lesezeichensuche verwendet werden. Wenn SQL Server für eine Lesezeichensuche verwendetPREFETCH, muss die Transaktionsisolationsstufe eines Teils der Abfrage auf "wiederholbare Lesevorgänge" für einen Teil der Abfrage erhöht werden. Dies bedeutet, dass das, was wie eine SELECT Anweisung auf einer Isolationsstufe mit "Read Committed" aussieht, viele Tausende von Schlüsselsperren (sowohl für den gruppierten Index als auch für einen nicht gruppierten Index) erhalten kann. Dies kann dazu führen, dass eine solche Abfrage die Schwellenwerte für die Sperrenausweitung überschreitet. Dies ist besonders wichtig, wenn Sie feststellen, dass es sich bei der eskalierten Sperre um eine freigegebene Tabellensperre handelt, obwohl diese häufig nicht auf der standardmäßigen Isolationsstufe "read-committed" zu sehen sind. Wenn eine Textmarkensuche WITH-Klausel PREFETCH die Eskalation verursacht, sollten Sie dem nicht gruppierten Index, der in index seek angezeigt wird, oder dem logischen Indexscan-Operator unterhalb des logischen Operators Lesezeichensuche im Abfrageplan Spalten hinzufügen. Es kann möglich sein, einen abdeckenden Index (einen Index, der alle Spalten in einer Tabelle enthält, die in der Abfrage verwendet wurden) oder zumindest einen Index zu erstellen, der die Spalten abdeckt, die für Joinkriterien verwendet wurden, oder in der WHERE-Klausel, wenn es nicht praktikabel ist, alles in die Liste "Spalte auswählen" aufzunehmen.

Ein Join einer geschachtelten Schleife kann auch verwenden PREFETCH, was das gleiche Sperrverhalten verursacht.
Die Sperrenausweitung kann nicht erfolgen, wenn eine andere SPID derzeit eine inkompatible Tabellensperre hält. Sperrenausweitung eskaliert immer zu einer Tabellensperre und nie zu einer Seitensperre. Wenn ein Sperrenausweitungsversuch fehlschlägt, weil eine andere SPID eine inkompatible TAB-Sperre enthält, wird die Abfrage, die die Eskalation versucht hat, nicht blockiert, während sie auf eine TAB-Sperre wartet. Stattdessen werden sperren weiterhin auf der ursprünglichen, präziseren Ebene (Zeile, Schlüssel oder Seite) abgerufen und in regelmäßigen Abständen zusätzliche Eskalationsversuche unternommen. Daher besteht eine Methode zum Verhindern einer Sperrenausweitung für eine bestimmte Tabelle darin, eine Sperre für eine andere Verbindung abzurufen und zu halten, die nicht mit dem eskalierten Sperrtyp kompatibel ist. Eine IX-Sperre (intent exclusive) auf Tabellenebene sperrt keine Zeilen oder Seiten, ist aber immer noch nicht mit einer eskalierten S-Sperre (freigegeben) oder X (exklusiv) TAB-Sperre kompatibel. Angenommen, Sie müssen einen Batchauftrag ausführen, der viele Zeilen in der tabelle mytable ändert und aufgrund einer Sperrenausweitung eine Blockierung verursacht hat. Wenn dieser Auftrag immer in weniger als einer Stunde abgeschlossen ist, können Sie einen Transact-SQL-Auftrag erstellen, der den folgenden Code enthält, und den Start des neuen Auftrags einige Minuten vor der Startzeit des Batchauftrags planen:
```
BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
Diese Abfrage ruft eine IX-Sperre für mytable ab und hält sie für eine Stunde. Dadurch wird eine Sperrenausweitung für die Tabelle während dieser Zeit verhindert. Dieser Batch ändert keine Daten oder blockiert keine anderen Abfragen (es sei denn, die andere Abfrage erzwingt eine Tabellensperre mithilfe des TABLOCK-Hinweises oder wenn ein Administrator Seiten- oder Zeilensperren mithilfe von ALTER INDEX deaktiviert hat).
Beseitigen Sie die Sperrenausweitung, die durch mangelnde SARGability verursacht wird. Dabei handelt es sich um einen Begriff für relationale Datenbanken, mit dem beschrieben wird, ob eine Abfrage Indizes für Prädikate und Joinspalten verwenden kann. Weitere Informationen zur SARGability finden Sie unter Überlegungen zur Abfrage im Entwurfshandbuch. Beispielsweise kann eine ziemlich einfache Abfrage, die anscheinend nicht viele Zeilen oder vielleicht eine einzelne Zeile anfordert, immer noch eine ganze Tabelle/einen index durchsucht. Dies kann auftreten, wenn auf der linken Seite einer WHERE-Klausel eine Funktion oder Berechnung vorhanden ist. Beispiele, bei denen sargierbarkeit fehlt, sind implizite oder explizite Datentypkonvertierungen, die ISNULL()-Systemfunktion, eine benutzerdefinierte Funktion, bei der die Spalte als Parameter übergeben wird, 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 zum Abrufen einer großen Anzahl von Sperren. Unter solchen Umständen können SQL Server den Schwellenwert für die Eskalation der Sperranzahl erreichen. Die Lösung besteht darin, die Verwendung von Funktionen für Spalten in der WHERE-Klausel zu vermeiden und SARGable-Bedingungen sicherzustellen.

Sperrenausweitung deaktivieren

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

Tabellenebene: Sie können die Sperrenausweitung auf Tabellenebene deaktivieren. Weitere Informationen finden Sie unter ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Um zu bestimmen, welche Tabelle als Ziel verwendet werden soll, untersuchen Sie die T-SQL-Abfragen. 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 Sperrenausweitung deaktivieren, indem Sie entweder die Ablaufverfolgungsflags 1211 oder 1224 oder beides für die instance aktivieren. Diese Ablaufverfolgungsflags deaktivieren jedoch alle Sperrenausweitungen global im instance von SQL Server. Sperrenausweitung dient einem nützlichen Zweck in SQL Server, indem die Effizienz von Abfragen maximiert wird, die andernfalls durch den Mehraufwand für das Abrufen und Freigeben von mehreren Tausend Sperren verlangsamt werden. Die Sperrenausweitung trägt auch dazu bei, den erforderlichen Arbeitsspeicher zu minimieren, um Sperren nachzuverfolgen. Der Speicher, den SQL Server dynamisch für Sperrenstrukturen zuordnen können, ist endlich. Wenn Sie die Sperrenausweitung deaktivieren und der Sperrspeicher ausreichend groß wird, kann daher jeder Versuch, zusätzliche Sperren für eine Abfrage zuzuweisen, fehlschlagen und den folgenden Fehlereintrag generieren:

Fehler: 1204, Schweregrad: 19, Status: 1
Die 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 konfiguration der SQL Server Sperre und des Arbeitsspeichers zu überprüfen.

Hinweis

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

Sie können diese Ablaufverfolgungsflags (-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) Abfrage oder DBCC TRACEON (1224, -1) ausführen, wird das Ablaufverfolgungsflag sofort wirksam.
Wenn Sie -T1211 oder -T1224 jedoch nicht als Startparameter hinzufügen, geht die Wirkung eines DBCC TRACEON Befehls verloren, wenn der SQL Server Dienst neu gestartet wird. Wenn Sie das Ablaufverfolgungsflag aktivieren, werden zukünftige Sperrenausweitungen verhindert, aber es werden keine Sperrenausweitungen rückgängig gemacht, die bereits in einer aktiven Transaktion aufgetreten sind.

Wenn Sie einen Sperrhinweis wie ROWLOCK verwenden, ändert dies nur den ursprünglichen Sperrplan. Sperrhinweise verhindern keine Sperrenausweitung.

Sperrenausweitungsschwellenwerte

Die Sperrausweitung kann unter einer der folgenden Bedingungen erfolgen:

Arbeitsspeicherschwellenwert erreicht : Ein Speicherschwellenwert von 40 Prozent des Sperrspeichers wird erreicht. Wenn der Sperrspeicher 24 Prozent des Pufferpools überschreitet, kann eine Sperrenausweitung ausgelöst werden. Der Sperrspeicher ist auf 60 Prozent des sichtbaren Pufferpools beschränkt. Der Schwellenwert für die Sperrenausweitung ist auf 40 Prozent des Sperrspeichers festgelegt. Dies sind 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 Sperrenausweitung deaktiviert ist), schlagen alle Versuche, zusätzliche Sperren zuzuweisen, fehl, und 1204 Fehler werden generiert.
Ein Sperrschwellenwert wird erreicht : Nachdem der Speicherschwellenwert überprüft wurde, wird die Anzahl der sperren bewertet, die für die aktuelle Tabelle oder den aktuellen Index abgerufen wurden. Wenn die Anzahl 5.000 überschreitet, wird eine Sperrenausweitung 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 untersuchen Sie den EventSubClass Wert, wobei "0 - LOCK_THRESHOLD" angibt, dass die Anweisung den Sperrschwellenwert überschritten hat, und "1 – MEMORY_THRESHOLD", dass die Anweisung den Speicherschwellenwert überschritten hat. Untersuchen Sie außerdem die IntegerData Spalten und IntegerData2 .

Empfehlungen

Die methoden, die im Abschnitt Sperrenausweitung verhindern erläutert werden, sind bessere Optionen als das Deaktivieren der Eskalation auf Tabellen- oder instance Ebene. Darüber hinaus erzielen die präventiven Methoden im Allgemeinen eine bessere Leistung für die Abfrage als das Deaktivieren der Sperrenausweitung. Microsoft empfiehlt, dieses Ablaufverfolgungsflag nur zu aktivieren, um schwerwiegende Blockierungen zu vermeiden, die durch eine Sperrenausweitung verursacht werden, während andere Optionen, wie die in diesem Artikel beschriebenen, untersucht werden.

Freigeben über