Freigeben über

Konflikt beim Einfügen von PAGELATCH_EX auf letzter Seite in SQL Server auflösen

  • Artikel

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

In diesem Artikel wird erläutert, wie sie den Inhalt des letzten Seiteneinfüges PAGELATCH_EX in SQL Server auflösen.

Problembeschreibung

Betrachten Sie die folgenden Szenarien:

  • Sie verfügen über eine Spalte, die sequenzielle Werte enthält, z. B. eine Identitätsspalte oder eine DateTime-Spalte, die über die Getdate() -Funktion eingefügt wird.

  • Sie haben einen gruppierten Index, der die sequenzielle Spalte als führende Spalte aufweist.

    Notiz

    Das häufigste Szenario ist ein gruppierter Primärschlüssel in einer Identitätsspalte. Dieses Problem kann für nicht gruppierte Indizes weniger häufig 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 16 CPUs oder mehr. Mit dieser Hardwarekonfiguration können mehrere Sitzungen gleichzeitig die INSERT-Vorgänge für dieselbe Tabelle ausführen.

In diesem Fall kann es zu einer Abnahme der Leistung Ihrer Anwendung führen. Wenn Sie Die Wartetypen untersuchen sys.dm_exec_requests, beobachten Sie Wartezeiten auf dem 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, aus der sys.dm_exec_requests session_id > 50 und wait_type = "pagelatch_ex"

In dieser Situation erhalten Sie möglicherweise Ergebnisse, die wie folgt aussehen.

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 alle auf dieselbe Ressource warten, die dem folgenden Muster ähnelt:

database_id = 5, file_id = 1, Datenbank-page_id = 4144

Notiz

Die database_id sollte eine Benutzerdatenbank sein (die ID-Nummer ist größer oder gleich 5). Wenn die database_id 2 ist, können Sie stattdessen das Problem feststellen, das in Dateien, Ablaufverfolgungskennzeichnungen und Aktualisierungen auf TEMPDB behandelt wird.

Ursache

PAGELATCH (Latch auf einer Daten- oder Indexseite) ist ein Mechanismus zur Threadsynchronisierung. Er wird verwendet, um 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 sie von einem Datenträger gelesen oder auf den Datenträger geschrieben werden.

Seitenlatches kommen in jedem System häufig vor, da sie physischen Seitenschutz gewährleisten. Ein gruppierter Index sortiert die Daten nach der führenden Schlüsselspalte. Aus diesem Grund treten beim Erstellen des Indexes in einer sequenziellen Spalte alle neuen Dateneinfügungen am Ende des Indexes auf derselben Seite auf, bis diese Seite gefüllt ist. Unter hoher Auslastung können die gleichzeitigen INSERT-Vorgänge jedoch zu Konflikten auf der letzten Seite der B-Struktur führen. Dieser Inhalt kann in 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 ordnen. Dieses Problem wird auch als Konflikt beim Einfügen auf der letzten Seite bezeichnet.

Weitere Informationen finden Sie unter Diagnose und Auflösen von Latch Contention auf SQL Server.

Lösung

Sie können eine der folgenden beiden Optionen auswählen, um das Problem zu beheben.

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

Notiz

Bevor Sie versuchen, dieses Notizbuch zu öffnen, stellen Sie sicher, dass Azure Data Studio auf Ihrem lokalen Computer installiert ist. Informationen zum Installieren von Azure Data Studio finden Sie unter "Informationen zum Installieren von Azure Data Studio".

Notebook in Azure Data Studio öffnen

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

Um diesen Inhalt zu beheben, besteht die Allgemeine Strategie darin, alle gleichzeitigen INSERT-Vorgänge am Zugriff auf dieselbe Datenbankseite zu hindern. Nehmen Sie stattdessen für jeden INSERT-Vorgang Zugriff auf eine andere Seite 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 Inhalt auf PAGELATCH_EX, und identifizieren Sie die Inhaltsressource.

