Risolvere l'inserimento dell'ultima pagina PAGELATCH_EX contesa 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:

• Si dispone di 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.

  Nota

  Lo scenario più comune è una chiave primaria 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 ha 16 CPU o più. Questa configurazione hardware consente a più sessioni di eseguire contemporaneamente le operazioni INSERT sulla stessa tabella.

In questo caso, è possibile che si verifichi una riduzione delle prestazioni dell'applicazione. Quando si esaminano i tipi di attesa in sys.dm_exec_requests, si osservano 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 questo caso, è 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, database page_id = 4144

Causa

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

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

I latch di pagina sono comuni in ogni sistema perché garantiscono la protezione fisica delle pagine. 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 compilata. Tuttavia, in caso di carico elevato, le operazioni INSERT simultanee possono causare contese nell'ultima pagina dell'albero B. Questa contesa può verificarsi su indici cluster e non cluster. Il motivo è che gli indici non cluster ordinano le pagine a livello foglia in base alla chiave iniziale. Questo problema è noto anche come contesa 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 i passaggi manualmente

Per risolvere questa contesa, la strategia generale consiste nell'impedire a tutte le operazioni INSERT simultanee di accedere alla stessa pagina del database. Impostare invece ogni operazione INSERT per accedere a una pagina diversa e aumentare la concorrenza. Pertanto, uno dei metodi seguenti che organizzano i dati in base a una colonna diversa dalla colonna sequenziale raggiunge questo obiettivo.

1. Confermare la contesa su 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 tempo di attesa significativo (10 ms o più). Consente inoltre di individuare l'oggetto e l'indice su cui si basa la contesa 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://video2.skills-academy.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 quello più adatto alle tue circostanze.

Metodo 1: usare OPTIMIZE_FOR_SEQUENTIAL_KEY'opzione index (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 su OPTIMIZE_FOR_SEQUENTIAL_KEY .

Metodo 2: Spostare la chiave primaria dalla colonna Identity

Impostare la colonna che contiene valori sequenziali come 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 cluster e quindi ricrearla come chiave primaria non cluster. Questo metodo è il seguito più semplice 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 struttura, è 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 come 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 consente anche di distribuire gli inserti. 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 della tabella 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 In-Memory OLTP

In alternativa, usare In-Memory OLTP in particolare se la contesa di latch è elevata. Questa tecnologia elimina la contesa di latch nel complesso. Tuttavia, è necessario riprogettare ed eseguire la migrazione delle tabelle specifiche, in cui viene osservata la contesa del latch di pagina, a una tabella ottimizzata per la memoria. È possibile usare Memory Optimization Advisor e Transaction Performance Analysis Report per determinare se la migrazione è possibile e quale sarebbe lo sforzo di eseguire la migrazione. Per altre informazioni su come In-Memory OLTP 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