[Compression] Seeing how much savings you'll get

If you meet all sorts of criteria (SQL Server 2008, Enterprise Edition) then you can use row or page level compression on your tables. But, you ask: will it make a difference in space used?

Fortunately, and a bit surprisingly, Microsoft came up with a way to do so.
sp_estimate_data_compression_savings

Sample usage:

sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  '20090820__abc' 
     , @index_id = NULL             --NULL does all.
     , @partition_number = null     --if you use partitioned tables
     , @data_compression = 'PAGE'   --can also use ROW or NONE
go

It looks like it takes roughly 40mb of data, copies it to TEMPDB, and compresses that. It then returns the results, including comparing the size to what the current compression is.

For us, mixed results. One set of EDI data, which uses certain characters to split out values, gets roughly 25% compression. A different set of EDI data that uses XML (with huge swathes of repeating data) get a whopping 1% savings.

I love the idea. I want to use it everywhere - since most systems are IO bound (not CPU bound) it seems a home run. But the requirement of Enterprise Edition lessens its usefulness by a _lot_.