PAGELATCH_EX-conflicten met invoegen van laatste pagina oplossen in SQL Server

Artikel
02/12/2025

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 4460004

In dit artikel wordt uitgelegd hoe u conflicten tussen het invoegen PAGELATCH_EX van laatste pagina's in SQL Server kunt oplossen.

Symptomen

Bekijk de volgende scenario's:

U hebt een kolom die sequentiële waarden bevat, zoals een identiteitskolom of een DateTime-kolom die wordt ingevoegd via de functie Getdate().
U hebt een geclusterde index met de sequentiële kolom als voorloopkolom.

Notitie

Het meest voorkomende scenario is een geclusterde primaire sleutel in een identiteitskolom. Minder vaak kan dit probleem worden waargenomen voor niet-geclusterde indexen.
Uw toepassing voert regelmatig INSERT- of UPDATE-bewerkingen uit voor de tabel.
U hebt veel CPU's op het systeem. Normaal gesproken heeft de server 16 CPU's of meer. Met deze hardwareconfiguratie kunnen meerdere sessies de INSERT-bewerkingen gelijktijdig uitvoeren op dezelfde tabel.

In deze situatie kan de prestaties van uw toepassing afnemen. Wanneer u wachttypen sys.dm_exec_requestsbekijkt, ziet u wachttijden op het PAGELATCH_EX wachttype en veel sessies die op dit wachttype wachten.

Er treedt een ander probleem op als u de volgende diagnostische query op uw systeem uitvoert:

selecteer session_id, wait_type, wait_time, wait_resource waaruit sys.dm_exec_requests session_id > 50 en wait_type = 'pagelatch_ex'

In deze situatie krijgt u mogelijk resultaten die er ongeveer als volgt uitzien.

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

U ziet dat meerdere sessies allemaal wachten op dezelfde resource die lijkt op het volgende patroon:

database_id = 5, file_id = 1, database page_id = 4144

Notitie

De database_id moet een gebruikersdatabase zijn (het id-nummer is groter dan of gelijk aan 5). Als de database_id 2 is, kunt u in plaats daarvan het probleem ondervinden dat wordt besproken in Bestanden, traceringsvlagmen en updates op TEMPDB.

Oorzaak

PAGELATCH (vergrendeling op een gegevens- of indexpagina) is een thread-synchronisatiemechanisme. Dit wordt gebruikt om de kortdurende fysieke toegang tot databasepagina's die zich in de buffercache bevinden te synchroniseren.

PAGELATCH is niet hetzelfde als een PAGEIOLATCH. De laatste wordt gebruikt om fysieke toegang tot pagina's te synchroniseren wanneer ze worden gelezen vanuit of naar een schijf worden geschreven.

Paginavergrendelingen zijn gebruikelijk in elk systeem omdat ze zorgen voor fysieke paginabeveiliging. Met een geclusterde index worden de gegevens gesorteerd op basis van de kolom met het voorloopteken. Daarom worden, als u de index in een opeenvolgende kolom maakt, alle nieuwe gegevens ingevoegd op dezelfde pagina aan het eind van de index totdat die pagina vol is. Bij hoge belasting kunnen de gelijktijdige INSERT-bewerkingen echter conflicten veroorzaken op de laatste pagina van de B-boomstructuur. Deze conflicten kunnen optreden in geclusterde en niet-geclusterde indexen. De reden hiervoor is dat niet-geclusterde indexen de pagina's op bladniveau ordenen op het voorloopteken. Dit probleem wordt ook wel conflicten bij het invoegen van laatste pagina genoemd.

Zie Diagnoses en oplossen van vergrendelingsconflicten op SQL Server voor meer informatie.

Oplossing

U kunt een van de volgende twee opties kiezen om het probleem op te lossen.

Optie 1: Voer de stappen rechtstreeks in een notebook uit via Azure Data Studio

Notitie

Voordat u dit notebook probeert te openen, moet u ervoor zorgen dat Azure Data Studio is geïnstalleerd op uw lokale computer. Als u deze wilt installeren, gaat u naar Meer informatie over het installeren van Azure Data Studio.

Notebook openen in Azure Data Studio

Optie 2: Volg deze stappen handmatig

Om deze conflicten op te lossen, is de algehele strategie om te voorkomen dat alle gelijktijdige INSERT-bewerkingen toegang hebben tot dezelfde databasepagina. Maak in plaats daarvan elke INSERT-bewerking toegang tot een andere pagina en verhoog de gelijktijdigheid. Daarom bereikt een van de volgende methoden die de gegevens ordenen op een andere kolom dan de sequentiële kolom dit doel.

1. Bevestig de conflicten op PAGELATCH_EX en identificeer de conflictresource

