Back in September last year I blogged about the native Backup Compression that is in SQL Server 2008 (see here) and promised to blog more when CTP-5 came out and I ran some tests. Well, it’s here and I have so here’s some data for you.


I expanded the AdventureWorks database to be 322Mb (random size, but big enough to get a decent sized run-time on my server). I used System Monitor to capture %user-mode CPU time, plus backup/restore throughput for a compressed and uncompressed backup operation, and then restores.



1) For the uncompressed backup the average CPU was 5% (the green line at the bottom), the run-time was 39.5s, and, of course, it took 322Mb to store the backup.



2) For the compressed backup the average CPU was way higher at 25%, BUT the run-time was 21.6s (a 45% improvement), and the backup was stored in 76.7MB (a 4.2x compression ratio). Very cool.



3) For the restore of the uncompressed backup the average CPU was 8%, and the run-time was 71.0s.



4) For the restore of the compressed backup the average CPU was 14.5%, and the run-time was 36s (a 50% improvement).


So – to summarize, turning on compression means more CPU and smaller run-times – just what was expected. Note that if you try this on your database you will see different results – the compression ratio and CPU usage is entirely dependent on the data being compressed.