Estimate Compression Savings for Every Table in a Database

To estimate compression savings for one table, call sp_estimate_data_compression_savings. To cover a whole database, loop over the tables and rank the results. The loop shows which tables repay compression and which do not.

What the Procedure Does

To estimate compression savings, the procedure copies a sample of the table into tempdb. It compresses the sample and scales the result up. It never changes your table.

You name a schema, a table, an index, a partition and the compression type. NULL for the index and the partition means all of them. The type can be NONE, ROW or PAGE, and on SQL Server 2019 and later also COLUMNSTORE and COLUMNSTORE_ARCHIVE. Row compression stores fixed-length values in fewer bytes. Page compression adds prefix and dictionary storage on top, so it saves more and costs more CPU.

One call handles one table. A database has many, so estimate compression savings in a loop. Sampling reads the table and writes to tempdb, which makes it heavy on a big table. Run it in a quiet hour, and skip the small tables.

Build Three Different Tables

The demo database holds three tables. TeaOrders repeats the same words in every row. ScanLog stores random numbers, a random identifier and random bytes. TeaTypes has three rows. Each shape compresses differently, and that is the point.

IF DB_ID(N'SavingsScanDemo') IS NULL CREATE DATABASE SavingsScanDemo;
GO
USE SavingsScanDemo;
GO
DROP TABLE IF EXISTS dbo.TeaOrders, dbo.ScanLog, dbo.TeaTypes;
CREATE TABLE dbo.TeaOrders (OrderID int IDENTITY(1,1) PRIMARY KEY, TeaName nvarchar(40) NOT NULL, Region nvarchar(40) NOT NULL, Notes nvarchar(100) NOT NULL, Quantity int NOT NULL);
CREATE TABLE dbo.ScanLog (LogID int IDENTITY(1,1) PRIMARY KEY, Token uniqueidentifier NOT NULL, Reading float NOT NULL, Payload varbinary(64) NOT NULL);
CREATE TABLE dbo.TeaTypes (TypeID int PRIMARY KEY, TypeName nvarchar(40) NOT NULL);
INSERT INTO dbo.TeaTypes VALUES (1, N'Green'), (2, N'Black'), (3, N'Herbal');
INSERT INTO dbo.TeaOrders (TeaName, Region, Notes, Quantity)
SELECT CHOOSE(n % 3 + 1, N'Green Sencha', N'Black Assam', N'Herbal Mint'), CHOOSE(n % 2 + 1, N'Oregon', N'Vermont'), N'Standard monthly delivery, no changes this month', n % 12 + 1
FROM (SELECT TOP (60000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
INSERT INTO dbo.ScanLog (Token, Reading, Payload)
SELECT TOP (60000) NEWID(), RAND(CHECKSUM(NEWID())) * 1000000, CRYPT_GEN_RANDOM(64)
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

Loop Over Every Table

The script keeps tables of at least 100 pages. A cursor visits each one and asks for ROW and then PAGE. Every answer lands in a temp table, and a final query adds up the indexes of each table. The temp table lists ObjectName before SchemaName, because the procedure returns its columns in that order. Swap them and the names land in the wrong columns.

SET NOCOUNT ON;
DECLARE @MinPages int = 100;
CREATE TABLE #Savings (
    ObjectName sysname, SchemaName sysname, IndexID int, PartitionNumber int,
    CurrentKB bigint, RequestedKB bigint, SampleCurrentKB bigint, SampleRequestedKB bigint, Mode varchar(10) NULL);
DECLARE @schema sysname, @table sysname, @mode varchar(10);
DECLARE tbl CURSOR LOCAL FAST_FORWARD FOR
    SELECT s.name, t.name
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE (SELECT SUM(ps.in_row_used_page_count) FROM sys.dm_db_partition_stats AS ps WHERE ps.object_id = t.object_id) >= @MinPages;
OPEN tbl;
FETCH NEXT FROM tbl INTO @schema, @table;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @mode = 'ROW';
    INSERT INTO #Savings (ObjectName, SchemaName, IndexID, PartitionNumber, CurrentKB, RequestedKB, SampleCurrentKB, SampleRequestedKB)
        EXEC sys.sp_estimate_data_compression_savings @schema, @table, NULL, NULL, @mode;
    UPDATE #Savings SET Mode = @mode WHERE Mode IS NULL;
    SET @mode = 'PAGE';
    INSERT INTO #Savings (ObjectName, SchemaName, IndexID, PartitionNumber, CurrentKB, RequestedKB, SampleCurrentKB, SampleRequestedKB)
        EXEC sys.sp_estimate_data_compression_savings @schema, @table, NULL, NULL, @mode;
    UPDATE #Savings SET Mode = @mode WHERE Mode IS NULL;
    FETCH NEXT FROM tbl INTO @schema, @table;
END;
CLOSE tbl;
DEALLOCATE tbl;
SELECT SchemaName, ObjectName, Mode, SUM(CurrentKB) AS CurrentKB, SUM(RequestedKB) AS EstimatedKB,
       CAST(100.0 * (SUM(CurrentKB) - SUM(RequestedKB)) / NULLIF(SUM(CurrentKB), 0) AS decimal(5, 1)) AS SavedPercent
FROM #Savings
GROUP BY SchemaName, ObjectName, Mode
ORDER BY SUM(CurrentKB) - SUM(RequestedKB) DESC, Mode;
DROP TABLE #Savings;

The list is sorted by kilobytes saved, so the best candidate comes first. TeaOrders shrinks by 92 percent with PAGE and by 46 percent with ROW. ScanLog saves under one percent either way, because random data has no pattern to remove. TeaTypes is missing, because 16 KB is far below the 100 page limit. Compression there would save nothing worth a rebuild.

A real table sits between these two extremes. The repeated words make TeaOrders a best case, so expect smaller numbers on your data.

The final query adds up all indexes of a table. To judge one index, read the temp table before the final query. The IndexID column is 0 for a heap, 1 for a clustered index and 2 or more for the others. Compression is available in every edition from SQL Server 2016 SP1 onward. Earlier versions limit it to Enterprise.

Check the Estimate Against the Real Thing

An estimate comes from a sample, so test it once. This block reads the used space of TeaOrders, rebuilds it with PAGE compression, and reads the space again.

SELECT OBJECT_NAME(object_id) AS TableName, SUM(used_page_count) * 8 AS UsedKB FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.TeaOrders') GROUP BY object_id;
ALTER TABLE dbo.TeaOrders REBUILD WITH (DATA_COMPRESSION = PAGE);
SELECT OBJECT_NAME(object_id) AS TableName, SUM(used_page_count) * 8 AS UsedKB FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.TeaOrders') GROUP BY object_id;

The estimate said 768 KB and the table now uses 720 KB. The size before matches CurrentKB from the loop. In this demo the estimate was close and a little high. These sizes come from my test server. A second server gave 816 KB after the rebuild, so the sizes differ a little by server. To undo the change, rebuild with DATA_COMPRESSION = NONE.

This rebuild changes only the heap or the clustered index. The loop adds up every index of a table, so a table with nonclustered indexes needs more. To compress every index of the table, use ALTER INDEX ALL ON dbo.TeaOrders REBUILD WITH (DATA_COMPRESSION = PAGE);. To judge one index, read its row in the temp table. In a test with a clustered key and one nonclustered index, ALTER TABLE ... REBUILD left the nonclustered index at NONE, and ALTER INDEX ALL gave PAGE for both.

Row, Page or Neither

Compression trades CPU for space. Page compression saves more and works harder on every write, so tables that change all day can suit ROW better. Tables that are mostly read are good PAGE candidates. Treat that as a starting point and measure your own workload. The post Page and Row Compression in SQL Server: Estimate, Enable, Remove times the CPU cost. It also shows how to remove compression again.

One table, index or partition takes one setting at a time. You cannot apply ROW and PAGE together, but two indexes of the same table can differ. Two more request types ask about a different design. After the rebuild above, ask for COLUMNSTORE on the same table. On SQL Server 2025 it returned 720 KB now and 200 KB requested. That estimate is for a different design, so read it as a hint, not a plan.

EXEC sys.sp_estimate_data_compression_savings N'dbo', N'TeaOrders', NULL, NULL, N'COLUMNSTORE';

You could argue that estimates are a waste of time and that you should compress every table. A table like ScanLog answers that. It would pay a rebuild and extra CPU for less than one percent. The estimate costs a few minutes and tells you which tables to leave alone.

What to Remember

Estimate compression savings table by table, with ROW and PAGE, and rank the results by kilobytes saved. Skip small tables and tables that save little. Check one estimate against a real rebuild, then measure CPU before you compress a busy table. Remove the demo database when you finish.

USE master;
GO
ALTER DATABASE SavingsScanDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SavingsScanDemo;

A compression estimate is not a promise, it is a sample that tells you where not to waste a rebuild.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.