SQL Server 2008: Backup Compression CPU Cost
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.