Columnstore partitionné sans arrêt ni Enterprise

Compresser et partitionner en columnstore une table de 385 millions de lignes écrite en continu sous SQL Server 2022 Standard, avec une bascule de 2,2 secondes.

Article coécrit avec Claude, qui a écrit avec moi les scripts de la migration et rejoué le procédé en laboratoire.

La table de départ

La table recevait les mesures d’une chaîne de supervision : un collecteur y écrivait en continu, environ 850 000 lignes par jour, une dizaine d’insertions par seconde, et des tableaux de bord la lisaient. Elle n’avait jamais été purgée ni compressée : 385 millions de lignes, 50,4 Go, un index cluster sur un identifiant et trois index non cluster, aucun compressé. Il n’y avait aucune fenêtre d’arrêt.

Le but : une table compressée, partitionnée par année pour purger une année entière par un TRUNCATE de partition le jour où la rétention serait décidée, et qui reste lisible et écrite pendant toute l’opération.

Un détail de l’historique a compté. La table avait été recréée onze mois plus tôt et une année entière rechargée dans le désordre : l’identifiant 1 avait une date de décembre, la ligne la plus ancienne datait de mai. Pour connaître le début de l’historique, il faut donc MIN() sur l’horodatage, et non l’horodatage du plus petit identifiant. J’ai fait l’erreur une fois, le jour même, avant de la corriger.

Les contraintes de l’édition Standard

Le serveur était en SQL Server 2022 Standard. Cette édition n’a ni les opérations d’index en ligne ni les opérations d’index parallèles (éditions et fonctionnalités de SQL Server 2022, tableaux High availability et Manageability). Partitionner la table en place ou la reconstruire en columnstore prend donc un verrou de modification de schéma (Sch-M) sur toute la table pendant toute l’opération ; créer un index non cluster prend un verrou partagé (S), qui laisse lire mais bloque toute écriture (CREATE INDEX, option ONLINE). Dans les deux cas, quelle que soit la valeur de LOCK_ESCALATION. Sur une table de 50 Go, cela veut dire des dizaines de minutes sans insertion possible.

Le columnstore partitionné ajoute ses propres règles, que j’ai vérifiées en laboratoire sur SQL Server 2022 (16.0.4265.3) :

  • un SPLIT n’est accepté que sur une partition vide (erreur 35346) ;
  • un MERGE est refusé si les deux partitions contiennent des données (erreur 35344) ;
  • un TRUNCATE TABLE ... WITH (PARTITIONS (...)) fonctionne, à condition que tous les index soient alignés sur le schéma de partitionnement (TRUNCATE TABLE) ; sur un columnstore cluster partitionné, le moteur refuse de toute façon un index non cluster qui ne l’est pas (erreur 35322). La liste des erreurs du moteur contient encore la 35349 (« A table with a columnstore index cannot be truncated »), que je n’ai pas réussi à déclencher : en laboratoire, le TRUNCATE d’une partition d’un columnstore cluster, avec un index B-tree aligné, a pris 4 ms.

Les bornes de partition doivent donc être posées avant que les données arrivent. Une borne oubliée sur une partition remplie ne se rattrape qu’en vidant la partition par un SWITCH, ou en supprimant puis recréant le columnstore, ce qui revient à reconstruire toute la table hors ligne.

La nouvelle table, construite à côté

L’idée est de ne jamais toucher la structure de la table en service. Je construis une nouvelle table, déjà partitionnée et en columnstore, je la remplis pendant que l’ancienne continue de servir, puis j’échange les noms.

Les exemples ci-dessous rejouent le procédé sur la table Elearning.Activite de la base de démonstration (7,8 millions de lignes, de 2018 à 2026). ContactId y joue le rôle du site, DateHeure celui de l’horodatage.

La fonction de partitionnement et la borne J

Une partition par année, et une borne de plus, J, le minuit qui suit la pose du trigger de recopie. La copie de l’historique ne démarre qu’après J : à partir de là, tout ce que le trigger écrit tombe après J, tout ce que la copie écrit tombe avant, et les deux flux n’écrivent jamais dans la même partition, pourvu que les mesures arrivent avec l’horodatage du moment : une mesure en retard, datée d’avant J, tomberait avec l’historique.

