Beheben des Konflikts beim Einfügen der letzten Seite PAGELATCH_EX in SQL Server
Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 4460004
In diesem Artikel wird beschrieben, wie Sie konflikte beim Einfügen PAGELATCH_EX der letzten Seite in SQL Server beheben.
Symptome
Betrachten Sie dazu die folgenden Szenarien:
Sie verfügen über eine Spalte, die sequenzielle Werte enthält, z. B. eine Identity-Spalte oder eine DateTime-Spalte, die über die Getdate()- Funktion eingefügt wird.
Sie verfügen über einen gruppierten Index, der die sequenzielle Spalte als führende Spalte enthält.
Hinweis
Das häufigste Szenario ist ein gruppierter Primärschlüssel in einer Identitätsspalte. Dieses Problem kann seltener bei nicht gruppierten Indizes beobachtet werden.
Ihre Anwendung führt häufige INSERT- oder UPDATE-Vorgänge für die Tabelle aus.
Sie haben viele CPUs auf dem System. In der Regel verfügt der Server über mindestens 16 CPUs. Diese Hardwarekonfiguration ermöglicht es mehreren Sitzungen, die INSERT-Vorgänge für dieselbe Tabelle gleichzeitig auszuführen.
In diesem Fall kann die Leistung Ihrer Anwendung beeinträchtigt werden. Wenn Sie Wartetypen in sys.dm_exec_requestsuntersuchen, beobachten Sie Wartezeiten für den PAGELATCH_EX Wartetyp und viele Sitzungen, die auf diesen Wartetyp warten.
Ein weiteres Problem tritt auf, wenn Sie die folgende Diagnoseabfrage auf Ihrem System ausführen:
Wählen Sie session_id, wait_type, wait_time, wait_resource aus, wobei sys.dm_exec_requests session_id > 50 und wait_type = "pagelatch_ex"
In diesem Fall erhalten Sie möglicherweise Ergebnisse, die den folgenden ähneln.
| Session_id | wait_type | wait_time | wait_resource |
|---|---|---|---|
| 60 | PAGELATCH_EX | 100 | 5:1:4144 |
| 75 | PAGELATCH_EX | 123 | 5:1:4144 |
| 79 | PAGELATCH_EX | 401 | 5:1:4144 |
| 80 | PAGELATCH_EX | 253 | 5:1:4144 |
| 81 | PAGELATCH_EX | 312 | 5:1:4144 |
| 82 | PAGELATCH_EX | 355 | 5:1:4144 |
| 84 | PAGELATCH_EX | 312 | 5:1:4144 |
| 85 | PAGELATCH_EX | 338 | 5:1:4144 |
| 87 | PAGELATCH_EX | 405 | 5:1:4144 |
| 88 | PAGELATCH_EX | 111 | 5:1:4144 |
| 90 | PAGELATCH_EX | 38 | 5:1:4144 |
| 92 | PAGELATCH_EX | 115 | 5:1:4144 |
| 94 | PAGELATCH_EX | 49 | 5:1:4144 |
| 101 | PAGELATCH_EX | 301 | 5:1:4144 |
| 102 | PAGELATCH_EX | 45 | 5:1:4144 |
| 103 | PAGELATCH_EX | 515 | 5:1:4144 |
| 105 | PAGELATCH_EX | 39 | 5:1:4144 |
Sie stellen fest, dass mehrere Sitzungen auf dieselbe Ressource warten, die dem folgenden Muster ähnelt:
database_id = 5, file_id = 1, Datenbank page_id = 4144
Hinweis
Der database_id sollte eine Benutzerdatenbank sein (die ID-Nummer ist größer oder gleich 5). Wenn der database_id 2 ist, tritt stattdessen möglicherweise das Problem auf, das unter Dateien, Ablaufverfolgungsflags und Updates für TEMPDB erläutert wird.
Ursache
PAGELATCH (Latch auf einer Daten- oder Indexseite) ist ein Threadsynchronisierungsmechanismus. Es wird verwendet, um den kurzfristigen physischen Zugriff auf Datenbankseiten zu synchronisieren, die sich im Puffercache befinden.
PAGELATCH unterscheidet sich von einem PAGEIOLATCH. Letzteres wird verwendet, um den physischen Zugriff auf Seiten zu synchronisieren, wenn diese von einem Datenträger gelesen oder auf den Datenträger geschrieben werden.
Seitenlatches sind in jedem System üblich, da sie physischen Seitenschutz gewährleisten. Ein gruppierter Index sortiert die Daten nach der führenden Schlüsselspalte. Aus diesem Grund werden beim Erstellen des Indexes für eine sequenzielle Spalte alle neuen Dateneinfügungen auf derselben Seite am Ende des Indexes ausgeführt, bis diese Seite ausgefüllt ist. Bei hoher Auslastung können die gleichzeitigen INSERT-Vorgänge jedoch zu Konflikten auf der letzten Seite der B-Struktur führen. Dieser Konflikt kann bei gruppierten und nicht gruppierten Indizes auftreten. Der Grund dafür ist, dass nicht gruppierte Indizes die Seiten auf Blattebene nach dem führenden Schlüssel anordnen. Dieses Problem wird auch als Konflikt beim Einfügen der letzten Seite bezeichnet.
Weitere Informationen finden Sie unter Diagnose und Auflösung von Latchkonflikten auf SQL Server.
Lösung
Sie können eine der beiden folgenden Optionen auswählen, um das Problem zu beheben.
Option 1: Direktes Ausführen der Schritte in einem Notebook über Azure Data Studio
Hinweis
Bevor Sie versuchen, dieses Notebook zu öffnen, stellen Sie sicher, dass Azure Data Studio auf Ihrem lokalen Computer installiert ist. Informationen zur Installation finden Sie unter Installieren von Azure Data Studio.
Option 2: Führen Sie die Schritte manuell aus.
Um diesen Konflikt zu lösen, besteht die allgemeine Strategie darin, zu verhindern, dass alle gleichzeitigen INSERT-Vorgänge auf dieselbe Datenbankseite zugreifen. Sorgen Sie stattdessen dafür, dass jeder INSERT-Vorgang auf eine andere Seite zugreift, und erhöhen Sie die Parallelität. Daher erreicht jede der folgenden Methoden, die die Daten nach einer anderen Spalte als der sequenziellen Spalte organisieren, dieses Ziel.
1. Bestätigen Sie den Konflikt auf PAGELATCH_EX und identifizieren Sie die Konfliktressource.
Dieses T-SQL-Skript hilft Ihnen, zu ermitteln, ob auf dem System Wartezeiten mit mehreren Sitzungen (5 oder mehr) mit erheblicher Wartezeit (10 ms oder mehr) vorhanden sind PAGELATCH_EX . Außerdem können Sie mithilfe von sys.dm_exec_requests und DBCC PAGE oder sys.fn_PageResCracker und sys.dm_db_page_info (nur SQL Server 2019) ermitteln, auf welchem Objekt und in welchem Index der Konflikt besteht.
SET NOCOUNT ON
DECLARE @dbname SYSNAME, @dbid INT, @objectid INT, @indexid INT, @indexname SYSNAME, @sql VARCHAR(8000), @manul_identification VARCHAR(8000)
IF (CONVERT(INT, SERVERPROPERTY('ProductMajorVersion')) >= 15)
BEGIN
DROP TABLE IF EXISTS #PageLatchEXContention
SELECT DB_NAME(page_info.database_id) DbName, r.db_id DbId, page_info.[object_id] ObjectId, page_info.index_id IndexId
INTO #PageLatchEXContention
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
WHERE er.wait_type = 'PAGELATCH_EX' AND page_info.database_id not in (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
GROUP BY DB_NAME(page_info.database_id), r.db_id, page_info.[object_id], page_info.index_id
HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10
SELECT * FROM #PageLatchEXContention
IF EXISTS (SELECT 1 FROM #PageLatchEXContention)
BEGIN
DECLARE optimize_for_seq_key_cursor CURSOR FOR
SELECT DbName, DbId, ObjectId, IndexId FROM #PageLatchEXContention
OPEN optimize_for_seq_key_cursor
FETCH NEXT FROM optimize_for_seq_key_cursor into @dbname, @dbid, @objectid , @indexid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Consider using below statement to enable OPTIMIZE_FOR_SEQUENTIAL_KEY for the indexes in the "' + @dbname + '" database' AS Recommendation
SELECT @sql = 'select ''use ' + @dbname + '; ALTER INDEX '' + i.name + '' ON ' + OBJECT_NAME(@objectid, @dbid) + ' SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON )'' AS Corrective_Action from #PageLatchEXContention pl JOIN ' + @dbname+'.sys.indexes i ON pl.ObjectID = i.object_id WHERE object_id = ' + CONVERT(VARCHAR, @objectid) + ' AND index_id = ' + CONVERT(VARCHAR, @indexid)
EXECUTE (@sql)
FETCH NEXT FROM optimize_for_seq_key_cursor INTO @dbname, @dbid, @objectid , @indexid
END
CLOSE optimize_for_seq_key_cursor
DEALLOCATE optimize_for_seq_key_cursor
END
ELSE
SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#PageLatchEXContentionLegacy') IS NOT NULL
DROP TABLE #PageLatchEXContentionLegacy
SELECT 'dbcc traceon (3604); dbcc page(' + replace(wait_resource,':',',') + ',3); dbcc traceoff (3604)' TSQL_Command
INTO #PageLatchEXContentionLegacy
FROM sys.dm_exec_requests er
WHERE er.wait_type = 'PAGELATCH_EX' AND er.database_id NOT IN (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
GROUP BY wait_resource
HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10
SELECT * FROM #PageLatchEXContentionLegacy
IF EXISTS(SELECT 1 FROM #PageLatchEXContentionLegacy)
BEGIN
SELECT 'On SQL Server 2017 or lower versions, you can manually identify the object where contention is occurring using DBCC PAGE locate the m_objId = ??. Then SELECT OBJECT_NAME(object_id_identified) and locate indexes with sequential values in this object' AS Recommendation
DECLARE get_command CURSOR FOR
SELECT TSQL_Command from #PageLatchEXContentionLegacy
OPEN get_command
FETCH NEXT FROM get_command into @sql
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql AS Step1_Run_This_Command_To_Find_Object
SELECT 'select OBJECT_NAME(object_id_identified)' AS Step2_Find_Object_Name_From_ID
FETCH NEXT FROM get_command INTO @sql
END
CLOSE get_command
DEALLOCATE get_command
SELECT 'Follow https://learn.microsoft.com/troubleshoot/sql/performance/resolve-pagelatch-ex-contention for resolution recommendations that fits your environment best' Step3_Apply_KB_article
END
ELSE
SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
2. Wählen Sie eine Methode zum Beheben des Problems aus.
Sie können eine der folgenden Methoden verwenden, um das Problem zu beheben. Wählen Sie die, die am besten zu Ihren Umständen passt.
Methode 1: Verwenden OPTIMIZE_FOR_SEQUENTIAL_KEY Indexoption (nur SQL Server 2019)
In SQL Server 2019 wurde eine neue Indexoption (OPTIMIZE_FOR_SEQUENTIAL_KEY) hinzugefügt, mit der dieses Problem behoben werden kann, ohne eine der folgenden Methoden zu verwenden. Weitere Informationen finden Sie unter Hinter den Kulissen auf OPTIMIZE_FOR_SEQUENTIAL_KEY .
Methode 2: Verschieben des Primärschlüssels aus der Identitätsspalte
Machen Sie die Spalte, die sequenzielle Werte enthält, zu einem nicht gruppierten Index, und verschieben Sie dann den gruppierten Index in eine andere Spalte. Entfernen Sie beispielsweise für einen Primärschlüssel in einer Identitätsspalte den gruppierten Primärschlüssel, und erstellen Sie ihn dann als nicht gruppierten Primärschlüssel neu. Diese Methode ist am einfachsten zu verfolgen und erreicht direkt das Ziel.
Angenommen, Sie verfügen über die folgende Tabelle, die mithilfe eines gruppierten Primärschlüssels in einer Identity-Spalte definiert wurde.
USE testdb;
CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );
Um diesen Entwurf zu ändern, können Sie den Primärschlüsselindex entfernen und neu definieren.
USE testdb;
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)
Methode 3: Festlegen des führenden Schlüssels in eine nicht sequenzielle Spalte
Ordnen Sie die Definition des gruppierten Indexes so an, dass die führende Spalte nicht die sequenzielle Spalte ist. Diese Methode erfordert, dass der gruppierte Index ein zusammengesetzter Index ist. In einer Kundentabelle können Sie beispielsweise festlegen, dass eine CustomerLastName-Spalte die führende Spalte ist, gefolgt von der CustomerID. Es wird empfohlen, diese Methode gründlich zu testen, um sicherzustellen, dass sie die Leistungsanforderungen erfüllt.
USE testdb;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)
Methode 4: Hinzufügen eines nicht sequenziellen Werts als führenden Schlüssel
Fügen Sie einen nichtsequentialen Hashwert als führenden Indexschlüssel hinzu. Diese Technik trägt auch dazu bei, die Einfügungen zu verteilen. Ein Hashwert wird als Modulo generiert, das der Anzahl der CPUs im System entspricht. Auf einem System mit 16 CPU können Sie beispielsweise ein Modulo von 16 verwenden. Diese Methode verteilt die INSERT-Vorgänge gleichmäßig auf mehrere Datenbankseiten.
USE testdb;
CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );
ALTER TABLE Customers
ADD [HashValue] AS (CONVERT([TINYINT], abs([CustomerID])%16)) PERSISTED NOT NULL;
ALTER TABLE Customers
ADD CONSTRAINT pk_table1
PRIMARY KEY CLUSTERED (HashValue, CustomerID);
Methode 5: Verwenden einer GUID als führenden Schlüssel
Verwenden Sie eine GUID als führende Schlüsselspalte eines Indexes, um die gleichmäßige Verteilung von Einfügungen sicherzustellen.
Hinweis
Obwohl das Ziel erreicht wird, empfehlen wir diese Methode nicht, da sie mehrere Herausforderungen mit sich führt, einschließlich eines großen Indexschlüssels, häufiger Seitenaufteilungen, geringer Seitendichte usw.
Methode 6: Verwenden der Tabellenpartitionierung und einer berechneten Spalte mit einem Hashwert
Verwenden Sie Tabellenpartitionierung und eine berechnete Spalte mit einem Hashwert, um die INSERT-Vorgänge zu verteilen. Da diese Methode Tabellenpartitionierung verwendet, kann sie nur in Enterprise-Editionen von SQL Server verwendet werden.
Hinweis
Partitionierte Tabellen können in SQL Server 2016 SP1 Standard Edition verwendet werden. Weitere Informationen finden Sie in der Beschreibung von "Tabellen- und Indexpartitionierung" im Artikel Editionen und unterstützte Features von SQL Server 2016.
Es folgt ein Beispiel in einem System mit 16 CPUs.
USE testdb;
CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );
ALTER TABLE Customers
ADD [HashID] AS CONVERT(TINYINT, ABS(CustomerID % 16)) PERSISTED NOT NULL;
CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ;
CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);
CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON Customers (CustomerID, HashID) ON ps_hash(HashID);
Methode 7: Wechseln zu In-Memory OLTP
Alternativ können Sie In-Memory OLTP verwenden, insbesondere wenn der Latchkonflikt hoch ist. Diese Technologie beseitigt den Latchkonflikt insgesamt. Sie müssen jedoch die spezifische(n) Tabelle(n), in der Seitenlatchkonflikte beobachtet werden, neu gestalten und zu einer speicheroptimierten Tabelle migrieren. Sie können den Speicheroptimierungsratgeber und den Transaktionsleistungsanalysebericht verwenden, um zu bestimmen, ob eine Migration möglich ist und wie viel Aufwand für die Migration erforderlich wäre. Weitere Informationen dazu, wie In-Memory OLTP Latchkonflikte beseitigt, finden Sie im Dokument unter In-Memory OLTP – Allgemeine Workloadmuster und Migrationsüberlegungen.
References
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für