Data Compression in SQL Server 2008
Estimate the Savings in Storage Space Using Inbuilt Stored Procedure
A DBA can estimate the saving in storage space using sp_estimate_data_compression_savings inbuilt system stored procedure. The below mentioned TSQL Query will let us know the space saved when Row Level Compression is enabled for Production.TransactionHistory Table of AdventureWorks Database.
@schema_name = ‘Production’,
@object_name = ‘TransactionHistory’,
@index_id = NULL,
@partition_number = NULL,
@data_compression = ‘ROW’ ;
In the above snippet you could see 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.TransactionHistory table. In this example there is no fragmentation for the Production.TransactionHistory table.
Estimate the Savings in Storage Space Using Data Compression Wizard
In SQL Server 2008 there is an inbuilt wizard namely Data Compression Wizard which enables you to estimate the savings in storage space when compression is enabled for a particular table or index in a database.
A DBA needs to first get connected to the SQL Server 2008 Instance and then expand the Databases Node | AdventureWorks Database | Tables, and right click Production.TransactionHistory table, select Storage and finally select Manage Compression as shown in the below snippet.
This will launch Data Compression Wizard Screen. Click Next to continue
In the Select Compression Type Page, click the drop down list for the compression type column. This will show
you the three compression type options namely None, Row and Page.
Different Compression Type Options
Once you select the compression type option as Row and click the calculate button on the wizard, it will calculate and let you know the amount of storage space required when row compression type is used.
Storage space required when row compression is used as compression type
If you select the compression type option as Page and click the calculate button on the wizard, it will calculate and let you know the amount of storage space required when page compression type is used.
Storage space required when page compression is used as compression type
Click Next to go to; Select an Output Option Page, in this screen there are many options available for the DBA to either create a script, or to run immediately, or to create a Schedule to run as a SQL Server Agent Job. The script generated can be included later in a database maintenance job which does the regular maintenance activity for the database. Click Next to continue with the wizard.