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.
The Curious Case of… missing 832 errors during memory corruption
(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more
7 thoughts on “SQL Server 2008: Backup Compression CPU Cost”
Cool – good to see that backup compression does a better job than NTFS compressed files. Google translates into English very nicely :-)
Hi Paul,
I don’t know if you understand spanish but, anyway, try to take a look to SQL Server 2008 Backup Compression
Confirmed. I see nearly the same results (although the rise of CPU use in my case is not that big and is pretty negligible compared to what I have normally). Compression results in a serious boost of task performance. After all it is always about how fast you are with getting things done. If you have ineffective process it doesn’t matter how fast your CPU/HDD and whatnot. Just the opposite, if you have a well-designed plan, you use the right tools to implement it will be just a matter of time to configure backup the right way and select the best configuration for your environment. In fact, I’ve first revealed how greatly SQL DB compression improves overall performance way before I had the November CTP of SQL server implemented in our company. That all was thanks to SQL 2005 and it’s marvelous improvements and security features and unprecedented ease of security management that I’ve opened for myself when I got a suggestion from a friend of mini to try out Litespeed SQL management tool from Scriptlogic. I am always a bit skeptical about installing new tools into my environment. Well, the more tools you have the more bloated your system becomes. But that wasn’t the case and thanks a chance that I got the tool installed and tried it on my servers. I did my nightly configurations for my database backup scripts. Then my friend, Jim called and I said him I fed up with all that because all that pack of scripts didn’t show any sign of being well-designed and working smooth. I tested several approaches but my perfmon showed me that I am far a way from finishing with it. And false positives with sluggish script execution added oil to the fire. I said him that I had to leave our weekend as I thought I would be unable to solve the task that fast. And then he told me why I did I do the grand and don’t use Scriptlogic’s Litespeed. I completed all my tasks in… Well in fact I don’t even remember how much time I spent, as I set a schedule and went back home. It was just to pass a few of configuration wizard’s screens and finally configure a scheduled backup for every database. After a while I’d been able to finish with configuring all backup jobs for every database I had in my backup plan and schedule them to run automatically when I’d be away. The best thing was that the tool was able to automatically compress, backup and encrypt the database to a remote network shared folder without any assistance from my side!
woahhh….finally, Microsoft is releasing the service pack for SQL Server 2008. I have been waiting for quite sometime and I love to know the fixes and patches it provides. Currently, I host my SQL 2008 database with asphostcentral.com and it looks good. The performance is good and features are a lot better than SQL 2005.
What would be the next SQL Server version? Something like SQL Server "Azure"? I truly love to know as I love to try new technologies.
:)
How can i reduce the cpu usage?
Use Resource Governor to reduce the CPU usage, which will of course make the backup take longer, but with the same space savings and potential for much faster restores. BOL has a link from the Resource Governor page and from the backup compression page.
Hey There. I found your blog using msn. This is a really well written article. I’ll make sure to bookmark it and come back to read more of In Recovery… | SQL Server 2008: Backup Compression CPU Cost . Thanks for the post. I’ll certainly return.