5

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?

Improve this question
4
  • 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 '14 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 '14 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 – Erik Darling Feb 11 '15 at 23:41
  • no not using TDE at all. – nojetlag Feb 13 '15 at 15:12
3

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.)

Improve this answer
-1

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

http://blog.sqlauthority.com/2010/03/01/sql-server-data-and-page-compressions-data-storage-and-io-improvement/

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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