I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month.

I now would expect SQL Server to apply the page compression. However using sp_estimate_data_compression_savings it doesn't look like it. (I would have savings of around 40%-50%.)

Did I miss something in configuring page compression?

The problem with this is that I have now large files, 70-80 GB per partition. When I compress them, they are half empty. Regaining this disk space is impossible because if I try to shrink the data file that is behind the partition I end up with a fragmented clustered index.

How can I avoid this disk space waste party?

  • 1
    Hi in DMV sys.dm_db_index_physical_stats we have compressed_page_count column which tells how many pages are being considered as being suitable for compression. You can enable page compression but unless DB engine find it suitable for compression it wont compress the poage only metadata will show compressed.Read this resource:msdn.microsoft.com/en-us/library/cc280464.aspx
    – Shanky
    Jul 25, 2014 at 9:27
  • Based on your information I had a look and things look like this: Partition 45 Size with current compression settings 54642000 KB Size with requested compression settings 29561320 KB page_count 6792353 compressed_page_count 6354979 So I would expect SQL Server 2012 to compress these pages, but for some reasons it doesn't do it. This behaviour will cost me around 10-15 TB disk space wasted (since the db runs in an Avalability Group with 3 nodes). :(
    – nojetlag
    Jul 28, 2014 at 6:17
  • Are you using TDE? I know that it causes backup compression to be less effective. Maybe it would hamper page/row compression as well. Source Feb 11, 2015 at 23:41
  • no not using TDE at all.
    – nojetlag
    Feb 13, 2015 at 15:12

2 Answers 2


If you're using big fields like VARCHAR(MAX), you'll discover that off-page data isn't compressed with page compression. (Hey, go figure.)

To see how much data of your table is being stored off-page, use the free open source script sp_BlitzIndex (disclaimer: I'm a coauthor on that). You can run it at the table level like this:

EXEC sp_BlitzIndex @DatabaseName = 'mydb', @SchemaName = 'dbo', @TableName = 'whywontthiscompress'

Then, in the top result set where it lists the indexes, look at the amount of LOB data stored on the table. If it's a lot, that's probably why compression isn't working. (And you can post a screenshot of the top result set in your question if you'd like more help/clarifications on this.)


From Page Compression Implementation:

However, the metadata for the table indicates that page compression should be used. As data is added to the first data page, data is row-compressed. Because the page is not full, no benefit is gained from page compression. When the page is full, the next row to be added initiates the page compression operation. The whole page is reviewed; each column is evaluated for prefix compression, and then all columns are evaluated for dictionary compression.

Look on the below link



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.