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

Database Migration With dbatools

Jess Pomfret shows how easy it is to migrate databases from one SQL Server instance to another using dbatools: Now that there are no connections we can move the database.  Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the […]

Read More

Working With Azure SQL Managed Instances

Jovan Popovic has a couple of posts covering configuration for Azure SQL Managed Instances.  First, he looks at how to configure tempdb: One limitation in the current public preview is that tempdb don’t preserves custom settings after fail-over happens. If you add new files to tempdb or change file size, these settings will not be preserved after fail-over, and […]

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


February 2016
« Jan Mar »