Row and Page Level Compression in SQL Server 2008
A new type of data compression was introduced in SQL Server 2008, available in Enterprise and Datacenter editions. Compression yields performance gains, as both storage and memory requirements are reduced due to the data being compressed both on disk on in the data cache. I/O is reduced by there being less data to actually have to read; however, this benefit comes at extra CPU overhead, but the net gain should be an overall performance boost.
What types of objects can be compressed? Tables, nonclustered indexes, and indexed views.
There are two basic types of compression in SQL Server 2008:
Row-Level Compression: not exactly true data compression. Savings are achieved here by employing a more efficient storage format for fixed-length datatypes. For example, the int datatype requires 4 bytes of storage, regardless of how much data is actually stored in the field, NULLs included. Row-level compression allows the usage of only the actual amount of storage required. Less compression is achieved by row-level compression then by page-level, but less CPU overhead is incurred as well.
To enable row-level compression on a particular table:
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)
Page-Level Compression: True data compression. Data is compresses by storing repeating values or common prefixes only once. When page-level compression is employed, row-level compression is switched on as well. The nature of the data stored in the object you are compressing will define how much the data is actually compressed. If there is a good deal of repeating data, compression will be highly effective.
To enable page-level compression on a particular table:
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=PAGE)
Page compression is applied only after a data page is full, while row-level compression is applied immediately.
Is data compression right for you?
To determine how a table will look post-compression, SQL Server 2008 provides a stored procedure to calculate the potential effects of compression by sampling 5,000 data pages of the object in question. (This works by copying the candidate pages to tempDB, compressing them, and reporting the results back to the user).
[ @schema_name = ] ‘schema_name’
, [ @object_name = ] ‘object_name’
, [@index_id = ] index_id
, [@partition_number = ] partition_number
, [@data_compression = ] ‘data_compression’
Either ‘ROW’ or ‘PAGE’ may be specified for the @data_compression parameter.
A sample call:
use myDatabase
go
exec sp_estimate_data_compression_savings ‘dbo’, ‘myTable’, null, null, ‘PAGE’
go
Implementing via Management Studio:
Right click on the table you would like to compress, go to Storage–>Manage Compression
The next page will allow you to select the compression type, and the partition you would like affected (if the table in question has multiple partitions).
The next screen simply affects your changes to the database, either by generating a script (my preferred method) or running it straight into your database.
