I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community. I have read several articles, but wanted to have a place where this addressed in DB Administrators.


2 Answers 2


This is a good reference and explanation for row v page (no pun intended) compression: http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/how-sql-server-data-compression/

In short though, page compression encompasses the algorithms contained with row level compression and then also covers Prefix compression and Dictionary compression. Prefix and dictionary compression finds patterns in the data and replaces them with smaller values. During decompression it will do the reverse and restore the data to its original values.

Row level makes easier compression decisions, like reducing metadata, and removing unused space, like the letter 'Y' stored in a CHAR(1000).

The article referenced explains this in much greater detail.


In order to make a decision for bunch of tables you can run procedure "sp_estimate_data_compression_savings":

exec sys.sp_estimate_data_compression_savings  @Schema,@Table,NULL,NULL,ROW;
exec sys.sp_estimate_data_compression_savings  @Schema,@Table,NULL,NULL,PAGE;

That will help you determine savings for each particular table.

From my experience PAGE compression works better in most of the cases.

  • except that sp_estimate_data_compression_savings base its extimate on 5000 rows only, with no way I could find to change it. So I ended up trying out Rebuild with (data_compression=row and page) on a test server, over the weekend. Commented Dec 6, 2021 at 16:42
  • 1
    @HenrikStaunPoulsen you can change the sample size in the procedure itself declare @pages_to_sample int = 5000;
    – pix1985
    Commented Jun 1, 2023 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.