Chez le client, je l’avais posée pour qu’une escalade de verrous de la copie, arrêtée à sa partition, ne bloque pas le trigger. En laboratoire, la précaution s’est révélée inutile : pendant une transaction de copie ouverte, chargée en masse ou dans le deltastore, une insertion dans la même partition passe en 4 ms, et l’escalade de la copie ne monte jamais au-dessus de la partition.

USE PachaDataFormation;
GO

-- Une partition par année, plus une borne J, le minuit qui suit la pose du trigger
CREATE PARTITION FUNCTION pfActiviteAnnee (datetime2(0))
AS RANGE RIGHT FOR VALUES ('2019-01-01', '2020-01-01', '2021-01-01', '2022-01-01',
                           '2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01',
                           '2026-11-21',   -- J
                           '2027-01-01', '2028-01-01');

CREATE PARTITION SCHEME psActiviteAnnee
AS PARTITION pfActiviteAnnee ALL TO ([PRIMARY]);
GO

La borne J reste ensuite dans la fonction : les partitions avant et après J contiennent toutes deux des données, et le columnstore refuse de les fusionner. Elles disparaîtront ensemble à la purge de l’année, après le TRUNCATE des deux.

La table en columnstore

USE PachaDataFormation;
GO

-- La nouvelle table, mêmes colonnes, columnstore cluster partitionné
CREATE TABLE Elearning.ActiviteNouvelle (
    ActiviteId      bigint       NOT NULL,
    DateHeure       datetime2(0) NOT NULL,
    ContactId       int          NOT NULL,
    InscriptionId   int          NOT NULL,
    ModuleId        int          NULL,
    TypeEvenementCd char(4)      NOT NULL,
    Etape           smallint     NULL,
    DureeSecondes   int          NULL,
    Score           tinyint      NULL,
    Appareil        char(1)      NOT NULL
) ON psActiviteAnnee (DateHeure);

CREATE CLUSTERED COLUMNSTORE INDEX ccix_ActiviteNouvelle
ON Elearning.ActiviteNouvelle
ON psActiviteAnnee (DateHeure);

-- Escalade de verrous à la partition, pas à la table
ALTER TABLE Elearning.ActiviteNouvelle SET (LOCK_ESCALATION = AUTO);

-- Les contraintes se recréent sous un autre nom, unique dans le schéma
ALTER TABLE Elearning.ActiviteNouvelle ADD CONSTRAINT chk_ActiviteNouvelle_Appareil
    CHECK (Appareil IN ('M', 'T', 'O'));

-- Les repères de la migration
CREATE TABLE Elearning.ActiviteMigration (
    Repere sysname NOT NULL PRIMARY KEY,
    Valeur bigint  NOT NULL
);
GO

Avec LOCK_ESCALATION = AUTO, une escalade sur une table partitionnée s’arrête au niveau de la partition (HoBT) et ne monte pas à la table (ALTER TABLE, option LOCK_ESCALATION).

Chez le client, la table d’origine avait une clé primaire qui commençait par l’identifiant. Je l’ai abandonnée, après avoir vérifié que personne ne s’en servait : aucune clé étrangère ne pointait vers la table, et sur les 8 745 requêtes du Query Store qui citaient la table en un mois, aucune ne lisait l’identifiant. Sur un columnstore partitionné, une clé unique doit de toute façon contenir la colonne de partitionnement : tout index non cluster y est aligné, et un index unique aligné doit contenir cette colonne (erreur 1908). Les autres contraintes se recréent sur la nouvelle table, sous un autre nom tant que l’ancienne existe : l’exemple ne recrée que chk_Activite_Appareil ; les quatre clés étrangères de la table de démonstration se recréent de la même façon, avant la bascule.

Le trigger de recopie

USE PachaDataFormation;
GO

CREATE TRIGGER Elearning.trg_Activite_Recopie
ON Elearning.Activite
WITH EXECUTE AS OWNER   -- utile avec SET IDENTITY_INSERT, qui exige ALTER sur la table
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;

    INSERT INTO Elearning.ActiviteNouvelle
        (ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
         TypeEvenementCd, Etape, DureeSecondes, Score, Appareil)
    SELECT ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
           TypeEvenementCd, Etape, DureeSecondes, Score, Appareil
    FROM inserted;
END;
GO

Chez le client, l’identifiant était une colonne IDENTITY : le trigger l’encadrait de SET IDENTITY_INSERT ... ON et OFF pour recopier la valeur d’origine. Cette instruction exige le droit ALTER sur la table, que le compte du collecteur n’avait pas, d’où le WITH EXECUTE AS OWNER.

