How to Estimate Space Saved by Data Compression in SQL Server? – Interview Question of the Week #167

Question: How can I estimate the space savings from ROW or PAGE compression before changing a SQL Server table?

Answer: I remember being asked this almost every day when disk space was expensive. Storage may be cheaper now, but a smaller table can still affect I/O, memory use, CPU and maintenance time. An estimate is more useful than choosing a compression setting by habit.

SQL Server provides sys.sp_estimate_data_compression_savings. Here it is for the Sales.InvoiceLines table in WideWorldImporters, once for ROW and once for PAGE:

USE WideWorldImporters;
EXEC sys.sp_estimate_data_compression_savings
    @schema_name = N'Sales',
    @object_name = N'InvoiceLines',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = N'ROW';

EXEC sys.sp_estimate_data_compression_savings
    @schema_name = N'Sales',
    @object_name = N'InvoiceLines',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = N'PAGE';

Compare size_with_current_compression_setting(KB) with size_with_requested_compression_setting(KB) in each result. The procedure samples data and builds an equivalent sample in tempdb; it estimates storage, it doesn’t apply compression. An estimate may show little saving or even growth. Run it when the required permissions and tempdb capacity are available, and test CPU and query behavior before a production rebuild.

Notice index 6: both requested settings estimate a larger size than its current format. That’s a reason to inspect the existing index and compression before changing it. The procedure didn’t rebuild these indexes; it produced estimates from samples.

The procedure also accepts NONE to estimate an uncompressed state, and newer versions support other compression types. My answer to the interview question is simple: measure the specific table and indexes first, then decide whether the storage estimate and the workload justify the change. What surprised you most when you compared ROW and PAGE estimates?

Compression is not free space, it is a trade of CPU for pages, so estimate before you rebuild.

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