Row and Page Compression: Estimating Savings Before You Compress

Storage savings need a workload check before they become a good change. Estimate page compression and row compression before rebuilding, then measure the result on representative data.

Understand the Two Rowstore Techniques

Row compression changes the physical representation of values to reduce avoidable storage. It does not change the declared logical data types or the values returned by queries. Savings depend on the actual values and row structure. Wide fixed-length definitions with smaller stored values can be candidates, while already compact data leaves less space to save.

Page compression incorporates row compression and additional prefix and dictionary techniques for repeated information on a page. Repetition makes that additional work useful, but the outcome depends on the stored distribution. It is not general file compression applied to the entire database. Out-of-row large-value storage has different coverage and should not be counted as automatically compressed by the rowstore setting.

Compression can reduce storage and the number of pages a query reads. It can also change CPU demand because SQL Server handles compressed representations. I compare the actual workload rather than choosing the setting from the table's name or age. An archive label does not guarantee repeated values, and a busy table does not automatically rule compression out.

Inventory the Current Compression by Partition

Compression is recorded at the partition level. A table can have a compressed clustered index while one or more nonclustered indexes use a different setting. Inspect every relevant index and partition before estimating the proposed change.

SELECT s.name AS SchemaName,t.name AS TableName,
       i.name AS IndexName,i.index_id,p.partition_number,
       p.rows,p.data_compression_desc
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
JOIN sys.indexes AS i ON i.object_id=t.object_id
JOIN sys.partitions AS p ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE t.is_ms_shipped=0
ORDER BY s.name,t.name,i.index_id,p.partition_number;

The partition row count is metadata rather than an exact business validation count. Use it to organize the review, then measure the storage and operations that matter. Distinguish heaps, clustered indexes, nonclustered indexes, and columnstore structures. This article's rebuild examples target ordinary rowstore data in a disposable database.

Record engine version and edition support for the environment before planning deployment. Also review the relevant object features and available rebuild options. An accepted setting on one server does not prove that a different build, edition, or specialized table supports the same deployment sequence.

Estimate Row and Page Compression Savings Separately

The estimator samples data and reports estimated sizes for the current and proposed compression. It performs work and uses temporary resources, so run it in an approved window or on a representative restored copy. The synthetic lab below provides a repeatable object without claiming a particular measured saving.

CREATE TABLE dbo.CompressionLab
(
    RecordID int NOT NULL PRIMARY KEY,
    RegionCode char(20) NOT NULL,
    StatusCode char(20) NOT NULL,
    Amount decimal(18,2) NOT NULL
);
INSERT dbo.CompressionLab(RecordID,RegionCode,StatusCode,Amount)
SELECT CONVERT(int,value),'North','Closed',CONVERT(decimal(18,2),value%100)
FROM GENERATE_SERIES(1,10000);
EXEC sys.sp_estimate_data_compression_savings
    @schema_name=N'dbo',@object_name=N'CompressionLab',
    @index_id=1,@partition_number=NULL,@data_compression=N'ROW';
EXEC sys.sp_estimate_data_compression_savings
    @schema_name=N'dbo',@object_name=N'CompressionLab',
    @index_id=1,@partition_number=NULL,@data_compression=N'PAGE';

The data generator requires SQL Server 2022 or later with compatibility level 160 or higher. Its repeated strings deliberately create a compressible pattern. They are test inputs, not evidence about your production data. Run separate estimates for the actual index or partition under consideration and retain each result with its capture time.

Compare estimated saved space with rebuild cost and expected workload behavior. A sample-based estimate can differ from the finished object. Do not convert it into a promised percentage reduction for the service. The estimator is a useful scout, but it does not sign the maintenance window.

Measure the Workload Before Rebuilding

Capture the relevant read and write operations, query plans, CPU, elapsed time, and logical reads on a representative copy. Include point lookups, reporting scans, inserts, updates, and index maintenance according to the table's role. Test the same data and query shapes after each proposed compression setting.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT RegionCode,SUM(Amount) AS TotalAmount
FROM dbo.CompressionLab
GROUP BY RegionCode;
SELECT RecordID,RegionCode,StatusCode,Amount
FROM dbo.CompressionLab
WHERE RecordID=5000;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

A scan benefiting from fewer pages does not prove that every write path remains acceptable. Conversely, some added compression work can be worthwhile when reduced reads dominate the workload. Keep the decision attached to measured service behavior and resource headroom. Compare repeated runs and account for cache state instead of relying on one elapsed-time observation.

Which operation becomes the limiting factor during the busiest period? Include that operation in the test rather than selecting only a convenient report. I retain both the space estimate and the workload comparison so the final decision explains the tradeoff. A storage-only report leaves the most useful part of the question unanswered.

Apply Page Compression With a Deliberate Rebuild

The following lab commands apply page compression to the table and separately to a nonclustered index. A table rebuild does not mean every independently stored nonclustered index has adopted the same compression. Recheck the partition inventory afterward.

ALTER TABLE dbo.CompressionLab
REBUILD WITH(DATA_COMPRESSION=PAGE);
CREATE INDEX IX_CompressionLab_Region
ON dbo.CompressionLab(RegionCode) INCLUDE(Amount);
ALTER INDEX IX_CompressionLab_Region ON dbo.CompressionLab
REBUILD WITH(DATA_COMPRESSION=PAGE);

For production, review locking, transaction log capacity, temporary space, duration, and the supported online or resumable options for the exact operation. Do not assume the illustrated rebuild is online. Arrange the change window and reversal plan using the actual deployment constraints. Changing back to NONE is another rebuild with its own space and operating cost.

Keep a separate pre-change backup and a tested recovery path for the maintenance operation. Reversal of the compression setting preserves logical data, but it does not resolve an unrelated failure during a long rebuild. Define the conditions for stopping the deployment, including blocking, log headroom, and service latency. Confirm who reviews those conditions and who can end the operation when the accepted maintenance boundaries are exceeded.

A partitioned archive can apply stronger compression to older partitions while leaving active partitions on a different setting. Confirm index alignment and maintenance scripts so later rebuilds preserve the intended policy. A maintenance command that resets compression accidentally can undo the accepted storage result without changing the application's logical rows.

Verify Space and Keep the Policy Visible

Measure allocated space after the rebuild and compare workload behavior with the recorded baseline. The following query reports page allocation per index for the lab object. It avoids treating all index storage as one unexplained table total.

SELECT i.index_id,i.name AS IndexName,
       SUM(p.reserved_page_count)*8.0/1024 AS ReservedMB,
       SUM(p.used_page_count)*8.0/1024 AS UsedMB
FROM sys.dm_db_partition_stats AS p
JOIN sys.indexes AS i ON i.object_id=p.object_id AND i.index_id=p.index_id
WHERE p.object_id=OBJECT_ID(N'dbo.CompressionLab')
GROUP BY i.index_id,i.name;

Document the selected settings, actual allocation, workload results, and required maintenance behavior. Revisit them when the data distribution or workload changes. Page compression is effective when the measured benefit justifies its operating cost, and that judgment belongs to the object and workload being tested.

Related reading on this blog: Reducing the Size of a Reporting Database and Why Table Size Numbers Disagree: Reserved, Used and Unused Space.

Compression is not free storage, it is a physical design choice with a measurable space and processing tradeoff.

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