Le CREATE TRIGGER attend son verrou Sch-M : les insertions en cours se terminent avant, et toutes les suivantes passent par le trigger. Il n’y a donc pas de trou entre l’historique et ce que le trigger recopie. Pendant cette attente, les nouvelles insertions font la queue derrière lui : une transaction longue ouverte sur la table bloque donc le collecteur. Un chargement en masse (BULK INSERT, bcp) ne déclenche pas les triggers sans l’option FIRE_TRIGGERS (BULK INSERT) : il faut vérifier comment le collecteur écrit. Ici, il passait par une procédure d’insertion.

Le risque est ailleurs : une erreur dans le trigger annule l’insertion du collecteur. Chez le client, les deux tables avaient les mêmes contraintes et le même disque, le cas était improbable, et le trigger se retirait à tout moment sans toucher à l’ancienne table.

La copie de l’historique par lots

USE PachaDataFormation;
GO
SET NOCOUNT ON;

-- Dernier identifiant à copier, figé au premier passage : la copie se relance sans risque
IF NOT EXISTS (SELECT 1 FROM Elearning.ActiviteMigration WHERE Repere = N'fin_copie')
    INSERT INTO Elearning.ActiviteMigration (Repere, Valeur)
    SELECT N'fin_copie',
           ISNULL((SELECT MIN(ActiviteId) - 1 FROM Elearning.ActiviteNouvelle),
                  (SELECT MAX(ActiviteId) FROM Elearning.Activite));

DECLARE @lot bigint = 4194304;   -- quatre groupes pleins, si le lot tombe dans une seule partition
DECLARE @fin bigint = (SELECT Valeur FROM Elearning.ActiviteMigration WHERE Repere = N'fin_copie');
DECLARE @de  bigint = ISNULL((SELECT MAX(ActiviteId) FROM Elearning.ActiviteNouvelle
                              WHERE ActiviteId <= @fin), 0);
DECLARE @a bigint;

WHILE @de < @fin
BEGIN
    SET @a = CASE WHEN @fin - @de > @lot THEN @de + @lot ELSE @fin END;

    -- Pas de TABLOCK : il bloquerait les insertions du trigger
    INSERT INTO Elearning.ActiviteNouvelle
        (ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
         TypeEvenementCd, Etape, DureeSecondes, Score, Appareil)
    SELECT ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
           TypeEvenementCd, Etape, DureeSecondes, Score, Appareil
    FROM Elearning.Activite
    WHERE ActiviteId > @de AND ActiviteId <= @a
    OPTION (RECOMPILE);   -- l'estimation des vraies bornes du lot, voir plus bas

    RAISERROR(N'%I64d à %I64d : %d lignes', 0, 1, @de, @a, @@ROWCOUNT) WITH NOWAIT;
    SET @de = @a;
END;
GO

La borne de fin est le premier identifiant recopié par le trigger, moins un. Elle se fige au premier lancement de la copie, chez le client des heures après la pose du trigger : juste après la pose, deux insertions concurrentes peuvent valider dans le désordre, et une ligne serait copiée deux fois. Sur la base de démonstration, où aucun collecteur n’écrit, le trigger n’a rien recopié, et la copie prend tout l’existant.

D’après la documentation, un INSERT ... SELECT d’au moins 102 400 lignes dans un columnstore est un chargement en masse : les lignes vont directement dans des groupes compressés, sans passer par le deltastore, même sans TABLOCK (chargement des données dans un columnstore). Sur une table partitionnée, ce seuil s’applique partition par partition. TABLOCK rendrait l’insertion parallèle, mais prendrait un verrou exclusif sur la table et bloquerait le trigger. Un lot de 4 194 304 identifiants qui se suivent et tombent dans une seule partition donne quatre groupes pleins de 1 048 576 lignes, si la mémoire et le dictionnaire ne les coupent pas ; sur la base de démonstration, où chaque lot couvre plusieurs années, aucun groupe n’est plein.

Chez le client, la copie tournait dans un travail de l’Agent SQL Server : arrêté, il suffisait de le relancer pour qu’il reparte du dernier lot validé. Elle a copié 385,1 millions de lignes en 1 786 s, une demi-heure, soit environ 216 000 lignes par seconde. Aucune session n’a été bloquée, et le journal (base en mode de récupération simple) n’a pas dépassé 0,1 % de remplissage. Les groupes de lignes faisaient en moyenne 1 044 000 lignes.

