Résoudre l’insertion de la dernière page PAGELATCH_EX contention dans SQL Server

Article
12/14/2024

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 4460004

Cet article explique comment résoudre la contention d’insertion PAGELATCH_EX de dernière page dans SQL Server.

Symptômes

Examinez les scénarios suivants :

Vous disposez d’une colonne qui inclut des valeurs séquentielles telles qu’une colonne Identity ou une colonne DateTime insérée par le biais de la fonction Getdate().
Vous disposez d’un index cluster qui a la colonne séquentielle en tant que colonne de début.

Note

Le scénario le plus courant est une clé primaire en cluster sur une colonne Identity. Moins fréquemment, ce problème peut être observé pour les index non cluster.
Votre application effectue des opérations INSERT ou UPDATE fréquentes sur la table.
Vous disposez de nombreux processeurs sur le système. En règle générale, le serveur a 16 PROCESSEURs ou plus. Cette configuration matérielle permet à plusieurs sessions d’effectuer les opérations INSERT sur la même table simultanément.

Dans ce cas, vous pouvez rencontrer une diminution des performances de votre application. Lorsque vous examinez les types d’attente dans sys.dm_exec_requests, vous observez les attentes sur le type d’attente PAGELATCH_EX et de nombreuses sessions qui attendent ce type d’attente.

Un autre problème se produit si vous exécutez la requête de diagnostic suivante sur votre système :

sélectionnez session_id, wait_type, wait_time, wait_resource à partir duquel sys.dm_exec_requests session_id > 50 et wait_type = 'pagelatch_ex'

Dans ce cas, vous pouvez obtenir des résultats qui ressemblent à ce qui suit.

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

Vous remarquez que plusieurs sessions attendent toutes la même ressource qui ressemble au modèle suivant :

database_id = 5, file_id = 1, base de données page_id = 4144

Note

Le database_id doit être une base de données utilisateur (le numéro d’ID est supérieur ou égal à 5). Si le database_id est 2, vous pouvez, à la place, rencontrer le problème abordé dans Fichiers, indicateurs de trace et mises à jour sur TEMPDB.

Cause

PAGELATCH (verrou sur une page de données ou d’index) est un mécanisme de synchronisation de threads. Il est utilisé pour synchroniser l’accès physique à court terme aux pages de base de données situées dans le cache de mémoire tampon.

PAGELATCH diffère d’une PAGEIOLATCH. Ce dernier est utilisé pour synchroniser l’accès physique aux pages lorsqu’elles sont lues ou écrites sur disque.

Les verrous de page sont courants dans chaque système, car ils garantissent la protection des pages physiques. Un index cluster trie les données par la colonne clé principale. Pour cette raison, lorsque vous créez l’index sur une colonne séquentielle, toutes les nouvelles insertions de données se produisent sur la même page à la fin de l’index jusqu’à ce que cette page soit remplie. Toutefois, sous une charge élevée, les opérations INSERT simultanées peuvent provoquer une contention sur la dernière page de l’arborescence B. Cette contention peut se produire sur des index cluster et non cluster. La raison est que les index non cluster trient les pages par alignement feuille par la clé de début. Ce problème est également appelé contention d’insertion de dernière page.

Pour plus d’informations, consultez Diagnostic et résolution de contention de verrous sur SQL Server.

Résolution

Vous pouvez choisir l’une des deux options suivantes pour résoudre le problème.

Option 1 : Exécuter les étapes directement dans un notebook via Azure Data Studio

Note

Avant d’essayer d’ouvrir ce notebook, vérifiez qu’Azure Data Studio est installé sur votre ordinateur local. Pour l’installer, accédez à Découvrir comment installer Azure Data Studio.

Ouvrir un notebook dans Azure Data Studio

Option 2 : Suivez les étapes manuellement

Pour résoudre cette contention, la stratégie globale consiste à empêcher toutes les opérations INSERT simultanées d’accéder à la même page de base de données. Au lieu de cela, faites en sorte que chaque opération INSERT accède à une page différente et augmente la concurrence. Par conséquent, l’une des méthodes suivantes qui organisent les données par une colonne autre que la colonne séquentielle atteint cet objectif.

1. Confirmer la contention sur PAGELATCH_EX et identifier la ressource de contention

Ce script T-SQL vous aide à découvrir s’il existe PAGELATCH_EX des attentes sur le système avec plusieurs sessions (5 ou plus) avec un temps d’attente significatif (10 ms ou plus). Il vous permet également de découvrir l’objet et l’index sur lesquels la contention est activée à l’aide de sys.dm_exec_requests et DBCC PAGE ou sys.fn_PageResCracker et sys.dm_db_page_info (SQL Server 2019 uniquement).

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. Choisir une méthode pour résoudre le problème

Vous pouvez utiliser l’une des méthodes suivantes pour résoudre le problème. Choisissez celui qui convient le mieux à vos circonstances.

Méthode 1 : Utiliser l’option d’index OPTIMIZE_FOR_SEQUENTIAL_KEY (SQL Server 2019 uniquement)

