REORGANIZE et mise à jour asynchrone des statistiques : la file d'attente du verrou Sch-M
Categories:
13 minutes à lire
Un ALTER INDEX ... REORGANIZE tient un verrou de stabilité de schéma (Sch-S) sur la statistique de l’index qu’il réorganise, et le garde jusqu’à la fin. Si la base est en AUTO_UPDATE_STATISTICS_ASYNC = ON et que cette statistique devient périmée, la tâche de fond qui la recalcule demande un Sch-M pour l’enregistrer, et attend la fin du REORGANIZE. Les compilations qui ont besoin de la même statistique attendent alors derrière elle.
À partir de SQL Server 2022, ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON empêche les compilations d’attendre derrière la tâche de fond ; en échange, la mise à jour peut être abandonnée tant que le REORGANIZE tourne. Avant, il faut ne pas lancer de longues réorganisations sur une table active, et savoir que @TimeLimit d’Ola Hallengren n’interrompt pas une commande en cours.
Le symptôme, en production
Le cas vient d’un audit. Une base en groupe de disponibilité, en SYNCHRONOUS_COMMIT, avec AUTO_UPDATE_STATISTICS_ASYNC à ON. La maintenance est la procédure IndexOptimize d’Ola Hallengren, réglée pour réorganiser seulement : @FragmentationMedium et @FragmentationHigh à INDEX_REORGANIZE, seuil à 10 %, @TimeLimit et @MaxNumberOfPages à NULL.
L’index en cause est la clé primaire en cluster d’une table de 48 Go, soit 6,2 millions de pages. Quatre tentatives, aucune n’a fini : deux échecs du travail de l’Agent après 1 h 57 et 1 h 24, puis deux arrêts manuels après 11 et 34 minutes. Onze minutes de réorganisation ont fait passer la fragmentation de 39,02 % à 38,60 %. Le REORGANIZE avance, lentement : 139 secondes de CPU sur 2 046 secondes écoulées, en alternant WRITELOG et HADR_SYNC_COMMIT, parce que chacune de ses petites transactions attend l’accusé du secondaire synchrone.
Pendant la quatrième tentative, deux instantanés pris à une minute d’intervalle montrent la même chaîne (noms anonymisés) :
120 ALTER INDEX [PK_Offre] ... REORGANIZE HADR_SYNC_COMMIT
└─ 37 TASK MANAGER (sa, background) LCK_M_SCH_M 79 878 ms
├─ 65 SELECT tableau de bord LCK_M_SCH_S 62 625 ms
└─ 60 SELECT application métier LCK_M_SCH_S 30 840 ms
La session 37 n’avance plus entre les deux instantanés : 3 961 ms de CPU, 365 117 lectures, 3 031 écritures, figées. Elle a vraisemblablement fini son calcul et attend de pouvoir l’écrire. Les deux SELECT attendent un Sch-S derrière une tâche de mise à jour de statistiques : c’est le verrou que prend une compilation sur la statistique, et ils ne lisent probablement pas encore la table.
Sur place, la ressource du verrou n’avait pas été relevée. Le mécanisme ci-dessous a donc été reproduit en laboratoire.
Ce que verrouille un REORGANIZE
La documentation de ALTER INDEX dit qu’un REORGANIZE est toujours en ligne et ne tient pas de verrou de table de longue durée. C’est vrai pour les données. Pendant une réorganisation sur SQL Server 2025, sys.dm_tran_locks montre notamment ceci pour la session qui réorganise :
resource_type | resource_subtype | Ressource | request_mode |
|---|---|---|---|
OBJECT | la table | IX | |
OBJECT | INDEX_OPERATION | la table | S |
METADATA | STATS | object_id = <table>, stats_id = 1 | Sch-S |
METADATA | INDEXSTATS | object_id = <table>, index_id or stats_id = 1 | Sch-S |
Le Sch-S porte sur la statistique de l’index réorganisé, et sur elle seule. Une autre statistique de la même table, sur une colonne hors de l’index, s’est mise à jour pendant la réorganisation sans attendre quoi que ce soit.
La mise à jour asynchrone des statistiques
En mode asynchrone, une compilation qui trouve une statistique périmée ne l’attend pas : elle compile avec l’ancienne et met une demande de mise à jour en file. Une tâche de fond, visible comme TASK MANAGER dans sys.dm_exec_requests et comme un job dans sys.dm_exec_background_job_queue, fait le calcul. La page Statistics décrit la suite :
When the request is ready to write updated statistics to the database, it attempts to acquire a schema modification lock on the statistics metadata object. If a different session is already holding a lock on the same object, asynchronous statistics update is blocked until the schema modification lock can be acquired. Similarly, sessions that need to acquire a schema stability (Sch-S) lock on the statistics metadata object to compile a query can be blocked by the asynchronous statistics update background session, which is already holding or waiting to acquire the schema modification lock.
Le REORGANIZE est cette « different session ». Il tient son Sch-S aussi longtemps qu’il tourne, donc la tâche de fond attend aussi longtemps que lui. Il suffit que les modifications de la clé franchissent le seuil de mise à jour automatique pendant la réorganisation, et qu’une compilation passe par là : sur une table alimentée en continu et une réorganisation de plusieurs heures, les deux conditions ont toutes les chances d’être réunies.
La reproduction
Sur SQL Server 2025 (RTM-CU7), 22 processeurs logiques, une table de 4 millions de lignes à clé uniqueidentifier, fragmentée à 99 %, statistique de la clé calculée en FULLSCAN. La séquence est celle du script de fin d’article :
- lancer
ALTER INDEX ... REORGANIZEsur la clé primaire ; - insérer 100 000 lignes, ce qui dépasse le seuil de mise à jour de la statistique de la clé (environ 63 000 modifications pour 4 millions de lignes :
SQRT(1000 * lignes)à partir du niveau de compatibilité 130) ; - lancer une requête filtrée sur la clé : elle compile avec l’ancienne statistique et met la mise à jour en file ;
- lancer une autre requête neuve sur la clé.
L’état relevé quelques secondes plus tard montre la même chaîne qu’en production :
session_id command wait_type wait_time blocking_session_id
20 TASK MANAGER LCK_M_SCH_M 82892 54
54 DBCC PREEMPTIVE_OS_FLUSHFILEBUFFERS 1 0
57 SELECT LCK_M_SCH_S 78356 20
wait_resource de 20 : METADATA: database_id = 15 STATS(object_id = 1221579390, stats_id = 1), lockPartitionId = 17
wait_resource de 57 : METADATA: database_id = 15 STATS(object_id = 1221579390, stats_id = 1), lockPartitionId = 14
La tâche de fond (session 20) est bloquée par le REORGANIZE (session 54), et le SELECT (session 57) par la tâche de fond, sur la même statistique. Il attendait depuis 78 secondes. Dans un autre essai, une requête bloquée 58 secondes s’est terminée dans les deux secondes qui ont suivi l’arrêt du REORGANIZE. Le même script, rejoué dans une base en READ_COMMITTED_SNAPSHOT, donne la même chaîne.
Le script complet est en fin d’article.
Les compilations qui attendent
Sur treize exécutions du scénario sans l’option basse priorité, avec des formes de requêtes différentes, la tâche de fond s’est retrouvée bloquée par le REORGANIZE à chaque fois. Dans six d’entre elles, aucune requête ordinaire n’a attendu.
Le partitionnement des verrous en explique une partie. Au-delà d’un certain nombre de processeurs logiques, SQL Server découpe une ressource de verrou en une partition par processeur. Selon le guide du verrouillage, un Sch-S ne se prend que sur la partition de la transaction, alors qu’un Sch-M doit se prendre sur toutes les partitions, dans l’ordre, à partir de 0. Dans l’un des essais, le REORGANIZE tenait son Sch-S sur la partition 6 : la tâche de fond avait obtenu ses Sch-M sur les partitions 0 à 5 et attendait la 6. Une compilation qui tombe sur une partition déjà prise par la tâche de fond attend ; une compilation qui tombe sur une partition plus haute passe. La partition est attribuée à la transaction quand elle commence, et elle a changé d’un essai à l’autre. Sur un serveur sans partitionnement, il n’y a qu’une ressource, et toute compilation qui en a besoin devrait attendre (je ne l’ai pas mesuré).
Le guide écrit que seuls les verrous de type OBJECT sont partitionnés. Les wait_resource relevés pendant la mesure portent pourtant METADATA: ... STATS(...), lockPartitionId = 6 : les verrous de métadonnées de statistiques le sont aussi, au moins sur SQL Server 2025.
La forme de la requête compte aussi, et la documentation le laisse entendre : « sessions that need to acquire a schema stability (Sch-S) lock on the statistics metadata object to compile a query ». Toutes les compilations n’en ont pas besoin. Dans un essai de 48 sessions dont la partition a été relevée, les requêtes SELECT MAX(Prix) ... WHERE OffreId < ... ont attendu chaque fois qu’elles tombaient sur une partition déjà prise (13 sur 13), alors que les requêtes SELECT COUNT(*) ... WHERE OffreId > ... sont toutes passées, sur les mêmes partitions. Je n’ai pas trouvé de règle documentée qui dise quelles compilations prennent ce verrou.
Sortir de la file d’attente
KILL STATS JOB arrête la tâche de fond. Son numéro est la colonne job_id de sys.dm_exec_background_job_queue :
SELECT job_id, database_id, object_id1 AS object_id, object_id2 AS stats_id,
retry_count, in_progress, session_id
FROM sys.dm_exec_background_job_queue;
-- remplacer 3 par le job_id lu ci-dessus
KILL STATS JOB 3;
Les requêtes bloquées se terminent. Mais le travail est remis en file automatiquement : dans l’essai, un nouveau job est apparu dans la seconde avec retry_count = 2 et l’erreur 9104, et il était de nouveau en LCK_M_SCH_M derrière le REORGANIZE au bout de quelques secondes.
Arrêter le REORGANIZE libère tout. Deux secondes plus tard, plus aucune requête n’attendait, et la statistique avait été écrite. La réorganisation ne perd pas son travail : la page Optimize index maintenance précise que la progression déjà faite est conservée, et qu’on peut réorganiser un gros index en plusieurs fois.
ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY
Cette configuration de base, disponible depuis SQL Server 2022, met la demande de Sch-M de la tâche de fond dans une file de basse priorité. Les compilations ne s’empilent plus derrière elle.
USE [PachaDataFormation];
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;
Mesuré sur le même scénario : la tâche de fond attend en LCK_M_SCH_M_LOW_PRIORITY au lieu de LCK_M_SCH_M, et aucune des vingt requêtes SELECT MAX(Prix) ... WHERE OffreId < ... lancées pendant l’attente n’est apparue bloquée, alors que cette forme de requête attend sans l’option dès qu’elle tombe sur une partition prise.
La contrepartie est écrite dans la même page Statistics : si la tâche de fond n’obtient pas son verrou dans un délai de « several minutes », la mise à jour est abandonnée, et la statistique reste périmée jusqu’au prochain déclenchement automatique, ou jusqu’à une mise à jour manuelle. Mesuré dans deux essais : la tâche de fond est restée environ cinq minutes en LCK_M_SCH_M_LOW_PRIORITY, puis elle a quitté la file sans écrire la statistique, alors que le REORGANIZE tournait toujours. Dans un troisième essai, la statistique a été écrite pendant la réorganisation, sans que j’aie trouvé pourquoi.
L’option échange donc un risque contre un autre : des compilations bloquées, ou une statistique qui reste périmée quelques minutes de plus. Sur une base qui a choisi le mode asynchrone pour que les requêtes n’attendent pas les statistiques, le second risque est en général le plus acceptable.
Avant SQL Server 2022
L’option n’existe pas, et la tâche de fond attend en priorité normale. Ce qui reste :
- Ne pas réorganiser un gros index sur une table active. Sur la production, onze minutes ont retiré 0,42 point de fragmentation ; dans le laboratoire, deux minutes et demie en ont retiré 0,08. Mesurer d’abord ce que la fragmentation coûte vraiment aux requêtes avant de lancer des heures de réorganisation.
- Écarter les gros index avec
@MaxNumberOfPages, en sachant que sur une table partitionnée le seuil s’applique à chaque partition (@PartitionLevel = 'Y'par défaut). - Ne pas compter sur
@TimeLimit. DansIndexOptimize.sql(version du 12 septembre 2026), le paramètre est comparé à l’heure courante avant de lancer chaque commande : il empêche de commencer la suivante, il n’interrompt pas unREORGANIZEen cours. Il ne borne la durée d’une commande que pour une reconstruction reprenable, où il devientMAX_DURATION. - Mettre à jour les statistiques avant la réorganisation plutôt qu’après.
IndexOptimizetraite chaque index avant ses statistiques, et aucun paramètre n’inverse cet ordre. On peut l’obtenir avec deux appels dans la même étape : d’abord les statistiques seules (@FragmentationLow,@FragmentationMediumet@FragmentationHighexplicitement àNULL,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y'), puis la réorganisation seule. Cela remet le compteur de modifications à zéro au départ, sans plus : sur une table alimentée en continu, une réorganisation de plusieurs heures repasse le seuil. - Repasser la base en mise à jour synchrone (
AUTO_UPDATE_STATISTICS_ASYNC = OFF), en sachant que l’observation qui suit est limitée. Mesuré deux fois sur le même scénario, avec une seule forme de requête,SELECT MAX(Prix) ... WHERE OffreId < ...: pendant leREORGANIZE, la compilation qui trouve la statistique de la clé périmée ne la met pas à jour et n’attend pas ; la mise à jour se fait à la première compilation qui suit l’arrêt de la réorganisation. La page Statistics dit qu’en mode synchrone l’optimiseur attend la statistique à jour ; pendant la réorganisation, il ne l’a pas fait. En contrepartie, hors réorganisation, la requête qui déclenche une mise à jour attend son calcul, ce que le mode asynchrone sert justement à éviter. Le choix se fait base par base, en connaissant la raison pour laquelle l’option avait été activée.
Diagnostiquer
Pendant l’incident, une requête suffit à voir la chaîne et la ressource :
SELECT r.session_id, r.command, r.wait_type, r.wait_time,
r.blocking_session_id, r.wait_resource
FROM sys.dm_exec_requests AS r
WHERE r.wait_type IN ('LCK_M_SCH_M', 'LCK_M_SCH_S', 'LCK_M_SCH_M_LOW_PRIORITY')
OR r.command = 'DBCC';
Les trois signes qui identifient ce cas :
- une session
TASK MANAGERenLCK_M_SCH_M(LCK_M_SCH_M_LOW_PRIORITYsi l’option basse priorité est active), dontwait_resourcecommence parMETADATA:et nommeSTATS(object_id = ..., stats_id = ...), oùstats_idest l’index_idde l’index réorganisé ; - sa
blocking_session_idexécuteDBCC, la commande quesys.dm_exec_requestsaffiche pour unALTER INDEX ... REORGANIZE; - une ligne
in_progress = 1danssys.dm_exec_background_job_queue, dontobject_id2est l’identifiant de cette statistique.
Une précaution : une requête sur sys.dm_db_stats_properties pour cette statistique peut elle aussi rester bloquée derrière la tâche de fond : pendant un essai, elle a attendu 271 secondes en LCK_M_SCH_S, alors que sys.stats répondait.
Le script de reproduction
Le script crée une table d’environ 2 Go dans PachaDataFormation, active les statistiques asynchrones sur la base, puis supprime la table et remet les deux options à OFF, leurs valeurs d’origine dans PachaDataFormation ; sur une autre base, notez-les avant de commencer. Le fichier de données garde la taille prise. Il se joue dans quatre fenêtres de requête.
Fenêtre 1, la préparation, puis la réorganisation :
USE [PachaDataFormation];
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = OFF;
GO
CREATE TABLE dbo.DemoOffre (
OffreId uniqueidentifier NOT NULL DEFAULT NEWID()
CONSTRAINT pk_DemoOffre PRIMARY KEY CLUSTERED,
Prix int NOT NULL,
Periode datetime2 NOT NULL,
Remplissage char(300) NOT NULL DEFAULT 'x'
);
GO
-- 4 millions de lignes à clé aléatoire : l'index sort fragmenté à 99 %
SET NOCOUNT ON;
DECLARE @i int = 0;
WHILE @i < 40
BEGIN
INSERT dbo.DemoOffre (Prix, Periode)
SELECT TOP (100000) ABS(CHECKSUM(NEWID())) % 1000,
DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 500000, '2026-01-01')
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b;
SET @i += 1;
END;
UPDATE STATISTICS dbo.DemoOffre pk_DemoOffre WITH FULLSCAN;
GO
-- dure plusieurs minutes
ALTER INDEX pk_DemoOffre ON dbo.DemoOffre REORGANIZE;
Fenêtre 2, pendant la réorganisation :
USE [PachaDataFormation];
SET NOCOUNT ON;
-- la statistique de la clé dépasse son seuil
INSERT dbo.DemoOffre (Prix, Periode)
SELECT TOP (100000) 1, '2026-06-01'
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b;
-- cette compilation met la mise à jour asynchrone en file
SELECT COUNT(*) FROM dbo.DemoOffre
WHERE OffreId > '00000000-0000-0000-0000-00000000AAAA';
Fenêtre 3, quelques secondes plus tard : une requête neuve sur la clé. Elle reste bloquée si elle tombe sur une partition déjà prise par la tâche de fond ; sinon, la relancer depuis une autre fenêtre en changeant la constante, pour forcer une nouvelle compilation. Dans une quatrième fenêtre, la requête de diagnostic de la section précédente montre la chaîne.
USE [PachaDataFormation];
SELECT MAX(Prix) FROM dbo.DemoOffre
WHERE OffreId < '00000000-0000-0000-0000-00000000BBBB';
Le nettoyage, une fois la réorganisation arrêtée (bouton d’annulation de la fenêtre 1) :
USE [PachaDataFormation];
DROP TABLE IF EXISTS dbo.DemoOffre;
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = OFF;