SQL Server Data Compression Overview

I have been reviewing the options for data compression in SQL Server and believe it can be a great value to Reporting and Business Intelligence databases.  By their nature BI databases typically store more data than any other database in your environment.  Even with the cost of disk storage decreasing, finding ways to limited space and improve performance are critical to an optimal architecture.

Data Compression Performance

Compression can be enabled at the row or page level on tables and indexes.  When turned on the data inside the database is compressed therefore reducing the overall size of the database.  By reducing the amount of pages required to store the data.  Improvement in the performance of heavy I/O workloads can be seen.  The trade off is the CPU must work harder to decompress the data when being retrieved for the application.  I recommend setting up and monitoring your database server’s performance counters prior to implementing data compression and creating a baseline.  Then enable compression on a single table or index and monitor the performance counters again.  Only by actively reviewing the performance can  you ensure you are achieving the benefits of compression.