Compresser les tables et les index

Depuis SQL Server 2016 sp1, la fonctionnalité de compression est disponible dans toutes les éditions de SQL Server, y compris l’édition Express.

La compression doit malheureusement être appliquée individuellement, pour chaque table et index, à la création de l’objet ou à sa reconstruction (rebuild).

Il y a deux modes de compression.

La compression ROW

La compression ROW n’est pas une compression au sens d’un algorithme spécifique. Il s’agit plutôt d’une variabilisation des types fixes, et de l’application d’une compression Unicode.

En résumé :

• très utile pour corriger les erreurs et les largesses de choix de types de données ;
• convertit à la volée le type de données pour chaque valeur, de façon transparente dans le moteur de stockage. Par exemple, la valeur 1 stockée dans une colonne de type INT sera convertie à la volée en TINYINT ;
• applique un algorithme de conversion Unicode très utile pour corriger les choix de colonnes NVARCHAR (stockage en UTF-16 sur deux octets par caractères) inutiles.

Cela permet d’économiser parfois beaucoup de place, d’IO et de RAM sans effet négatif notable sur la charge CPU. A activer sans modération.

La compression PAGE

La compression PAGE ajoute à la compression ROW un algorithme de compression par dictionnaire sur chaque page de 8 Ko. C’est une compression légèrement plus consommatrice en CPU, mais cela reste mesuré. En général, il y a assez de puissance CPU libre sur un serveur de bases de données pour absorber cette charge sans problème.

Cette compression est utile dans les tables ou index qui comportent beaucoup de doublons. Donc :

• les tables dans un data mart ;
• les index ;
• les tables mal normalisées.

Identifier les objets non compressés

Vous pouvez identifier les objets non compressés avec la requête suivante.

La colonne cmd du résultat vous permet de récupérer le code de compression des objets. Vous pouvez sélectionner uniquement cette colonne et choisir un affichage de résultat en texte (CTRL+T) dans SSMS pour générer les commandes GO à la ligne.

Estimation du gain de la compression

Vous pouvez estimer le gain apporté sur une table ou un index par l’un ou l’autre des modes de compression.

• A l’aide de SSMS : dans l’explorateur d’objets, clic droit sur l’objet (table ou index), menu contextuel « Stockage » (Storage) ➥ « Gérer la compression » (Manage compression). Vous pouvez utiliser le bouton « Calculer » (Calculate) pour obtenir une estimation de gain sur une compression ROW ou PAGE.
• A l’aide de la procédure stockée sp_estimate_data_compression_savings (non disponible dans Azure SQL Database).
• Vous trouvez sur mon Github un script qui utilise cette procédure stockée pour estimer les bénéfices de compression sur une table et tous ses index.

Compresser les objets

Vous pouvez compresser une table ou un index au moment de la création de l’objet. La compression n’a besoin d’être appliquée qu’une seule fois. Le moteur de stockage maintient ensuite la compression sur cet objet.

Par exemple :

USE [PachaDataFormation];

-- compression ROW
CREATE TABLE Inscription.FactureArchive (
    FactureCd VARCHAR(50) NOT NULL,
    DateFacture DATE NOT NULL,
    SocieteId INT NULL,
    ContactId INT NULL,
    MontantHT DECIMAL(9,2) NOT NULL
)
WITH (DATA_COMPRESSION = ROW);

-- compression PAGE
CREATE TABLE Inscription.LigneFactureArchive (
    FactureCd VARCHAR(50) NOT NULL,
    NumeroLigne SMALLINT NOT NULL,
    TypeLigne CHAR(4) NOT NULL,
    Quantite DECIMAL(9,2) NOT NULL,
    PrixUnitaire DECIMAL(9,2) NOT NULL
)
WITH (DATA_COMPRESSION = PAGE);

Et pour un index :

USE [PachaDataFormation];

-- compression ROW
CREATE INDEX nix_facture_datefacture ON Inscription.Facture (DateFacture)
WITH (DATA_COMPRESSION = ROW);

-- compression PAGE
CREATE INDEX nix_lignefacture_inscriptionid ON Inscription.LigneFacture (InscriptionId)
WITH (DATA_COMPRESSION = PAGE);

Compression des objets existants

Vous pouvez reconstruire une table ou un index pour le compresser après coup. Par exemple :

USE [PachaDataFormation];

-------- Table ---------
-- Compression ROW
ALTER TABLE Contact.Contact REBUILD WITH (DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER TABLE Contact.Contact REBUILD WITH (DATA_COMPRESSION = PAGE);

-------- Index ---------
-- Compression ROW
ALTER INDEX nix_contact_nom ON Contact.Contact REBUILD WITH (DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER INDEX nix_contact_nom ON Contact.Contact REBUILD WITH (DATA_COMPRESSION = PAGE);

Éviter les blocages en édition Entreprise

En édition Entreprise de SQL Server, vous pouvez utiliser l’option ONLINE pour reconstruire une table ou un index de manière non bloquante.

USE [PachaDataFormation];

-------- Table ---------
-- Compression ROW
ALTER TABLE Contact.Contact REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER TABLE Contact.Contact REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);

-------- Index ---------
-- Compression ROW
ALTER INDEX nix_contact_nom ON Contact.Contact REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER INDEX nix_contact_nom ON Contact.Contact REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);