Dela via

Lös sista sidans inläggskonkurrens för PAGELATCH_EX i SQL-servern

  • Artikel

Ursprunglig produktversion: SQL Server
Ursprungligt KB-nummer: 4460004

Den här artikeln beskriver hur du löser infogningskonkurnitet PAGELATCH_EX på sista sidan i SQL Server.

Symptom

Föreställ dig följande scenarier:

  • Du har en kolumn som innehåller sekventiella värden, till exempel en identitetskolumn eller en DateTime-kolumn som infogas via funktionen Getdate().

  • Du har ett grupperat index som har den sekventiella kolumnen som en inledande kolumn.

    Kommentar

    Det vanligaste scenariot är en klustrad primärnyckel i en identitetskolumn. Mindre ofta kan det här problemet observeras för icke-illustrerade index.

  • Programmet utför frekventa INSERT- eller UPDATE-åtgärder mot tabellen.

  • Du har många processorer i systemet. Vanligtvis har servern 16 processorer eller mer. Med den här maskinvarukonfigurationen kan flera sessioner utföra INSERT-åtgärderna mot samma tabell samtidigt.

I den här situationen kan det uppstå en minskning av programmets prestanda. När du undersöker väntetyper i sys.dm_exec_requestsser du väntetider på PAGELATCH_EX väntetyp och många sessioner som väntar på den här väntetypen.

Ett annat problem uppstår om du kör följande diagnostikfråga i systemet:

välj session_id, wait_type, wait_time, wait_resource varifrån sys.dm_exec_requests session_id > 50 och wait_type = "pagelatch_ex"

I den här situationen kan du få resultat som liknar följande.

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

Du märker att flera sessioner väntar på samma resurs som liknar följande mönster:

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

Kommentar

Database_id ska vara en användardatabas (ID-numret är större än eller lika med 5). Om database_id är 2 kan du i stället uppleva problemet som beskrivs i Filer, spårningsflaggor och uppdateringar på TEMPDB.

Orsak

PAGELATCH (låser på en data- eller indexsida) är en trådsynkroniseringsmekanism. Den används för att synkronisera en kortvarig fysisk åtkomst till databassidor som finns i Buffer-cacheminnet.

PAGELATCH skiljer sig från en PAGEIOLATCH. Den senare används för att synkronisera fysisk åtkomst till sidor när de läses från eller sparas till en hårddisk.

Pagelatches är vanliga i varje system, eftersom de säkerställer fysiskt sidoskydd. Ett klustrat index sammanställer datan genom den ledande nyckelkolumnen. Av denna anledning, när du skapar indexet på en sekventiell kolumn, sker alla nya datainlägg på samma sida vid slutet av indexet tills den sidan är fylld. Men under hög belastning kan det samtidiga INLÄGGET eventuellt orsaka en konkurrens på sista sidan av B-tre. Denna konkurrens kan inträffa på klustrade och icke-klustrade index. Orsaken är att icke-klustrade index sammanställer sidor på bladnivå genom huvudnyckeln. Detta problem är även känt som inläggskonkurrens på sista sidan.

Mer information finns i Diagnostisera och lösa spärrkonkurration på SQL Server.

Åtgärd

Du kan välja något av följande två alternativ för att lösa problemet.

Alternativ 1: Kör stegen direkt i en notebook-fil via Azure Data Studio

Kommentar

Innan du försöker öppna den här notebook-filen kontrollerar du att Azure Data Studio är installerat på den lokala datorn. Om du vill installera det går du till Lär dig hur du installerar Azure Data Studio.

Öppna notebook-fil i Azure Data Studio

Alternativ 2: Följ stegen manuellt

För att lösa den här konkurrensen är den övergripande strategin att förhindra att alla samtidiga INSERT-åtgärder kommer åt samma databassida. Gör i stället varje INSERT-åtgärd till en annan sida och öka samtidigheten. Därför uppnår någon av följande metoder som organiserar data efter en annan kolumn än den sekventiella kolumnen det här målet.

1. Bekräfta konkurrensen om PAGELATCH_EX och identifiera konkurrensresursen

