Actual Page Data Compression Results in SQL Server 2008

We have a table with a little over 4 billion rows that uses SQL Server 2008 Page data compression. With Page data compression, the table takes up 3.8TB. If I were to remove the data compression, we would need about 4.6TB of space for the same table. This is about a 16% space savings (which is not the best I have ever seen), but this is the largest table that I have used data compression on in a production environment.

BTW, this database lives on a little Dell PowerEdge 1950, with one quad-core Xeon 5440 CPU and 32GB of RAM, connected to a 3PAR S400 SAN.

This entry was posted in SQL Server 2008. Bookmark the permalink.

3 Responses to Actual Page Data Compression Results in SQL Server 2008

  1. Michael says:

    Just to share our results of Page compression, some tables I\’m working on are in the hundreds of GB range upwards of a TB. We\’ve seen 90% savings. The tables exclusively have numeric values and are pretty narrow. I think this shows that the effectiveness of the compression is very dependent on the table definition and content. In our system Row compression did not compress as much as Page did. Also we tested performance of DML statements. INSERTs are running 40% slower, SELECT performance is affected by the # of rows selected. When selecting small number of rows in part of complex queries performance is same or slightly slower, When # of rows increases (millions) performance is faster than uncompressed data. Just wanted to share our results of the same.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s