Les écarts au plan

Le plan tenait en cinq étapes. La plupart des écarts qui suivent en ont ajouté une.

La date de début de la copie

Le début de la copie a été avancé de deux jours, et la borne J avec lui. La borne J était alors posée dans le futur, et la partition « avant J » contenait déjà les lignes du trigger : la borne ne pouvait plus être déplacée, puisqu’un columnstore refuse de découper une partition remplie. J’ai retiré le trigger et supprimé la nouvelle table. La fonction de partitionnement, qui ne servait plus à aucune table, s’est modifiée instantanément, puis j’ai tout recréé. La borne J se pose donc au moment où le trigger arrive, pas avant.

Les requêtes sur le columnstore seul

Avant de basculer, j’ai mesuré sur les deux tables les cinq requêtes de tableau de bord les plus coûteuses du Query Store, trois passages par table.

RequêteAncienne table (B-tree)Columnstore seul
Disponibilité d’un site sur tout l’historique0,76 à 1,0 s20,5 à 25,6 s
Disponibilité sur 7 et 14 jours, par suffixe0,76 à 0,95 s1,9 à 14,7 s
Disponibilité sur 7 et 14 jours, par site0,72 à 0,95 s1,1 à 1,8 s
Disponibilité et pannes, 7 et 14 jours0,72 à 0,74 s1,3 à 1,4 s
Périodes à zéro sur 7 jours0,38 à 0,40 s0,17 à 0,27 s

Toutes filtrent sur un site. Or chaque groupe de lignes contient tous les sites, puisque les lignes arrivent dans l’ordre du temps : les valeurs minimale et maximale du site de chaque segment couvrent toute la plage, et aucun groupe ne peut être écarté sur ce critère. Seule la requête des périodes à zéro gagne : elle lit le moins de lignes, sur sept jours et un seul suffixe. Je n’en ai pas isolé la cause. En édition Standard, le mode batch est de plus limité à deux threads.

Sans cette mesure, j’aurais découvert la régression en production.

L’index B-tree

Il a fallu un index B-tree non cluster sur le site et l’horodatage, aligné sur le schéma de partitionnement (le TRUNCATE de partition l’exige) et compressé en PAGE.

USE PachaDataFormation;
GO

-- B-tree aligné sur les partitions, compressé en PAGE
CREATE NONCLUSTERED INDEX nix_ActiviteNouvelle_Contact_DateHeure
ON Elearning.ActiviteNouvelle (ContactId, DateHeure)
INCLUDE (TypeEvenementCd, DureeSecondes)
WITH (DATA_COMPRESSION = PAGE)
ON psActiviteAnnee (DateHeure);
GO

En édition Standard, cette construction est hors ligne et en série : un verrou partagé (S) sur la nouvelle table pendant une demi-heure, qui laisse lire mais bloque toute insertion. Avec le trigger en place, chaque insertion du collecteur aurait attendu derrière lui, puisque le trigger écrit dans cette table. J’ai donc retiré le trigger, construit l’index sur une table que plus rien n’écrivait, et prévu de rattraper à la bascule les lignes arrivées entre-temps dans l’ancienne table, qui restait complète.

La première construction de l’index

La première construction de l’index a été perdue après environ 25 minutes sur 33 : la connexion SSMS du poste a été coupée (erreur 10053, « une connexion établie a été abandonnée par un logiciel de votre ordinateur hôte »), et SQL Server a annulé la construction, puisque sa session n’existait plus. La seconde est partie dans un travail de l’Agent. Depuis, une opération longue en production se lance dans un travail de l’Agent, même quand je reste devant l’écran.

Un plan forcé du Query Store

En relisant le Query Store, j’ai trouvé un plan forcé sur une des requêtes des tableaux de bord qui échouait depuis onze mois : 268 échecs, raison NO_INDEX, et une dernière exécution le jour où la table avait été recréée. L’index que le plan utilisait n’existait plus depuis le DROP et le CREATE de la table, et personne ne l’avait vu. La vue sys.query_store_plan donne ces échecs dans force_failure_count et last_force_failure_reason_desc.

La bascule

USE PachaDataFormation;
GO
SET XACT_ABORT ON;
SET LOCK_TIMEOUT 5000;   -- sans le verrou en 5 s, tout est annulé : on relance