Dieses T-SQL-Skript hilft Ihnen zu ermitteln, ob PAGELATCH_EX das System mit mehreren Sitzungen (5 oder mehr) mit erheblicher Wartezeit (10 ms oder mehr) wartet. Außerdem können Sie ermitteln, auf welchem Objekt und indiziert sich der Inhalt befindet, indem Sie sys.dm_exec_requests und DBCC PAGE oder sys.fn_PageResCracker und sys.dm_db_page_info verwenden (nur SQL Server 2019).

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 aus, um das Problem zu beheben.

Sie können eine der folgenden Methoden verwenden, um das Problem zu beheben. Wählen Sie die Option aus, die Ihren Umständen am besten entspricht.

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 im Hintergrund zu OPTIMIZE_FOR_SEQUENTIAL_KEY .

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

Legen 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 z. B. für einen Primärschlüssel in einer Identitätsspalte den gruppierten Primärschlüssel, und erstellen Sie ihn dann erneut als nicht gruppierten Primärschlüssel. Diese Methode ist die einfachste Folge und erreicht direkt das Ziel.

Angenommen, Sie haben die folgende Tabelle, die mithilfe eines gruppierten Primärschlüssels in einer Identitätsspalte 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 zu einer nicht sequenziellen Spalte

Ordnen Sie die Gruppierte Indexdefinition so neu 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 eine CustomerLastName-Spalte als führende Spalte festlegen, 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ührender Schlüssel

Fügen Sie einen nichtsequenten Hashwert als führenden Indexschlüssel hinzu. Diese Technik trägt auch dazu bei, die Einfügungen auszubreiten. Ein Hashwert wird als Modul generiert, das der Anzahl der CPUs im System entspricht. Beispielsweise können Sie auf einem 16-CPU-System ein Modulo von 16 verwenden. Diese Methode verteilt die INSERT-Vorgänge einheitlich auf mehreren 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ührende Taste

Verwenden Sie eine GUID als führende Schlüsselspalte eines Indexes, um die einheitliche Verteilung von Einfügungen sicherzustellen.

Notiz

Obwohl es das Ziel erreicht, empfehlen wir diese Methode nicht, da sie mehrere Herausforderungen darstellt, einschließlich eines großen Indexschlüssels, häufiger Seitenteilungen, geringer Seitendichte usw.

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

Verwenden Sie die Tabellenpartitionierung und eine berechnete Spalte mit einem Hashwert, um die INSERT-Vorgänge auszubreiten. Da diese Methode die Tabellenpartitionierung verwendet, kann sie nur in Enterprise-Editionen von SQL Server verwendet werden.

Notiz

Sie können partitionierte Tabellen in SQL Server 2016 SP1 Standard Edition verwenden. Weitere Informationen finden Sie in der Beschreibung von "Tabellen- und Indexpartitionierung" im Artikel Editionen und unterstützten Features von SQL Server 2016.

Im Folgenden sehen Sie ein Beispiel für ein 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

Verwenden Sie alternativ OLTP im Arbeitsspeicher, insbesondere, wenn der Verschlussinhalt hoch ist. Diese Technologie beseitigt die Klammern insgesamt. Sie müssen jedoch die spezifischen Tabellen neu entwerfen und migrieren, bei denen der Seitenriegelinhalt beobachtet wird, zu einer speicheroptimierten Tabelle. Sie können den Speicheroptimierungsratgeber und den Analysebericht zur Transaktionsleistung verwenden, um zu ermitteln, ob die Migration möglich ist und wie viel Aufwand für die Migration erforderlich wäre. Weitere Informationen dazu, wie In-Memory OLTP die Klammerung beseitigt, das Dokument in OLTP im Arbeitsspeicher herunterladen und überprüfen – Allgemeine Arbeitsauslastungsmuster und Migrationsüberlegungen.

References

PAGELATCH_EX wartet und schwere Einfügungen