Published on

March 17, 2010

Exploring SQL Server Compression Technologies

SQL Server 2008 introduced two compression technologies: backup compression and table/index compression. These technologies are only available in the Enterprise edition of SQL Server. As a database administrator responsible for managing backups and large databases, I was intrigued by the potential benefits of these compression technologies. In this article, I will share the results of my testing and discuss the effectiveness and performance impact of SQL Server compression.

Setup and Test Procedure

I conducted the testing on a workstation-class machine running Windows 7 with SQL Server 2008 Enterprise Edition installed. I created a test database with three identical tables and populated them with one million random records. One table remained uncompressed, one was compressed using row-level compression, and the third was compressed using page-level compression. After compressing the tables, I performed a standard full backup, a full backup with compression, and a compressed backup using a third-party compression utility. I also ran scripts to simulate random reads and writes to the database while monitoring server performance.

Results

Backup Compression: The uncompressed database size was just over 10GB. A standard full backup produced a 9.26GB file in 7 minutes and 19 seconds. A full backup with compression produced a 1.80GB file in 2 minutes and 58 seconds, resulting in an 80% smaller file in 60% of the time. The compressed backup restored 43% faster than the uncompressed backup.

Table/Index Compression: After compression, the table compressed with row-level compression showed a disk space savings of about 91%, reducing the data size from 7990MB to 709MB. The table compressed with page-level compression yielded similar space savings. The compression process took a few minutes to complete. During the test, CPU usage increased by around 6% for page-level compression and 12% for row-level compression. Disk read performance was slightly improved with compression, while disk write performance fluctuated.

Conclusion

The space savings and increased speed of compressed backups are impressive. The only reasons not to take advantage of backup compression would be if your SQL server is already struggling with high CPU usage or if you don’t have the Enterprise edition. Table and index compression also offer significant space savings with little or no impact on performance. The slightly higher CPU usage is offset by the reduced disk space usage. Selective compression of specific tables or partitions can help achieve a balance between space savings and performance. It is important to test these compression technologies in your own environment before making a final decision.

Overall, SQL Server compression technologies provide valuable tools for reducing disk space usage without sacrificing performance. While there may be cases where compression is not suitable, it is worth exploring these features to determine their potential benefits in your specific environment.

Disclaimer: Each SQL server setup is unique, and thorough testing should be conducted in a development environment before implementing compression technologies.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.