Risolvere i conflitti di inserimento dell'ultima pagina PAGELATCH_EX in SQL Server

Versione originale del prodotto: SQL Server
Numero KB originale: 4460004

Questo articolo illustra come risolvere la contesa di inserimento PAGELATCH_EX dell'ultima pagina in SQL Server.

Sintomi

Si considerino gli scenari seguenti:

• È disponibile una colonna che include valori sequenziali, ad esempio una colonna Identity o una colonna DateTime inserita tramite la funzione Getdate().

• Si dispone di un indice cluster con la colonna sequenziale come colonna iniziale.

  Note

  Lo scenario più comune è una chiave primaria in cluster in una colonna Identity. Meno frequentemente, questo problema può essere osservato per gli indici non cluster.

• L'applicazione esegue frequenti operazioni INSERT o UPDATE sulla tabella.

• Nel sistema sono presenti molte CPU. In genere, il server dispone di 16 CPU o più. Questa configurazione hardware consente a più sessioni di eseguire le operazioni INSERT contemporaneamente sulla stessa tabella.

In questo caso, è possibile che si verifichi una diminuzione delle prestazioni dell'applicazione. Quando si esaminano i tipi di attesa in sys.dm_exec_requests, si osservano le attese sul tipo di attesa PAGELATCH_EX e molte sessioni in attesa di questo tipo di attesa.

Un altro problema si verifica se si esegue la query di diagnostica seguente nel sistema:

selezionare session_id, wait_type, wait_time, wait_resource da sys.dm_exec_requests dove session_id > 50 e wait_type = 'pagelatch_ex'

In questa situazione, è possibile ottenere risultati simili ai seguenti.

Si noti che più sessioni sono tutte in attesa della stessa risorsa simile al modello seguente:

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

Causa

PAGELATCH (latch in una pagina di dati o indice) è un meccanismo di sincronizzazione dei thread. Viene usato per sincronizzare l'accesso fisico a breve termine alle pagine del database che si trovano nella cache buffer.

PAGELATCH è diverso da un PAGEIOLATCH. Quest'ultimo viene usato per sincronizzare l'accesso fisico alle pagine quando vengono lette o scritte su disco.

I latch di pagina sono comuni in ogni sistema perché garantiscono la protezione della pagina fisica. Un indice cluster ordina i dati in base alla colonna chiave iniziale. Per questo motivo, quando si crea l'indice in una colonna sequenziale, tutti i nuovi inserimenti di dati si verificano nella stessa pagina alla fine dell'indice fino a quando tale pagina non viene riempita. Tuttavia, in caso di carico elevato, le operazioni INSERT simultanee possono causare conflitti nell'ultima pagina dell'albero B. Questo conflitto può verificarsi in indici cluster e non cluster. Il motivo è che gli indici non cluster ordinano le pagine a livello di foglio in base alla chiave iniziale. Questo problema è noto anche come conflitto di inserimento dell'ultima pagina.

Per altre informazioni, vedere Diagnosi e risoluzione dei conflitti di latch in SQL Server.

Risoluzione

È possibile scegliere una delle due opzioni seguenti per risolvere il problema.

Opzione 1: Eseguire i passaggi direttamente in un notebook tramite Azure Data Studio

Opzione 2: Seguire manualmente i passaggi

Per risolvere questo conflitto, la strategia complessiva consiste nel impedire a tutte le operazioni INSERT simultanee di accedere alla stessa pagina del database. In alternativa, rendere ogni operazione INSERT accedere a una pagina diversa e aumentare la concorrenza. Di conseguenza, uno dei metodi seguenti che organizzano i dati in base a una colonna diversa dalla colonna sequenziale raggiunge questo obiettivo.

1. Confermare la contesa in PAGELATCH_EX e identificare la risorsa di contesa

