Beheben des Konflikts beim Einfügen der letzten Seite PAGELATCH_EX in SQL Server

Artikel
06/15/2023

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.

Öffnen des Notebooks in 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

PAGELATCH_EX Wartezeiten und schwere Einfügungen

Freigeben über

Beheben des Konflikts beim Einfügen der letzten Seite PAGELATCH_EX in SQL Server

Symptome

Ursache

Lösung

Option 1: Direktes Ausführen der Schritte in einem Notebook über Azure Data Studio

Option 2: Führen Sie die Schritte manuell aus.

1. Bestätigen Sie den Konflikt auf PAGELATCH_EX und identifizieren Sie die Konfliktressource.

2. Wählen Sie eine Methode zum Beheben des Problems aus.

Methode 1: Verwenden OPTIMIZE_FOR_SEQUENTIAL_KEY Indexoption (nur SQL Server 2019)

Methode 2: Verschieben des Primärschlüssels aus der Identitätsspalte

Methode 3: Festlegen des führenden Schlüssels in eine nicht sequenzielle Spalte

Methode 4: Hinzufügen eines nicht sequenziellen Werts als führenden Schlüssel

Methode 5: Verwenden einer GUID als führenden Schlüssel

Methode 6: Verwenden der Tabellenpartitionierung und einer berechneten Spalte mit einem Hashwert

Methode 7: Wechseln zu In-Memory OLTP

References

Feedback

Zusätzliche Ressourcen