# REORGANIZE et mise à jour asynchrone des statistiques : la file d'attente du verrou Sch-M

> Un REORGANIZE ne prend que des verrous légers, et pourtant des requêtes de lecture se sont retrouvées bloquées derrière lui pendant des minutes. Le coupable est la mise à jour asynchrone des statistiques, reproduite ici pas à pas sur SQL Server 2025.
>
> Source : https://www.pachadata.com/docs/articles/sqlserver/performances/reorganize-statistiques-asynchrones/
> Auteur : Rudi Bruchez · publié le 2026-10-11 · mis à jour le 2026-11-15
> Version Markdown de la page, pour les outils et les LLM. La page HTML fait référence.





> TL;DR :
>
> 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) :

```text
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`](https://learn.microsoft.com/fr-fr/sql/t-sql/statements/alter-index-transact-sql#reorganize-a-rowstore-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`](https://learn.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql) 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`](https://learn.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql) et comme un job dans [`sys.dm_exec_background_job_queue`](https://learn.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-background-job-queue-transact-sql), fait le calcul. La page [Statistics](https://learn.microsoft.com/fr-fr/sql/relational-databases/statistics/statistics#auto_update_statistics_async) 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 :

1. lancer `ALTER INDEX ... REORGANIZE` sur la clé primaire ;
2. 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)`](https://learn.microsoft.com/fr-fr/sql/relational-databases/statistics/statistics#auto_update_statistics-option) à partir du niveau de compatibilité 130) ;
3. lancer une requête filtrée sur la clé : elle compile avec l'ancienne statistique et met la mise à jour en file ;
4. 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 :

```text
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](https://learn.microsoft.com/fr-fr/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide#lock-partitioning), 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`](https://learn.microsoft.com/fr-fr/sql/t-sql/language-elements/kill-stats-job-transact-sql) arrête la tâche de fond. Son numéro est la colonne `job_id` de `sys.dm_exec_background_job_queue` :

```sql
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](https://learn.microsoft.com/fr-fr/sql/relational-databases/indexes/reorganize-and-rebuild-indexes#reorganize-an-index) 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](https://learn.microsoft.com/fr-fr/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql), 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.

```sql
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`. Dans `IndexOptimize.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 un `REORGANIZE` en cours. Il ne borne la durée d'une commande que pour une reconstruction reprenable, où il devient `MAX_DURATION`.
- Mettre à jour les statistiques avant la réorganisation plutôt qu'après. `IndexOptimize` traite 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`, `@FragmentationMedium` et `@FragmentationHigh` explicitement à `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 le `REORGANIZE`, 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 :

```sql
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 MANAGER` en `LCK_M_SCH_M` (`LCK_M_SCH_M_LOW_PRIORITY` si l'option basse priorité est active), dont `wait_resource` commence par `METADATA:` et nomme `STATS(object_id = ..., stats_id = ...)`, où `stats_id` est l'`index_id` de l'index réorganisé ;
- sa `blocking_session_id` exécute `DBCC`, la commande que `sys.dm_exec_requests` affiche pour un `ALTER INDEX ... REORGANIZE` ;
- une ligne `in_progress = 1` dans `sys.dm_exec_background_job_queue`, dont `object_id2` est l'identifiant de cette statistique.

Une précaution : une requête sur [`sys.dm_db_stats_properties`](https://learn.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-objects/sys-dm-db-stats-properties-transact-sql) 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 :

```sql
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 :

```sql
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.

```sql
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) :

```sql
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;
```


---

## L'auteur

Rudi Bruchez, consultant SQL Server indépendant depuis 2006 : audit de performance, optimisation, migrations, haute disponibilité et formation. Pour un audit ou une intervention sur votre serveur : https://www.pachadata.com/contact/ (audit à distance : https://www.pachadata.com/services/audit-sql-server-a-distance/).