Questo script T-SQL consente di individuare se sono PAGELATCH_EX presenti attese nel sistema con più sessioni (5 o più) con un tempo di attesa significativo (10 ms o più). Consente inoltre di individuare l'oggetto e l'indicizzazione dei conflitti usando sys.dm_exec_requests e DBCC PAGE o sys.fn_PageResCracker e sys.dm_db_page_info (solo 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. Scegliere un metodo per risolvere il problema

Per risolvere il problema, è possibile usare uno dei metodi seguenti. Scegli quella più adatta alle tue circostanze.

Metodo 1: usare OPTIMIZE_FOR_SEQUENTIAL_KEY'opzione di indice (solo SQL Server 2019)

In SQL Server 2019 è stata aggiunta una nuova opzione di indice (OPTIMIZE_FOR_SEQUENTIAL_KEY) che consente di risolvere il problema senza usare uno dei metodi seguenti. Per altre informazioni, vedere Dietro le quinte OPTIMIZE_FOR_SEQUENTIAL_KEY .

Metodo 2: Spostare la chiave primaria dalla colonna Identity

Impostare la colonna contenente valori sequenziali un indice non cluster e quindi spostare l'indice cluster in un'altra colonna. Ad esempio, per una chiave primaria in una colonna Identity, rimuovere la chiave primaria in cluster e quindi ricrearla come chiave primaria non cluster. Questo metodo è il più semplice seguito e raggiunge direttamente l'obiettivo.

Si supponga, ad esempio, di avere la tabella seguente definita usando una chiave primaria cluster in una colonna Identity.

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

Per modificare questa progettazione, è possibile rimuovere l'indice della chiave primaria e ridefinirlo.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

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

Metodo 3: impostare la chiave iniziale su una colonna non sequenziale

Riordinare la definizione dell'indice cluster in modo che la colonna iniziale non sia la colonna sequenziale. Questo metodo richiede che l'indice cluster sia un indice composito. Ad esempio, in una tabella customer è possibile impostare una colonna CustomerLastName come colonna iniziale, seguita da CustomerID. È consigliabile testare accuratamente questo metodo per assicurarsi che soddisfi i requisiti di prestazioni.

USE testdb;

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

Metodo 4: Aggiungere un valore non sequenziale come chiave iniziale

Aggiungere un valore hash non sequenziale come chiave di indice iniziale. Questa tecnica aiuta anche a distribuire gli inserimenti. Un valore hash viene generato come modulo che corrisponde al numero di CPU nel sistema. Ad esempio, in un sistema a 16 CPU, è possibile usare un modulo di 16. Questo metodo distribuisce le operazioni INSERT in modo uniforme su più pagine di database.

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

Metodo 5: usare un GUID come chiave iniziale

Usare un GUID come colonna chiave iniziale di un indice per garantire la distribuzione uniforme degli inserimenti.

Metodo 6: Usare il partizionamento delle tabelle e una colonna calcolata con un valore hash

Usare il partizionamento delle tabelle e una colonna calcolata con un valore hash per distribuire le operazioni INSERT. Poiché questo metodo usa il partizionamento delle tabelle, è utilizzabile solo nelle edizioni Enterprise di SQL Server.

Di seguito è riportato un esempio in un sistema con 16 CPU.

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

Metodo 7: passare a OLTP in memoria

In alternativa, usare OLTP in memoria in particolare se la contesa di latch è elevata. Questa tecnologia elimina complessivamente la contesa di latch. Tuttavia, è necessario riprogettare ed eseguire la migrazione delle tabelle specifiche, in cui viene osservata la contesa di latch di pagina, in una tabella ottimizzata per la memoria. È possibile usare Ottimizzazione guidata memoria e Report analisi delle prestazioni delle transazioni per determinare se è possibile eseguire la migrazione e quale sarebbe l'impegno per eseguire la migrazione. Per altre informazioni su come OLTP in memoria elimina la contesa di latch, scaricare ed esaminare il documento in OLTP in memoria - Modelli di carico di lavoro comuni e considerazioni sulla migrazione.

Riferimenti

PAGELATCH_EX attese e inserimenti pesanti