Résoudre la contention d’insertion PAGELATCH_EX dernière page dans SQL Server
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.
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
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour