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.
According to Technet:
Data compression can be configured for the following database objects:
- A whole table that is stored as a heap
- A whole table that is stored as a clustered index
- A whole nonclustered index
- A whole indexed view
- For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting
How Data Compression Affects Others
- Bulk import and export operations – Exports are uncompressed, Imports are compressed
- Backups and Restores – Compression does not affect backup and restore operations
- Log Shipping – Compression does not affect log shipping
- Sparse Columns – Data compression is incompatible with sparse columns.
- Query Optimizer – Enabling compression can cause query plans to change
More details on SQL Server Data Compression can be found here: