Dans cet article, nous aborderons un problème majeur de performances qui peut survenir dans SQL Server lors de l’insertion intensive de données dans une table. Ce problème est connu sous le nom de contention de la dernière page d’insertion et peut avoir un impact négatif sur les performances globales de la base de données.

Comprendre le problème

SQL Server alloue de la mémoire à partir du système d’exploitation pour effectuer des opérations de lecture et de modification en mémoire plutôt que sur le disque. Cette architecture permet de réduire les opérations d’E/S physiques et d’améliorer le temps de réponse. La zone de mémoire réservée, appelée pool de tampons ou cache de tampons, est responsable de la conservation des pages de données et d’index.

Un mécanisme interne de SQL Server appelé PAGELATCH est responsable de la synchronisation et de la protection de l’intégrité des pages d’index et de données dans le pool de tampons. Cependant, lors de scénarios de charge intensive, ce mécanisme peut entraîner des problèmes de performances.

La contention de la dernière page d’insertion se produit lorsque plusieurs threads tentent d’accéder simultanément à la dernière page d’un index. Cette contention fait attendre les threads que la dernière page devienne accessible, ce qui entraîne une diminution des performances et l’observation du type d’attente PAGELATCH_EX.

Identifier le problème

Pour illustrer le problème de contention de la dernière page d’insertion, considérons un exemple simple. Nous créons une table d’exemple avec un index cluster et une contrainte d’identité :

CREATE TABLE InsertTestTable (
  Id INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
  Col VARCHAR(50) NOT NULL
);

Nous pouvons générer une charge d’insertion intensive sur cette table à l’aide de l’outil SQLQueryStress :

SET NOCOUNT ON;

DECLARE @i INT = 1;
WHILE @i < 1000
BEGIN
  INSERT INTO dbo.InsertTestTable (Col)
  VALUES ('Valeur de test');
  SET @i += 1;
END;

En surveillant le système, nous pouvons observer le type d’attente PAGELATCH_EX et les sessions suspendues en attente que la dernière page devienne accessible.

Résoudre le problème

Il existe plusieurs méthodes pour résoudre le problème de contention de la dernière page d’insertion et améliorer les performances de SQL Server :

1. Activer l’option OPTIMIZE_FOR_SEQUENTIAL_KEY

À partir de SQL Server 2019, vous pouvez activer l’option OPTIMIZE_FOR_SEQUENTIAL_KEY sur l’index cluster. Cette option limite le nombre de threads autorisés à demander le verrou à un par planificateur, ce qui réduit le temps passé dans la file d’attente des tâches une fois le verrou acquis.

ALTER INDEX PK__InsertTe__3214EC074999CCCE ON dbo.InsertTestTable
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

L’activation de cette option peut entraîner des améliorations de performances, comme en témoigne le changement du type d’attente de PAGELATCH_EX à BTREE_INSERT_FLOW_CONTROL.

2. Définir un index non-cluster sur la colonne de clé primaire

Une autre approche consiste à supprimer l’index cluster et la clé primaire de la table, puis à créer une clé primaire avec un index non-cluster à la place :

ALTER TABLE InsertTestTable DROP CONSTRAINT PK__InsertTe__3214EC074999CCCE;

ALTER TABLE InsertTestTable ADD CONSTRAINT PrimaryKey_Id PRIMARY KEY NONCLUSTERED (ID);

Cette approche de conception permet de résoudre le problème d’insertion de la dernière page et de réduire l’occurrence du type d’attente PAGELATCH_EX.

3. Utiliser des tables optimisées en mémoire

L’utilisation de tables optimisées en mémoire plutôt que de tables basées sur le disque peut également améliorer les performances de SQL Server. Les tables optimisées en mémoire peuvent résoudre les problèmes de verrouillage et de contention, ce qui se traduit par des opérations d’insertion plus rapides.

Pour créer une table optimisée en mémoire, vous devez ajouter un groupe de fichiers optimisés en mémoire pour contenir les données et créer les fichiers nécessaires :

ALTER DATABASE SQLShackDemo ADD FILEGROUP SQLShackDemo_mod CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE SQLShackDemo ADD FILE (name = 'SQLShackDemo_mod1', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLShackDemo_mod1.ndf') TO FILEGROUP SQLShackDemo_mod;

Après avoir créé le groupe de fichiers et les fichiers, vous pouvez créer une table optimisée en mémoire durable :

CREATE TABLE InMemoryInsertTestTable (
  Id INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED,
  Col VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

L’utilisation d’une table optimisée en mémoire peut considérablement améliorer les performances de SQL Server pour les opérations d’insertion et éliminer le type d’attente PAGELATCH_EX.

Conclusion

Dans cet article, nous avons exploré le problème de contention de la dernière page d’insertion dans SQL Server et discuté des différentes méthodes pour le résoudre. En activant l’option OPTIMIZE_FOR_SEQUENTIAL_KEY, en définissant des index non-cluster sur les colonnes de clé primaire ou en utilisant des tables optimisées en mémoire, vous pouvez améliorer considérablement les performances de SQL Server lors d’opérations d’insertion intensives.