Sql-server – Page compression not working

compressionsql serversql-server-2012

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?

Best Answer

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

Related Solutions

Sql-server – Best Practice in storage allocation for compressed and partitioned tables

Rebuild the index to write out all pages freshly in the optimal way.

If you want to absolutely minimize space usage, specify ONLINE = OFF. Online operations add a small amount of per-row space overhead.

MAXDOP = 1 can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save.

If the target file of the rebuild already contains fragmented freespace the newly built index/partition can turn out to be fragmented immediately. Consider building into an empty target filegroup that is pre-sized to approximately the right size.

Compressing partition – what about index

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use:

ALTER TABLE table_name MOVE partition TEST_TAB_2014 COMPRESS;

And then rebuild(not recreated) all indexes manually, preferably with reasonable parallel option.

PS: you should also read something about various compression options Oracle offers. Unfortunately it is not simple from both technical and also licensing perspective.

Briefly, Oracle supports these compression options:

BASIC, offline, block level. Usually the bigger the tablespace's block size the better compression ratio. This one is used only when moving tables/partitions, or when using direct path load (/* +APPEND */ hint)
OTLP, online, row level, being part of advanced compression extra cost option.

ALTER TABLE … COMPRESS FOR OLTP  This approach will enable OLTP Table Compression for all future DML -- however, the existing data in the table will remain uncompressed.

ALTER TABLE … MOVE COMPRESS FOR OLTP  This approach will enable OLTP Table Compression for future DML and also compress existing data.

COLUMNAR, works on column level, this one can also used for LOB compression. You can use it only on Exatada or when you use some SAN/NAS storage from Oracle.

Best Answer

Related Solutions

Sql-server – Best Practice in storage allocation for compressed and partitioned tables

Compressing partition – what about index

Related Question