Data compression in SQL Server 2008

Data compression allows more rows to be stored in a page, but the maximum row size of a table or index does not change. There are a number of technical reasons for the DBA to consider often weighed up against perceived Database Cost Savings

Compressed data is stored on fewer pages therefore less pages to read therefore less IO intensive. The cost is increased CPU to compress and decompress

The first step taken in the data compression process is prefix compression.  Prefix compression identifies a value per column which is used to decrease storage space. 

The second step is dictionary compression. Dictionary compression focuses on repeated values across the whole page.

Row Compression uses 4 bits per compressed column to store data length in the column.  The extra space must be available for compression to succeed.  For example , if 3 columns existed : char (1000) , char(6000) , char(1049)   , attempting to enable a table for compression would fail , as the  aggregate of the column lengths plus the additional overhead of the bits required per column , exceed the  8060 maximum bytes per row.

Page compression performs Row Compression + optimization of multiple row storage in a page.  

Compressing indexes requires explicit setting.   Note:  the default setting for data compression on indexes is NONE

ALTER INDEX testIndex ON Test.Test
SET (
    DATA_COMPRESSION = PAGE,
  SORT_IN_TEMPDB=ON
    ) ;
GO

Data compression can be used on a HEAP.   This rebuilds all non-clustered indexes ,  to repoint the indexes to the new locations on the HEAP

 ALTER TABLE MyTable REBUILD WITH (Data_Compression = PAGE)

 Notes:

1)   SQL Server 2008 Enterprise and Developer Edition are minimum version requirements

2)  Hard disk space is saved and query performance can increase.  Weigh up against increased CPU.

3)  Use the stored procedure sp_estimate_data_compression_savings to create an estimate in savings.  Be careful when using this stored procedure , as it’s tempdb intensive.

4)  Offline compression is faster but locks the table during the compression

5)   Compress one object at a time , as it used less resources than multiple\simultaneous compressions

6)  Consider defragmenting prior to data compression.Read more on SQL Index Fragmentation and sys.dm_db_index_physical_stats

7)   Application changes aren’t required after data compression.

8)   Results vary , heavily dependant on usage of  precision based data types

Read More

SQL Server - How to use SQL Backup Compression - SQL Server DBA

Database Cost Savings - SQL Server DBA