LLI Wiki — My Digital Knowledge Base

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;
Mastodon LLIMastodon SocialFosstodon