Dans SQL Server 2019, une nouvelle option d’index (OPTIMIZE_FOR_SEQUENTIAL_KEY) a été ajoutée qui peut aider à résoudre ce problème sans utiliser l’une des méthodes suivantes. Pour plus d’informations, voir Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY .

Méthode 2 : Déplacer la clé primaire hors colonne d’identité

Définissez la colonne qui contient des valeurs séquentielles comme index non cluster, puis déplacez l’index cluster vers une autre colonne. Par exemple, pour une clé primaire sur une colonne d’identité, supprimez la clé primaire en cluster, puis recréez-la en tant que clé primaire non cluster. Cette méthode est la plus simple à suivre et atteint directement l’objectif.

Par exemple, supposons que vous disposez du tableau suivant qui a été défini à l’aide d’une clé primaire en cluster sur une colonne 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 );

Pour modifier cette conception, vous pouvez supprimer l’index de clé primaire et le redéfinir.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

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

Méthode 3 : Faire de la clé de début une colonne non séquentielle

Réorganisez la définition d’index cluster de telle sorte que la colonne de début n’est pas la colonne séquentielle. Cette méthode nécessite que l’index cluster soit un index composite. Par exemple, dans une table client, vous pouvez faire d’une colonne CustomerLastName la colonne de début, suivie du CustomerID. Nous vous recommandons de tester soigneusement cette méthode pour vous assurer qu’elle répond aux exigences de performances.

USE testdb;

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

Méthode 4 : Ajouter une valeur non séquentielle en tant que clé de début

Ajoutez une valeur de hachage non référentielle comme clé d’index de début. Cette technique permet également de répartir les insertions. Une valeur de hachage est générée en tant que modulo qui correspond au nombre de processeurs sur le système. Par exemple, sur un système de 16 processeurs, vous pouvez utiliser un modulo de 16. Cette méthode répartit uniformément les opérations INSERT sur plusieurs pages de base de données.

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

Méthode 5 : Utiliser un GUID comme clé de début

Utilisez un GUID comme colonne clé principale d’un index pour garantir la distribution uniforme des insertions.

Note

Bien qu’elle atteigne l’objectif, nous ne recommandons pas cette méthode, car elle présente plusieurs défis, notamment une clé d’index volumineuse, des fractionnements de pages fréquents, une faible densité de page, et ainsi de suite.

Méthode 6 : Utiliser le partitionnement de tables et une colonne calculée avec une valeur de hachage

Utilisez le partitionnement de table et une colonne calculée qui a une valeur de hachage pour répartir les opérations INSERT. Étant donné que cette méthode utilise le partitionnement de tables, elle est utilisable uniquement sur les éditions Enterprise de SQL Server.

Note

Vous pouvez utiliser des tables partitionnée dans SQL Server 2016 SP1 Édition Standard. Pour plus d’informations, consultez la description de « Partitionnement de table et d’index » dans les éditions de l’article et les fonctionnalités prises en charge de SQL Server 2016.

Voici un exemple dans un système qui a 16 PROCESSEURs.

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

Méthode 7 : Basculer vers OLTP en mémoire

Vous pouvez également utiliser OLTP en mémoire particulièrement si la contention du verrou est élevée. Cette technologie élimine la contention du verrou dans l’ensemble. Toutefois, vous devez redéfinir et migrer la ou les tables spécifiques, où la contention de latch de page est observée, vers une table optimisée en mémoire. Vous pouvez utiliser le rapport d’analyse des performances de la mémoire et de l’optimisation de la mémoire pour déterminer si la migration est possible et ce que l’effort serait d’effectuer la migration. Pour plus d’informations sur la façon dont OLTP en mémoire élimine la contention des verrous, téléchargez et passez en revue le document dans OLTP en mémoire - Modèles de charge de travail courants et considérations relatives à la migration.

References

PAGELATCH_EX attentes et insertions lourdes

Partager via

Résoudre l’insertion de la dernière page PAGELATCH_EX contention dans SQL Server

Symptômes

Cause

Résolution

Option 1 : Exécuter les étapes directement dans un notebook via Azure Data Studio

Option 2 : Suivez les étapes manuellement

1. Confirmer la contention sur PAGELATCH_EX et identifier la ressource de contention

2. Choisir une méthode pour résoudre le problème

Méthode 1 : Utiliser l’option d’index OPTIMIZE_FOR_SEQUENTIAL_KEY (SQL Server 2019 uniquement)

Méthode 2 : Déplacer la clé primaire hors colonne d’identité

Méthode 3 : Faire de la clé de début une colonne non séquentielle

Méthode 4 : Ajouter une valeur non séquentielle en tant que clé de début

Méthode 5 : Utiliser un GUID comme clé de début

Méthode 6 : Utiliser le partitionnement de tables et une colonne calculée avec une valeur de hachage

Méthode 7 : Basculer vers OLTP en mémoire

References

Commentaires

Ressources supplémentaires