LLI Wiki — My Digital Knowledge Base

Compression des tables SQL Server : choisir la méthode et estimer le gain

Choix d'une méthode de compression SQL Server (ROW, PAGE, columnstore), liste des tables compressées et estimation du gain avant compression.

La compression réduit l'espace occupé par les tables et leurs index. Le bon choix dépend de l'objectif : gagner vite de la place, limiter la perturbation ou obtenir le taux de compression le plus élevé. Pour aller plus loin, voir l'article de SQLPerformance sur la compression columnstore et celui de SQL Server Science sur la compression des grandes tables.

Choisir la méthode

Objectif Méthode
Gagner de la place le plus vite possible Compression ROW appliquée sur place
Limiter la perturbation Reconstruction en ligne (ONLINE)
Optimiser la vitesse Reconstruction hors ligne (OFFLINE)
Compression maximale sans interruption Compression PAGE en ligne, qui peut réduire le stockage de près de 90 % selon les données
Compression maximale, interruption acceptée Copie des données dans une table partitionnée avec un index columnstore clustered

Lister les tables compressées

SELECT DISTINCT t.name AS CompressedTables
FROM sys.partitions p
    INNER JOIN sys.tables t
        ON p.object_id = t.object_id
WHERE p.data_compression > 0;

Estimer le gain avant de compresser

La procédure sp_estimate_data_compression_savings estime la taille d'une table pour un type de compression donné, sans la modifier.

USE WideWorldImporters;
GO
EXEC sp_estimate_data_compression_savings 'Sales', 'Invoices', NULL, NULL, 'ROW';
GO

Pour une table de votre base :

EXEC sp_estimate_data_compression_savings 'dbo', 'MyTable', NULL, NULL, 'PAGE';

Tables les plus volumineuses

SELECT TOP 10 schema_name(tab.schema_id) + '.' + tab.name AS Table_Name,
    CAST(SUM(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS used_mb,
    CAST(SUM(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS allocated_mb
FROM sys.tables tab
    JOIN sys.indexes ind
        ON tab.object_id = ind.object_id
    JOIN sys.partitions part
        ON ind.object_id = part.object_id AND ind.index_id = part.index_id
    JOIN sys.allocation_units spc
        ON part.partition_id = spc.container_id
GROUP BY schema_name(tab.schema_id) + '.' + tab.name
ORDER BY SUM(spc.used_pages) DESC;
Mastodon LLIMastodon SocialFosstodon