Det här T-SQL-skriptet hjälper dig att identifiera om det finns PAGELATCH_EX väntetider i systemet med flera sessioner (5 eller mer) med betydande väntetid (10 ms eller mer). Det hjälper dig också att identifiera vilket objekt och index som konkurrensen är på med hjälp av sys.dm_exec_requests och DBCC PAGE eller sys.fn_PageResCracker och sys.dm_db_page_info (endast 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. Välj en metod för att lösa problemet

Du kan använda någon av följande metoder för att lösa problemet. Välj den som passar bäst för dina omständigheter.

Metod 1: Använd alternativet OPTIMIZE_FOR_SEQUENTIAL_KEY index (endast SQL Server 2019)

I SQL Server 2019 lades ett nytt indexalternativ (OPTIMIZE_FOR_SEQUENTIAL_KEY) till som kan hjälpa dig att lösa problemet utan att använda någon av följande metoder. Mer information finns i Bakom kulisserna på OPTIMIZE_FOR_SEQUENTIAL_KEY .

Metod 2: Flytta primärnyckel från identitetskolumnen

Gör kolumnen som innehåller sekventiella värden till ett icke-grupperat index och flytta sedan det klustrade indexet till en annan kolumn. För en primärnyckel i en identitetskolumn tar du till exempel bort den klustrade primärnyckeln och återskapar den som en icke-illustrerad primärnyckel. Den här metoden är den enklaste metoden och uppnår målet direkt.

Anta till exempel att du har följande tabell som definierades med hjälp av en klustrad primärnyckel i en identitetskolumn.

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

Om du vill ändra den här designen kan du ta bort primärnyckelindexet och omdefiniera det.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)
Metod 3: Gör den inledande nyckeln till en icke-sekventiell kolumn

Ändra ordning på den klustrade indexdefinitionen på ett sådant sätt att den inledande kolumnen inte är den sekventiella kolumnen. Den här metoden kräver att det klustrade indexet är ett sammansatt index. I en kundtabell kan du till exempel göra en CustomerLastName-kolumn till den inledande kolumnen följt av CustomerID. Vi rekommenderar att du testar den här metoden noggrant för att se till att den uppfyller prestandakraven.

USE testdb;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)
Metod 4: Lägg till ett icke-sekventiellt värde som en inledande nyckel

Lägg till ett icke-sekventiellt hashvärde som den ledande indexnyckeln. Den här tekniken hjälper också till att sprida ut skären. Ett hash-värde genereras som en modulo som matchar antalet processorer i systemet. I ett 16-CPU-system kan du till exempel använda modulo 16. Den här metoden sprider ut INSERT-åtgärderna jämnt mot flera databassidor.

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);
Metod 5: Använd ett GUID som en inledande nyckel

Använd ett GUID som den inledande nyckelkolumnen i ett index för att säkerställa en enhetlig fördelning av infogningar.

Kommentar

Även om målet uppnås rekommenderar vi inte den här metoden eftersom den medför flera utmaningar, inklusive en stor indexnyckel, frekventa siddelningar, låg sidtäthet och så vidare.

Metod 6: Använd tabellpartitionering och en beräknad kolumn med ett hash-värde

Använd tabellpartitionering och en beräknad kolumn som har ett hash-värde för att sprida ut INSERT-åtgärderna. Eftersom den här metoden använder tabellpartitionering kan den endast användas i Enterprise-utgåvor av SQL Server.

Kommentar

Du kan använda partitionerade tabeller i SQL Server 2016 SP1 Standard Edition. Mer information finns i beskrivningen av "Tabell- och indexpartitionering" i artikeln Utgåvor och funktioner som stöds i SQL Server 2016.

Följande är ett exempel i ett system som har 16 processorer.

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);
Metod 7: Växla till Minnesintern OLTP

Alternativt kan du använda Minnesintern OLTP, särskilt om spärrkonkurrationen är hög. Den här tekniken eliminerar spärrkonkurrationen överlag. Du måste dock designa om och migrera de specifika tabellerna, där sidspärrkonkurvans observeras, till en minnesoptimerad tabell. Du kan använda rapporten Minnesoptimeringsrådgivare och Analys av transaktionsprestanda för att avgöra om migrering är möjlig och vad du kan göra för migreringen. Om du vill ha mer information om hur minnesintern OLTP eliminerar spärrkonkurration kan du ladda ned och granska dokumentet i Minnesintern OLTP – Vanliga arbetsbelastningsmönster och migreringsöverväganden.

Referenser

PAGELATCH_EX väntetider och tunga skär