SQL Server 2008 (Enterprise Edition) offers row and page compression, which can potentially offer a boost in disk I/O in return for giving up some CPU resources. While this feature has lots of potential, one of the greatest difficulties in using it is how to determine which tables and indexes should be compressed (if any), and if they should be compressed using either row or page compression.

While I have not seen a great solution for this problem as of yet, I did run across a couple of stored procedures that are a good start.

Paul Nielson, the author of the SQL Server 2008 Bible (to be released in early 2009) has written a couple of stored procedure that I have found to be useful.

The first one is called db_compression_estimate, which goes through the database and estimates the amount of compression that can be gained from turning on either row or page compression.

The second one is called db_compression, and it actually implements row or page compression, depending on criteria you choose. For example, if you only want to compress tables and indexes that offer a 25% or greater amount of compression, the stored procedure will only turn on compression for those tables or indexes that meet this criteria, using the compression method (row or page) that offers the best compression level. In addition, if row and page compression offer identical compression levels (which is sometimes the case), then it will choose row compression over page compression in order to reduce compression overhead.

If you are considering implementing data compression in SQL Server 2008, you should spend a few moments reviewing these stored procedures, as they could end up saving you a lot of time. Even if you don't want to use these stored procedures directly, reviewing the code can teach you a lot about how to write your own similar stored procedures.

You can find the stored procedures here: http://www.sqlserverbible.com/scripts.htm.

Share this post :