SQL Server table compression: choose the method and estimate the savings
Choosing a SQL Server compression method (ROW, PAGE, columnstore), listing compressed tables and estimating the savings before compressing.
Compression reduces the space used by tables and their indexes. The right choice depends on the goal: save space quickly, limit disruption, or reach the highest compression ratio. For more background, see the SQLPerformance article on columnstore compression and the SQL Server Science article on compressing big tables.
Choose the method
| Goal | Method |
|---|---|
| Save space as quickly as possible | ROW compression applied in place |
| Limit disruption | Online rebuild (ONLINE) |
| Optimize speed | Offline rebuild (OFFLINE) |
| Maximum compression without interruption | PAGE compression, online, which can reduce storage by close to 90% depending on the data |
| Maximum compression, interruption accepted | Copy the data to a new partitioned table with a clustered columnstore index |
List the compressed tables
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;
Estimate the savings before compressing
The procedure sp_estimate_data_compression_savings estimates the size of a table for a given compression type, without changing it.
USE WideWorldImporters;
GO
EXEC sp_estimate_data_compression_savings 'Sales', 'Invoices', NULL, NULL, 'ROW';
GO
For a table in your own database:
EXEC sp_estimate_data_compression_savings 'dbo', 'MyTable', NULL, NULL, 'PAGE';
Largest tables
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;