June 1, 2012 at 10:41 am
We have a large production database that currently produces three 149GB files during its full backup job. I started using backup compression on a copy on our test server and we're getting three 40GB files, in about a third of the time. This offers advantages for our backup archive system, and for when we want to copy backups to the test server to create new test systems. I'd like to propose using backup compression in production, but that will only fly if I can convince management that increased CPU load would not affect performance while the backup job runs. It looks to me that the server's processors have plenty of extra horsepower.
Has anyone ever seen compressed backups adversely affect performance for users?
June 1, 2012 at 11:57 am
We haven't here but I'm sure there are cases where it has. On a server where there isn't a lot of extra CPU power I would think it may be possible. If there's plenty of spare power when you're doing the backup then it isn't likely a concern.
There are also third party backup tools that offer compression at varying levels so you have more control over how much CPU is used.
June 1, 2012 at 12:11 pm
I use compressed backups for full and log backups and have never had an issue. The only way to know is to test on your hardware. You have a non-trivial size backup and are using multiple backup devices so you have more to test 🙂
This article may help your decision-making process:
Tuning Backup Compression Part 1 from SQLCAT
Tuning Backup Compression Part 2 from SQLCAT
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2012 at 2:22 pm
Yes, you need to test against the hardware you have
Compression does not increase CPU usage always as you expect
Also consider reduction in IO (disk or network) reduces CPU usage too and compensates overhead due to compression
What I have observed in my case is, CPU usage goes high (acceptable) during backup but overall CPU time used is less.
June 2, 2012 at 6:28 pm
Agree 100% with Daxesh. There is increased CPU due to the compression, but the decreased IO offsets most of the CPU hit. The big exception is if the database is TDE encrypted. The encrypted data cannot be compressed very much and is probably not worth the CPU hit.
June 4, 2012 at 5:35 am
You need to validate how much CPU usage you're seeing on the system now. If you're already operating at the edge, sustained CPU usage at or above 75%, then you may not want to add to the load by putting compression into the mix. However, if your CPU is down low, the one or two percent increase you're likely to see is going to be worth everything because of all the IO and time savings.
I've never seen compression hurt. I've only ever seen it help. Same goes with database compression.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply