Data Compression in SQL Server

In this blog we’ll share the benefits of Data Compression, what Data Compression is/isn’t, as well as some common compression scenarios. There are a few things to consider before starting Data Compression; and today we will share some of our implementation considerations.

Saving space and saving money are the two main benefits of SQL Server’s data compression while also being one of the more easily adaptable features. Data compression can improve the performance of workloads by being capable of storing more data pages into SQL Server memory space (the Buffer Pool)

SQL Server provides two levels of data compression: row and page compression. Row compression stores data more efficiently by using the least amount of bytes to store specific values.

• All numeric data types like int, float, decimal and money are converted into variable length data types.
• Char and Nchar data types are stored as variable length data types.
• NULL and 0 values are optimized and no space consumed.
• The metadata of records are reduced.

Page Compression is a superset of row compression that optimizes multiple rows in a page by minimizing the data redundancy. Using prefix and dictionary compression to look for common patterns in the beginning of the column values and the exact value matches across all columns and rows on each page. Prefix and dictionary compression are type-agnostic and shows every column as a bag of bytes.

Data Compression can apply to:

• Heaps
• Clustered indexes
• Non-clustered indexes
• Partitions (tables and indexes)
• Indexed views

Note- Large object are not compressed (MAX strings, LOBs, BLOBs, etc.)

Data compression can be applied flexibly and does not to be one method applied across the board. By this we mean one should use the compression scenario that is more beneficial for each index, heap, or partition. Log tables, audit tables, and data warehouse fact tables should be the first targets for implementing data compression.

Only store what you NEED! While Data compression is a way to store more that does not mean to store everything and always remember to prune data once it’s no longer useful. Before using data compression a company should ensure their maintenance scripts support compression, ensure the company has enough space for the uncompressed/compressed objects, and know how to monitor for performance impacts.

Ready to start data compression? Let’s do it! Here are some implementation tips:

• Initial compression during your maintenance window
• Compress a few objects at a time
• Propagate a few compression changes all the way to prod before starting another set
• Look for uncompressed pages

Post a comment or click here! to watch the full Data Compression webinar.