Professional Microsoft® SQL Server® 2008 Administration

12.4. Data Compression

SQL Server 2008 data compression brings enhancements in storage and performance benefits. Reducing the amount of disk space that a database occupies reduces the overall data files storage footprint and offers increase in throughput with the following:

• Better I/O utilization, as more data is read and written per page

• Better memory utilization, as more data will fit in the buffer cache

• Reduction in page latching, as more data will fit in each page

It is true that disk space is becoming less expensive. However, implementation of a high-performance database system requires a high-performing disk system or Storage Area Network (SAN) or Network Attached Storage (NAS) storage, which is not inexpensive. Additionally, this may require additional storage for high availability, backups, QA, and test environments. Overall, it lowers SQL Server 2008's total cost of ownership, making it more competitive.

Before implementing data compression, you must consider the trade-off between I/O and CPU. To compress and uncompress data requires CPU processing utilization, so it would not be recommended for a system that is CPU-bound. However, it would benefit a system that is more I/O-bound. Second, keep in mind that accessing the compressed data requires CPU processing work, and if this data is highly selected, there is a CPU performance penalty. Data compression makes most sense on data that is older and less queried. As a simple example, with a very large partitioned table, the ...