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
1stockée dans une colonne de typeINTsera convertie à la volée enTINYINT; - 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 :
-- compression ROW
CREATE TABLE matable (id INT)
WITH (DATA_COMPRESSION = ROW);
-- compression PAGE
CREATE TABLE matable (id INT)
WITH (DATA_COMPRESSION = PAGE);
Et pour un index :
-- compression ROW
CREATE INDEX ix ON matable (id)
WITH (DATA_COMPRESSION = ROW);
-- compression PAGE
CREATE INDEX ix ON matable (id)
WITH (DATA_COMPRESSION = PAGE);
Compression des objets existants
Vous pouvez reconstruire une table ou un index pour le compresser après coup. Par exemple :
-------- Table ---------
-- Compression ROW
ALTER TABLE matable REBUILD WITH (DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER TABLE matable REBUILD WITH (DATA_COMPRESSION = PAGE);
-------- Index ---------
-- Compression ROW
ALTER INDEX ix ON matable REBUILD WITH (DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER INDEX ix ON matable REBUILD WITH (DATA_COMPRESSION = PAGE);
Eviter 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.
-------- Table ---------
-- Compression ROW
ALTER TABLE matable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER TABLE matable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
-------- Index ---------
-- Compression ROW
ALTER INDEX ix ON matable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
-- ou compression PAGE
ALTER INDEX ix ON matable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);