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.
| 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 |
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
Nota
Il database_id deve essere un database utente (il numero ID è maggiore o uguale a 5). Se il database_id è 2, è invece possibile riscontrare il problema descritto in File, flag di traccia e aggiornamenti in TEMPDB.
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
Nota
Prima di tentare di aprire questo notebook, assicurarsi che Azure Data Studio sia installato nel computer locale. Per installarlo, vedere Informazioni su come installare 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://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 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.
Nota
Anche se raggiunge l'obiettivo, questo metodo non è consigliabile perché presenta più sfide, tra cui una chiave di indice di grandi dimensioni, frequenti suddivisioni di pagine, densità di pagina bassa e così via.
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.
Nota
È possibile usare tabelle partizionate in SQL Server 2016 SP1 Standard Edition. Per altre informazioni, vedere la descrizione del partizionamento di tabelle e indici nell'articolo Edizioni e funzionalità supportate di SQL Server 2016.
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.