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

Article
06/15/2023

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

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

Symptômes

Plusieurs scénarios sont envisageables :

Vous avez 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 avez un index cluster qui a la colonne séquentielle comme colonne de début.

Remarque

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 avez 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 simultanément les opérations INSERT sur la même table.

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 sys.dm_exec_requests d’où 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, page_id de base de données = 4144

Remarque

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 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 qui se trouvent dans le cache de la mémoire tampon.

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

Les verrous de page sont courants dans tous les systèmes, car ils garantissent la protection physique des pages. Un index cluster trie les données en fonction de la colonne clé de début. 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, en cas de charge élevée, les opérations INSERT simultanées peuvent entraîner une contention sur la dernière page de l’arbre B. Cette contention peut se produire sur des index cluster et non cluster. La raison en est que les index non-cluster trient les pages de niveau 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 la 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

Remarque

Avant de tenter d’ouvrir ce notebook, assurez-vous 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 : Suivre les étapes manuellement

Pour résoudre ce conflit, 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 augmentez l’accès concurrentiel. Par conséquent, l’une des méthodes suivantes qui organisent les données selon une colonne autre que la colonne séquentielle atteint cet objectif.

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

Ce script T-SQL vous permet de déterminer s’il existe PAGELATCH_EX des attentes sur le système avec plusieurs sessions (5 ou plus) avec un temps d’attente important (10 ms ou plus). Il vous permet également de découvrir l’objet et d’index sur lequel se trouve la contention à 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. Choisissez 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 correspond le mieux à votre situation.

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 pour vous aider à résoudre ce problème sans utiliser l’une des méthodes suivantes. Pour plus d’informations, consultez Les coulisses de OPTIMIZE_FOR_SEQUENTIAL_KEY .

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

Faites de la colonne qui contient des valeurs séquentielles un 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 le tableau suivant 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 manière à ce que la colonne de début ne soit pas la colonne séquentielle. Cette méthode nécessite que l’index cluster soit un index composite. Par exemple, dans une table customer, vous pouvez faire en sorte qu’une colonne CustomerLastName soit la colonne de début, suivie de customerID. Nous vous recommandons de tester minutieusement 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 comme clé de début

Ajoutez une valeur de hachage non identique comme clé d’index de début. Cette technique permet également d’étaler les insertions. Une valeur de hachage est générée sous la forme d’un modulo qui correspond au nombre de processeurs sur le système. Par exemple, sur un système à 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é de début d’un index pour garantir la distribution uniforme des insertions.

Remarque

Bien qu’elle atteigne cet 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 pages, etc.

Méthode 6 : utiliser le partitionnement de table 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 table, elle est utilisable uniquement sur les éditions Enterprise de SQL Server.

Remarque

Vous pouvez utiliser des tables partitionnée dans SQL Server 2016 SP1 Standard Edition. Pour plus d’informations, consultez la description de « Partitionnement de table et d’index » dans l’article Éditions et 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 In-Memory OLTP

Vous pouvez également utiliser In-Memory OLTP en particulier si la contention de verrous est élevée. Cette technologie élimine la contention globale des verrous. Toutefois, vous devez reconcevoir et migrer la ou les tables spécifiques, où la contention des verrous de page est observée, vers une table mémoire optimisée. Vous pouvez utiliser l’Assistant Optimisation de la mémoire et le rapport d’analyse des performances des transactions pour déterminer si la migration est possible et quel serait l’effort nécessaire pour effectuer la migration. Pour plus d’informations sur la façon dont In-Memory OLTP é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 la contention d’insertion PAGELATCH_EX dernière page dans SQL Server

Symptômes

Cause

Résolution

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

Option 2 : Suivre les étapes manuellement

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

2. Choisissez 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 de la 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 comme clé de début

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

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

Méthode 7 : Basculer vers In-Memory OLTP

References

Commentaires

Ressources supplémentaires