Recompress Heaps

Heaps, or tables without a clustered index, suffer from a little-known problem when configured with page compression. Page compression only takes place once a page is 100% full, i.e. when no more rows can be added to the page. However, since the page header for heap pages isn’t reliably updated to indicate the remaining free space on the page, most pages in a heap will never become 100% full, and hence page compression will never run on those pages. For a table configured with page compression, row compression is actually used for each row added to the page. Once there is no more room for new rows on the page, SQL Server uses page compression on the page. Solomon Rutzky’s answer on has some great details about heap compression.

Bellotto Cracow Suburb as seen from the Cracow Gate

Bellotto Cracow Suburb as seen from the Cracow Gate

The script below allows you to recompress heaps; it automates a REBUILD action for page-compressed-heaps that have a minimum percentage of compressed pages in the heap. You can configure a cut-off, such that heaps where 90% of the pages are page-compressed will not be rebuilt, but any heap with less than that will be rebuilt. The act of rebuilding a page-compressed heap automatically fills each page to capacity, which causes page compression to take place. As an aside, the rebuild operation for a heap also triggers a rebuild of all non-clustered indexes defined on the table, thereby causing any page-compressed non-clustered index to be re-compressed, which may result in further space savings.

Here’s the script:

For a script that compresses tables over a certain size where compression would be beneficial, check this prior post.

Let me know if you have any questions or suggestions about this script that can be used to automatically recompress heaps. Also, check out the rest of our SQL Server Tools.

If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.