Page Compression

Andy Mallory continues his discussion of compression options:

You can think of page compression as doing data deduplication within a page. If there is some value repeated in multiple spots on a page, then page compression can store the repetitive value only once, and save some space.

Page compression is actually a process that combines three different compression algorithms into a bigger algorithm. Page compression applies these three algorithms in order:
1) Row compression
2) Prefix compression
3) Dictionary compression

Page compression is my go-to compression option, typically.  There are some cases in which it doesn’t work well, so check beforehand (start with sp_estimate_data_compression_savings), but I’ve had good luck with page compression.

Related Posts

Accelerated Database Recovery and Filegroups

Randolph West shows a change to Accelerated Database Recovery in SQL Server 2019 CTP 3.2 and later: ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling […]

Read More

Change Tracking in SQL Server

Tim Weigel covers the basics of change tracking in SQL Server: There aren’t a lot of parameters here. You can set change tracking on or off, you can specify your retention period, and you can specify whether to enable auto-cleanup or not. For the retention period, you have the choice of DAYS, HOURS, or MINUTES. […]

Read More

1 Comment

  • Andy Mallon on 2016-02-23

    sp_estimate_data_compression_savings is a great tool–I’ll tackle it in the near future in another post. Since it relies on sampling, it’s not perfect.

    Just like statistics, if you get a sample that doesn’t represent the entire table, the estimate could be off.

    Trust, but verify!

Comments are closed

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29