Met dit T-SQL-script kunt u ontdekken of er wachttijden zijn PAGELATCH_EX op het systeem met meerdere sessies (5 of meer) met een aanzienlijke wachttijd (10 ms of meer). Het helpt u ook te ontdekken welk object en indexeren het conflict is op het gebruik van sys.dm_exec_requests en DBCC PAGE of sys.fn_PageResCracker en sys.dm_db_page_info (alleen 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. Kies een methode om het probleem op te lossen

U kunt een van de volgende methoden gebruiken om het probleem op te lossen. Kies de optie die het beste past bij uw omstandigheden.

Methode 1: Gebruik OPTIMIZE_FOR_SEQUENTIAL_KEY indexoptie (SQL Server 2019 en latere versies)

In SQL Server 2019 is een nieuwe indexoptie (OPTIMIZE_FOR_SEQUENTIAL_KEY) toegevoegd waarmee u dit probleem kunt oplossen zonder een van de volgende methoden te gebruiken. Zie Achter de schermen op OPTIMIZE_FOR_SEQUENTIAL_KEY voor meer informatie.

Methode 2: primaire sleutel uit identiteitskolom verplaatsen

Maak de kolom met opeenvolgende waarden een niet-geclusterde index en verplaats de geclusterde index vervolgens naar een andere kolom. Voor een primaire sleutel in een identiteitskolom verwijdert u bijvoorbeeld de geclusterde primaire sleutel en maakt u deze opnieuw als een niet-geclusterde primaire sleutel. Deze methode is de eenvoudigste manier om het doel te bereiken.

Stel dat u de volgende tabel hebt die is gedefinieerd met behulp van een geclusterde primaire sleutel in een identiteitskolom.

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 );

Als u dit ontwerp wilt wijzigen, kunt u de primaire-sleutelindex verwijderen en opnieuw definiëren.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)

Methode 3: De voorloopsleutel een niet-sequentiële kolom maken

Rangschik de geclusterde indexdefinitie zodanig dat de voorloopkolom niet de sequentiële kolom is. Deze methode vereist dat de geclusterde index een samengestelde index is. In een klanttabel kunt u bijvoorbeeld een Kolom CustomerLastName de voorloopkolom maken, gevolgd door de CustomerID. We raden u aan deze methode grondig te testen om ervoor te zorgen dat deze voldoet aan de prestatievereisten.

USE testdb;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)

Methode 4: Een niet-sequentiële waarde toevoegen als een voorloopsleutel

Voeg een niet-opeenvolgende hashwaarde toe als de belangrijkste indexsleutel. Deze techniek helpt ook bij het uitspreiden van de invoegingen. Er wordt een hashwaarde gegenereerd als een modulo die overeenkomt met het aantal CPU's op het systeem. Op een systeem met 16 CPU's kunt u bijvoorbeeld een modulo van 16 gebruiken. Met deze methode worden de INSERT-bewerkingen gelijkmatig verdeeld over meerdere databasepagina's.

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: Een GUID gebruiken als voorloopsleutel

Gebruik een GUID als de belangrijkste sleutelkolom van een index om de uniforme verdeling van invoegingen te garanderen.

Notitie

Hoewel deze methode het doel bereikt, raden we deze methode niet aan, omdat deze meerdere uitdagingen aangeeft, waaronder een grote indexsleutel, frequente paginasplitsingen, lage paginadichtheid, enzovoort.

Methode 6: Tabelpartitionering en een berekende kolom gebruiken met een hash-waarde

Gebruik tabelpartitionering en een berekende kolom met een hashwaarde om de INSERT-bewerkingen uit te spreiden. Omdat deze methode gebruikmaakt van tabelpartitionering, is deze alleen bruikbaar in Enterprise-edities van SQL Server.

Notitie

U kunt gepartitioneerde tabellen gebruiken in SQL Server 2016 SP1 Standard Edition. Zie de beschrijving van 'Tabel- en indexpartitionering' in het artikel Edities en ondersteunde functies van SQL Server 2016 voor meer informatie.

Hier volgt een voorbeeld in een systeem met 16 CPU's.

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: Overschakelen naar OLTP in het geheugen

U kunt ook OLTP in het geheugen gebruiken, met name als het vergrendelingsconflict hoog is. Deze technologie elimineert het vergrendelingsconflict over het algemeen. U moet echter de specifieke tabellen opnieuw ontwerpen en migreren, waarbij conflicten tussen paginavergrendelingen worden waargenomen, naar een tabel die is geoptimaliseerd voor geheugen. U kunt het rapport Memory Optimization Advisor en Transaction Performance Analysis gebruiken om te bepalen of de migratie mogelijk is en wat de inspanning is om de migratie uit te voeren. Voor meer informatie over hoe IN-Memory OLTP conflicten tussen vergrendelingen elimineert, downloadt en bekijkt u het document in In-Memory OLTP - Algemene workloadpatronen en migratieoverwegingen.

Verwijzingen

PAGELATCH_EX wachttijden en zware invoegingen

Delen via

PAGELATCH_EX-conflicten met invoegen van laatste pagina oplossen in SQL Server

Symptomen

Oorzaak

Oplossing

Optie 1: Voer de stappen rechtstreeks in een notebook uit via Azure Data Studio

Optie 2: Volg deze stappen handmatig

1. Bevestig de conflicten op PAGELATCH_EX en identificeer de conflictresource

2. Kies een methode om het probleem op te lossen

Methode 1: Gebruik OPTIMIZE_FOR_SEQUENTIAL_KEY indexoptie (SQL Server 2019 en latere versies)

Methode 2: primaire sleutel uit identiteitskolom verplaatsen

Methode 3: De voorloopsleutel een niet-sequentiële kolom maken

Methode 4: Een niet-sequentiële waarde toevoegen als een voorloopsleutel

Methode 5: Een GUID gebruiken als voorloopsleutel

Methode 6: Tabelpartitionering en een berekende kolom gebruiken met een hash-waarde

Methode 7: Overschakelen naar OLTP in het geheugen

Verwijzingen

Feedback

Aanvullende resources