Compress Archive Databases, Gain Disk Space And Improve Performance

Database Compression is one of the useful functionalities that is became available with SQL Server 2008. By compressing tables and indexes of archive database, you can gain 6-8 time disk space and reduce I/O workload.
One of the best scenarios that suits SQL Server Data Comression is compressing archive databases. Because archive databases are written once and read rarely if needed. DML operations (insert, update, delete) are never done apart from archiving operation.

Archive databases are generally for data reading.  While reading data from compressed tables, since less I/O needed, reading performance is improved.

The cost of data compression is more cpu usage. Cpu usage must be taken into consideration if there is cpu pressure.

Data compression has two levels, row level compression and page level compression. Page level compression contains row level compression.

It is possible to check how much disk space will be freed with Management Studio.  By clicking right button of mouse on the table, as seen below, the related screen is open.

After choosing compression type, by pushing  “Calculate” button, it is calculated that how much disk space will be used after compression. This is calculated by sampling.

Compression ratio depends on table structure, like column types, data histogram etc. . For example if columns have mostly null values, or if some integer columns has small values like 0 or less than 255, or if some char or varchar columns have repeating values, they are compressed in a high ratio.

During compression operation file usage is increased. In order to not grow file size, it is better to start compressing by small sized tables.

Log space is also needed during compression operation. If database recovery model is not simple it is better to take transaction log backup between compressions operations.

When a table is compressed, non-clustered indexes are not compressed, only rebuilt. If non-clustered indexes wanted to be compressed, they must be compressed additionally.

After completing compression operation, the freed space must be handled. If free space is too much, files may be shrunk to a reasonable size. 

Conclusion: Compressing archive databases, improves I/O and frees disk space. If compression ratio is 6-8 times,  compression process can be started immediately.