-- Pas de bascule si la copie de l'historique n'est pas allée jusqu'à sa borne
DECLARE @fin bigint = (SELECT Valeur FROM Elearning.ActiviteMigration WHERE Repere = N'fin_copie');
IF (SELECT MAX(ActiviteId) FROM Elearning.ActiviteNouvelle WHERE ActiviteId <= @fin)
   IS DISTINCT FROM (SELECT MAX(ActiviteId) FROM Elearning.Activite WHERE ActiviteId <= @fin)
    THROW 50000, N'Copie de l''historique incomplète : pas de bascule', 1;

BEGIN TRANSACTION;

    -- Plus aucune insertion dans l'ancienne table jusqu'au COMMIT
    SELECT TOP (0) ActiviteId FROM Elearning.Activite WITH (TABLOCKX, HOLDLOCK);

    -- Le trigger, s'il est encore là
    DROP TRIGGER IF EXISTS Elearning.trg_Activite_Recopie;

    -- Rattrapage des lignes arrivées depuis le retrait du trigger
    INSERT INTO Elearning.ActiviteNouvelle
        (ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
         TypeEvenementCd, Etape, DureeSecondes, Score, Appareil)
    SELECT a.ActiviteId, a.DateHeure, a.ContactId, a.InscriptionId, a.ModuleId,
           a.TypeEvenementCd, a.Etape, a.DureeSecondes, a.Score, a.Appareil
    FROM Elearning.Activite AS a
    WHERE a.ActiviteId > (SELECT MAX(ActiviteId) FROM Elearning.ActiviteNouvelle);

    EXEC sp_rename N'Elearning.Activite', N'ActiviteAncienne';
    EXEC sp_rename N'Elearning.ActiviteNouvelle', N'Activite';

COMMIT TRANSACTION;
GO

Chez le client, la transaction a duré 2,2 s sous verrou exclusif, rattrapage compris : 103 869 lignes, arrivées pendant les trois heures écoulées depuis le retrait du trigger. Le collecteur n’a pas été arrêté ; ses insertions ont attendu ces 2,2 s.

Le rattrapage suppose des identifiants croissants et une copie de l’historique terminée : si la copie s’était arrêtée en route, MAX(ActiviteId) sauterait le trou sans rien dire. Le contrôle en tête du bloc refuse alors la bascule ; il ne compare que les maxima, et le comptage du lendemain reste nécessaire.

Par précaution, au cas où une insertion bloquée pendant la bascule atterrirait dans l’ancienne table une fois le verrou levé, j’ai réensemencé l’identité de la nouvelle table (DBCC CHECKIDENT ... RESEED) dans la transaction, avant le COMMIT, avec un écart de 10 000, pour qu’une telle ligne puisse être recopiée avec son identifiant sans collision, puis j’ai cherché ces lignes égarées une minute après : il n’y en avait aucune. En laboratoire non plus : les insertions bloquées, ad hoc ou par procédure, sont toutes arrivées dans la nouvelle table. Le rattrapage d’une table IDENTITY s’encadre aussi de SET IDENTITY_INSERT ... ON et OFF. Les vues qui citent la table par son nom gardent leurs métadonnées de colonnes : sp_refreshview sur chacune, et sp_recompile sur la procédure d’insertion.

Le lendemain, le contrôle donnait 385 263 633 lignes dans l’ancienne table, et autant dans la nouvelle jusqu’au dernier identifiant d’avant la bascule.

Le contrôle des groupes de lignes

Une fois la bascule faite, il faut relire l’état des groupes de lignes, partition par partition.

USE PachaDataFormation;
GO

SELECT rg.partition_number, rg.state_desc, rg.trim_reason_desc,
       COUNT(*)           AS groupes,
       SUM(rg.total_rows) AS lignes,
       MIN(rg.total_rows) AS plus_petit
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE rg.object_id = OBJECT_ID(N'Elearning.Activite')
GROUP BY rg.partition_number, rg.state_desc, rg.trim_reason_desc
ORDER BY rg.partition_number, rg.state_desc;
GO

Chez le client, l’historique tenait en 369 groupes compressés de 1 044 000 lignes en moyenne : 354 pleins (raison NO_TRIM), 15 coupés en fin de lot (BULKLOAD), aucun coupé pour la mémoire ou le dictionnaire, aucune ligne supprimée. Les 103 869 lignes du rattrapage, au-dessus du seuil de 102 400, sont arrivées directement compressées. Les insertions du collecteur remplissaient plusieurs groupes delta ouverts à la fois, ce qui arrive avec des sessions concurrentes : c’est normal, chacun se fermera à 1 048 576 lignes.

Un groupe posait problème : un groupe delta OPEN de 28 471 lignes, écrit par le trigger entre sa pose et l’heure J, donc dans la partition d’avant la borne. Cette partition ne recevra plus rien, et le tuple mover ne compresse que les groupes fermés (CLOSED) : le groupe pouvait rester ouvert longtemps. La documentation mentionne une tâche de fusion d’arrière-plan qui, depuis SQL Server 2019, compresse aussi les petits groupes ouverts depuis un certain temps, selon un seuil interne (vue d’ensemble des index columnstore) ; je ne l’ai pas attendue.

Le cas se produit dès qu’un flux temporaire (un trigger, un rattrapage) écrit moins de 102 400 lignes dans une partition qui ne reçoit plus rien.

Il se produit aussi, en laboratoire, avec la copie elle-même. Sans OPTION (RECOMPILE), la boucle de copie laisse des groupes ouverts dans plusieurs partitions, de taille variable d’une exécution à l’autre (de 130 à 308 947 lignes au fil des essais), y compris dans des partitions qui recevaient plusieurs centaines de milliers de lignes du même INSERT. Avec OPTION (RECOMPILE), qui donne à l’optimiseur les vraies bornes du lot au lieu de variables, aucun essai n’en a laissé. Je n’ai pas trouvé ce comportement dans la documentation, qui annonce des groupes compressés pour tout lot d’au moins 102 400 lignes.

Le correctif est un REORGANIZE de la partition, qui se fait en ligne, même en édition Standard (ALTER INDEX, REORGANIZE d’un index columnstore) :

USE PachaDataFormation;
GO

-- Compresse les groupes ouverts et fermés de la partition 6
ALTER INDEX ccix_ActiviteNouvelle ON Elearning.Activite
REORGANIZE PARTITION = 6 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- Fusionne ensuite les petits groupes compressés
ALTER INDEX ccix_ActiviteNouvelle ON Elearning.Activite
REORGANIZE PARTITION = 6;
GO

Le sp_rename de la bascule ne renomme pas les index : celui-ci garde le nom qu’il avait sur Elearning.ActiviteNouvelle. Le numéro de partition est celui que la requête de contrôle donne.

Chez le client, le premier REORGANIZE, avec COMPRESS_ALL_ROW_GROUPS, a pris une seconde. Il a compressé le groupe delta (transition REORG_FORCED, 28 471 lignes) et fusionné au passage les deux plus petits groupes compressés de la partition (298 759 et 561 733 lignes) en un seul de 860 492 (transition MERGE). Mais il n’a pas fusionné le nouveau groupe de 28 471 lignes : il a fallu un second REORGANIZE, qui l’a réuni au groupe de 860 492 en un groupe de 888 963 lignes. La partition finit à 237 groupes compressés, le plus petit à 888 963 lignes. Les groupes remplacés passent en TOMBSTONE, et le tuple mover les nettoie ensuite.

Le laboratoire donne la même chose. Dans une partition de la base de démonstration, après une copie sans OPTION (RECOMPILE), le premier REORGANIZE a compressé un groupe ouvert de 21 195 lignes sans le fusionner, et le second l’a réuni au groupe de 878 709 lignes. Avec une nuance : le dictionnaire a plafonné le groupe fusionné à 885 464 lignes (raison DICTIONARY_SIZE), et il reste un groupe de 14 440 lignes.

Le résultat

La place

AvantAprès
Table50,4 Go, 4 index non compressés9,3 Go
dont données14,1 Go (index cluster)2,6 Go (columnstore)
dont index non cluster36,2 Go (3 index)6,7 Go (1 index, PAGE)

Une fois l’ancienne table supprimée, ses 50 Go resteront alloués au fichier de données, qui ne rétrécit pas sans DBCC SHRINKFILE.

Les requêtes

Même protocole qu’avant la bascule, sur la nouvelle table indexée :

RequêteAncienne tableNouvelle table
Disponibilité sur 7 et 14 jours, par site0,73 s0,04 s
Disponibilité et pannes, 7 et 14 jours0,72 s0,06 s
Périodes à zéro sur 7 jours0,39 s0,04 à 0,06 s
Disponibilité sur 7 et 14 jours, par suffixe0,74 s1,08 s
Disponibilité d’un site sur tout l’historique0,78 s1,23 s

Les deux requêtes qui perdent environ 0,4 s sont les deux seules qui envoient le site en nvarchar, alors que la colonne est en varchar. En laboratoire, en collation Windows, avec un index sur le site et l’horodatage, une constante nvarchar garde le seek, mais sur le site seul : l’horodatage devient un filtre résiduel, et la requête lit tout l’historique du site (20 000 lignes lues pour 202 rendues, contre 202 avec une constante varchar). La requête par suffixe, qui ne demande que 7 et 14 jours, lit donc probablement tout l’historique, comme l’autre. Je ne l’ai pas vérifié sur le plan réel du client. Reste l’écart entre les deux tables pour une lecture de tout l’historique d’un site : la nouvelle doit décompresser les pages PAGE de l’index, hypothèse que je n’ai pas isolée. J’ai accepté cette perte : les autres requêtes sur 7 et 14 jours, de loin les plus nombreuses, sont 6 à 18 fois plus rapides, et un index étroit de plus aurait coûté 2 à 3 Go (estimation) et une nouvelle construction hors ligne d’une demi-heure.

Le lendemain matin, le Query Store sur les requêtes réellement envoyées par les tableaux de bord, une cinquantaine de requêtes (avant : la semaine précédant la migration ; après : de la bascule au lendemain 9 h 45, 1 à 6 exécutions par requête, donc des chiffres indicatifs) :

Famille de requêtesAvantAprèsLectures logiques avant, après
Panneaux sur 7 et 14 jours (la grande majorité)0,6 à 1,8 s20 à 65 ms11 700 à 27 000, 92 à 206
Panneaux de comptage simples0,5 à 0,9 s1 à 3 ms11 700 à 28 000, 23 à 1 005
Disponibilité sur tout l’historique d’un site0,74 à 1,5 s0,85 à 1,05 s7 700 à 9 200, 5 500 à 6 500

La requête sur tout l’historique, l’une des deux que la mesure de la veille donnait perdantes, reste au même niveau en production. La requête au plan forcé en échec depuis onze mois passe de 1,2 s à 43 ms.

Au passage, les tableaux de bord envoient le site en nvarchar, alors que la colonne est en varchar. La conversion coûte environ un tiers du temps de la requête sur les deux tables (0,76 s contre 0,53 s sur l’ancienne, 1,23 s contre 0,88 s sur la nouvelle). Le seek reste possible en collation Windows, mais sur le site seul ; en collation SQL, la conversion l’empêche. La correction est à faire côté tableau de bord.

Les insertions du collecteur

Moyennes horaires du Query Store :

PériodeDurée moyenneLectures logiquesJournal par insertion
Ancienne table, sans trigger123 à 143 µs19,2626 octets
Ancienne table, trigger actif (copie en cours)318 à 334 µs24 à 25770 à 790 octets
Nouvelle table, première heure157 à 164 µs8,0361 à 371 octets

Le trigger a multiplié par environ 2,4 le coût d’une insertion pendant la migration, sans jamais bloquer le collecteur : le maximum observé est de 347 ms, pendant l’heure de la copie. Sur la nouvelle table, une insertion lit 2,4 fois moins de pages et écrit 40 % de journal en moins, puisqu’il n’y a plus qu’un index B-tree à maintenir au lieu de quatre. Elle dure en revanche environ 20 % de plus (groupe delta du columnstore et compression PAGE de l’index). À dix insertions par seconde, la différence est de l’ordre de 0,3 ms par seconde.

Avant de supprimer l’ancienne table

L’ancienne table reste quelques jours sous un autre nom, pour revenir en arrière : recopier ce qui est arrivé depuis la bascule, puis rendre les noms. Avant de la supprimer, je vérifie qu’une sauvegarde complète postérieure à la bascule existe et contient les deux tables, et je note sa rétention. Ici, la sauvegarde complète de la nuit suivante (59,9 Go, 17,8 Go compressée, avec sommes de contrôle) n’était gardée qu’environ 48 heures sur le disque, et la base était en mode de récupération simple : pour garder l’ancienne table plus longtemps, il faut copier ce fichier ailleurs.

Enfin, chaque année, il faut ajouter la borne de l’année N+2 tant que la dernière partition est vide : avec un columnstore, une borne oubliée ne se pose plus par un simple SPLIT.