Data & Backup Compression (Part 3) - Tests, Numbers and Tuning Tips
One of the most common question I've received regarding data and backup compression in SQL Server 2008 is related to achievable results in terms of performance and compressio-ratio on very large databases.
I've just found some interesting data published by Unisys which you can review at:
Microsoft SQL Server 2008 Data and Backup Compression
https://www.unisys.com/eprise/main/admin/corporate/doc/41371394.pdf
This document presents the results achieved by tests performed on a 2TB database, representative of a modern OLTP financial environment, on a dual-core machine with 128 GB of RAM, installed on SQL Server 2008 64-bit.
Tests include:
- Data compression (ROW and PAGE, with different granularity combinations)
- Backup Compression (on data-uncompressed database, on ROW compressed database, on PAGE compressed database)
Very interesting are the results related to Backup Compression.
In particular, you can easily notice how data-compression can influence backup-compression compressio-ratio, and how it impacts elapsed time and CPU utilization.
As you can see, backup compression on a data uncompressed database gives you a compressio-ratio of 2.95%, reduces by 36% backup runtime (from 6,937 secs to 2,358 secs) but increases CPU utilization by nearly the 55% (from 1.42 to 77.26).
How you can tune the performances of your backup compression.
1) We've already discussed how you can take advantage of Resource Governor to minimize the amount of CPU used (https://blogs.technet.com/beatrice/archive/2008/11/10/resource-governor-how-to-get-started.aspx).
2) Additionally Performance of backup operations may be increased by utilizing multiple backup devices and/or increasing BUFFERCOUNT.
This will have the effect of increasing the parallelism and overall CPU usage across all CPUs in the server, allowing to achieve an even more reduced backup time.
On the other hand, reducing the number of backup devices or BUFFERCOUNT may reduce the overall CPU used by the backup operation, leaving more CPU resources for other workloads.
More information can be found on the SQL CAT blog:
Tuning the Performance of Backup Compression in SQL Server 2008
Interesting would be to test resource governor together with increased buffercount, this should allow you to control the CPU utilization and on the same time to speedup the backup operation.
- Beatrice Nicolini -