Predicting the performance of database (table/index) compression

SQL Server row or page level compression is a very handy option in many cases. However it will be nice if we can predict what will be exact space saving before we do any change in a production table. In other way, this is an answer to the below question:

How can we estimate (predict) the savings from compression?

Use below SP to estimate the savings from SQL Server compression:

sp_estimate_data_compression_savings 
     [ @schema_name = ] 'schema_name'  
   , [ @object_name = ] 'object_name' 
   , [@index_id = ] index_id 
   , [@partition_number= ] partition_number 
   , [@data_compression= ] 'data_compression' 
[;]


Example:

We are testing the compression performance of table named “dbo.test” from database “testdatabase” and we are using page level compression on entire table.

use testdatabase
EXEC sp_estimate_data_compression_savings 'dbo','test',null,null,'page'

Example output:



object_name                                                                                                                      schema_name                                                                                                                      index_id    partition_number size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB) sample_size_with_current_compression_setting(KB) sample_size_with_requested_compression_setting(KB)
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- ----------------------------------------- ------------------------------------------- ------------------------------------------------ --------------------------------------------------
test                                                                                                         dbo                                                                                                                              1           1                57087872                                  28594376                                    40744                                            20408

From this example, the estimated compression is around 50% (28594376/57087872)

For more details on this stored procedure, please refer to http://msdn.microsoft.com/en-us/library/cc280574.aspx

Comments