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

Finding Windows Version With T-SQL

Jack Vamvas shows us several methods to figure out which version of Windows you have installed from within SQL Server: Method 2 : Use xp_cmdshell – although this does mean enabling xp_cmdshell , which is in many organisations as security violation  exec master..xp_cmdshell 'systeminfo' Click through for several less controversial methods.

Read More

Azure SQL Database and Extended Events

Dave Bland shows how to set up and read an extended event file on Azure SQL Database: This first step when using T-SQL to read Extended Files that are stored in an Azure Storage Account is to create a database credential.  Of course the credential will provide essential security information to connect to the Azure […]

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