Data compression is one of my favorite new SQL Server 2008 features. For high-transaction databases, it can dramatically cut the I/O and make SAN shadow copy backups smaller.

As cool as data compression is,  enabling compression means running the compression wizard or alter table/index command for each individual object or index. The good news is that Microsoft has exposed the system stored procedure that estimates the potential gain using compression.

I’ve just posted on www.SQLServerBible.com a script that includes two stored procedures.

db_compression_estimate estimates the row and page compression gain for every object and index in the database.

db_compression (@minCompression) will check the current compression setting and compare it to potential row and page compression gains. If the compression should be adjusted it performs the alter command. The default @minCompression setting is 25%.

If the estimated gain is equal to or greater than @minCompression parameter then it enables row or page compression whichever is greater gain. If row and page have same gain then it enables row compression.

If estimated gain is less than @mincompression parameter then compression is set to none.

disclaimer: This is a first version of these stored procedures. This version has no error trapping. Also there’s a known issue when using Insert..exec with sp_estimate_data_compression_savings that generates a transaction error. The script was developed for CPT6. please email any issues to pauln@sqlserverbible.com.