Data Compression in SQL Server 2008

Estimate the Savings in Storage Space Using Inbuilt Stored Procedure..

sp_estimate_data_compression_savings inbuilt system stored procedure gives the DBA the facility of estimating the saving in storage space . The mentioned TSQL Query shows the space saved when Row Level Compression is enabled for Production.TransactionHistoryArchive Table of AdventureWorks Database.

The attached SQL code snippet shows that there are columns namely size_with_current_compression_settings (KB) and size_with_requested_compression_settings (KB) which provides you an estimate on the amount of disk space which you can save for Production.TransactionHistoryArchive table.

Estimate the Savings in Storage Space Using Data Compression Wizard
SQL Server 2008 inbuilt wizard Data Compression Wizard provides the facility of estimate the savings of storage space when compression is enabled for a particular table or index in a database.

To do that:

• Click on the AdventureWorks and under the tables find "Production.TransactionHistoryArchive".
• Right Click on the Production.TransactionHistoryArchive and then click on "Storage" and then "Manage Compression".
• Click on next

• After that click on "Row" from the Compression type drop down list.

• Select the compression type option as Row and click the calculate button on the wizard, it will calculate the amount of storage space required when row compression type is used.

• Click Next to go to; Select an Output Option Page. There are many options such as create a script, run immediately, or create a Schedule. Click Next to continue with the wizard.

• Finally Click on Finish.