I thought that it would be handy to capture some Perfmon graphs of what happens to backup throughput and CPU on my SQL Server when I have backup compression on vs off against my 35GB database.
No compression
BACKUP DATABASE TATA TO DISK = 'd:\mylocation\DBNOTOCOMP.BAK' WITH COPY_ONLY, STATS = 10
I will be using data collectors to show the perfmon counters(below) for the duration of the backup command – it just paints a better picture than running real time perfmon graphs.
% Processor Time
Backup Throughput/sec
Overall Time & size
5.5 minutes to do:
With Compression
BACKUP DATABASE TATA TO DISK = ' d:\mylocation\DByesCOMP.BAK' WITH COPY_ONLY, STATS = 10, COMPRESSION
Again Data collectors paint a nice picture for CPU and Backup Throughput.
% Processor Time
Backup Throughput/sec
Overall Time & size
2.5 minutes to do:
Findings
Some of the findings maybe obvious to you, having said that here is what I found:
- Using backup compression uses more CPU. (My Average for %Processor Time went from 5.05 to 18.93)
- A compressed backup is much smaller hence backup speed is much faster (nearly reduced the time taken by 50%).
- Backup Throughput more than doubled when compressing.
- Compression ratio does depend on many factors but my ratio was 2.88.
Trace Flag 3042
You can change the behaviour of the compressed backup. By default the Database Engine uses a pre-allocation algorithm for the backup file. For my example it pre-defined to the size of DByesCOMP.BAK = 4646286KB.
Using the trace flag you can see the change in behaviour. Instead it starts of from a low size and works its way up to the true final size.
DBCC TRACEON (3042) BACKUP DATABASE TATA TO DISK = ' d:\mylocation\DByesCOMP1.BAK' WITH COPY_ONLY, STATS = 10, COMPRESSION
I did find that using this trace flag made the backup operation slightly longer with a reduced throughput rate. I personally have no use for this trace flag but it was a new behaviour for me so I thought I would share it too.
Pingback: SQL Server Backup Compression - SQL Server Blog - SQL Server - Toad World
fantastic article! This is so chock full of users information and the resources you provided was helpful to me. There I found informative blog discussing about advantages of Backup compression: http://www.sqlserverlogexplorer.com/advantages-of-backup-compression/
LikeLike
Thanks – I have a post on multiple backup files coming soon too
LikeLike
Hi, nice explanation, thanks. We used to perform our databases backups in such way, now we are testing SQLBackupAndFTP and it works smoothly. It backups, compresses, and send backups to selected destination places according to the schedule.
LikeLike
Here is that tool https://sqlbackupandftp.com
LikeLike
Pingback: Sql Backup Compression - SecuredGuide