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;