SQL Server Enterprise Features – Data Compression

In the world of big data, we are always trying to lighten our storage footprint.  Luckily for us, Microsoft has introduced data compression as an enterprise-level feature to aid in conserving storage.  Not only are you able to save on storage, you will also dramatically reduce the number of I/O requests.  Knowing that the disk subsystem is the slowest part of our environments, these fewer I/O requests needed for retrieving data will lead to an increase in performance. 

SQL Server offers two basic types of compression in row and page compression.  Without going into the inner workings of each, row compression is best for write-intensive tables whereas page compression is best for read-intensive tables.

Let’s take a look at both types of data compression using the largest table in the AdventureWorks2012 database from a data standpoint in the Person table.  We will start by capturing the data space footprint before we start any work.

Corey_BlogPost5_Pic1.png

Before we jump right into enabling either row or page compression, we can actually estimate the savings of each to determine which will provide us with the most savings on storage.  Since page compression includes row compression, we will start with row compression and the estimated savings.

EXEC sp_estimate_data_compression_savings
     ‘Person’,‘Person’,null,null,‘row’

Corey_BlogPost5_Pic2.png

USE [AdventureWorks2012]
ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)

Corey_BlogPost5_Pic3.png 

The actual savings is very close to the estimated and proved to provide little benefit in regards to storage with an ~2% gain.

Now we will move onto the estimate in comparison to actual for enabling page compression on the Person table.

EXEC sp_estimate_data_compression_savings
    ‘Person’,‘Person’,null,null,‘page’

 

Corey_BlogPost5_Pic4.png

Now that’s the savings on storage we are looking for, but is the page compression estimate as accurate as the row compression estimate?

USE [AdventureWorks2012]
ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)

Corey_BlogPost5_Pic5.png

 

BINGO!  The estimated savings in comparison to the actual savings again was very close, but unlike row compression, page compression provides a much bigger benefit in regards to storage with an ~40% gain!

If you find that data compression doesn’t work for you or your specific scenario during testing, you can easily remove it.

USE [AdventureWorks2012]
ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = NONE
)

As mentioned previously, utilizing the enterprise-level feature of data compression can drastically cut down on storage and I/O requests needed to retrieve data leading to increased performance.  However, these reductions are typically at the cost of added CPU load on the system.  For this reason, I strongly advise thorough testing before any production implementation. 

Thanks for reading. 

Leave a Reply

Your email address will not be published. Required fields are marked *