Benefits of Backup Compression - Faster BACKUP / RECOVERY
This is in continuation to my blog that I posted on 23rd Dec, 2008 on “SQL Server 2008 Backup Compression”. While discussing about Benefits of SQL Server Backup Compression, I talked about “Faster Database Recoveries from Compressed Backups”. This might have raised questions “How do we achieve faster recoveries from compressed backup sets, when we are spending additional CPU time for decompressing the backup?”. I too had similar thoughts initially.
Here’s how I got the answer…
In a normal SQL Server Database backup scenario – maximum wait time is on I/O. (Remember, Backups are an I/O intensive operations.)
In case of a SQL 2008 Compressed backup – We are still spending time on I/O. However, since we are now dealing with compressed backups (small size backup files), hence we are spending less time in I/O, but more time in CPU as we are de-compressing backups at the time of recovery. This answers our questions.
Now, let’s check this with an example. In below example, let’s monitor the SQL Server WAITTYPES while performing a database backup/recovery from compressed and uncompressed database backups.
Test Scenario: To Determine Backup I/O
CASE A. UN-COMPRESSED Backup
- I have a database named ‘A_large_database’ of size 10174.25 MB’s (or 10 GB’s approx)
- And, I took a normal database backup (as below)
-----------------------------------------------
BACKUP DATABASE A_large_database
TO DISK = 'D:\tempdb\Before_Compression.bak'
GO
-----------------------------------------------
- Restored this backup (Note: remember using a database different name when restoring on same instance)
-----------------------------------------------
RESTORE DATABASE [un_compressed]
FROM DISK = 'D:\tempdb\Before_Compression.bak'
WITH FILE = 1,
MOVE 'A_large_DB' TO N'D:\tempdb\un_compressed.mdf',
MOVE 'A_large_DB_log' TO N'D:\tempdb\un_compressed_1.LDF'
GO
-----------------------------------------------
Output Message:
Processed 723144 pages database 'un_compressed', file 'A_large_DB' on file 1.
Processed 6 pages for database 'un_compressed', file 'A_large_DB_log' on file 1.
RESTORE DATABASE successfully processed 723150 pages in 349.7 sec (16.1 MB/sec)
-----------------------------------------------
SQL Server Wait Types and Wait Time:
wait type total_duration total_signal_duration
BACKUPTHREAD 349777 0
BACKUPIO 341662 0
PREEMPTIVE_OS_WRITEFILEGATHER 278254 101
IO_COMPLETION 6781 0
WRITE_COMPLETION 844 0
PREEMPTIVE_OS_FLUSHFILEBUFFERS 392 11679
PAGEIOLATCH_SH 288 0
PAGEIOLATCH_UP 33 0
PREEMPTIVE_OS_FILEOPS 24 6162
PAGEIOLATCH_EX 15 0
WRITELOG 4 0
-------------------------------------------------------------------------------------------------
CASE B. COMPRESSED Backup
Now, I took a COMPRESSED database backup (as below)
-----------------------------------------------
BACKUP DATABASE A_large_database
TO DISK = 'D:\tempdb\With_Compression.bak'
WITH COMPRESSION
GO
-----------------------------------------------
- Restored this backup
-----------------------------------------------
RESTORE DATABASE [compressed]
FROM DISK = 'D:\tempdb\With_Compression.bak'
WITH FILE = 1,
MOVE 'A_large_DB' TO N'D:\tempdb\compressed.mdf',
MOVE 'A_large_DB_log' TO N'D:\tempdb\compressed_1.LDF'
GO
-----------------------------------------------
Output Message:
Processed 723144 pages for database 'compressed', file 'A_large_DB' on file 1.
Processed 1 pages for database 'compressed', file 'A_large_DB_log' on file 1.
RESTORE DATABASE successfully processed 723145 pages in 228.4 sec (24.7 MB/sec)
-----------------------------------------------
SQL Server Wait Types and Wait Time:
wait_type total_duration total_signal_duration
PREEMPTIVE_OS_WRITEFILEGATHER 228547 29
BACKUPTHREAD 228481 0
BACKUPIO 133434 1520
ASYNC_IO_COMPLETION 83458 0
IO_COMPLETION 6479 0
WRITE_COMPLETION 1193 0
PREEMPTIVE_OS_FLUSHFILEBUFFERS 524 11715
PAGEIOLATCH_SH 259 0
PREEMPTIVE_OS_FILEOPS 55 4149
PREEMPTIVE_OS_CREATEFILE 29 24
PAGEIOLATCH_EX 25 0
WRITELOG 12 0
PAGEIOLATCH_UP 10 0
SLEEP_BPOOL_FLUSH 7 0
-------------------------------------------------------------------------------------------------
The result of above recovery scenario clearly shows that, due to fewer BACKUPIO, the total time spend while recovering from a compressed backup set is comparatively less.
To summarize Benefits:
- A compressed backup being smaller in size takes less space on storage media
- Backup and Restore of compressed backup requires less I/O. The result is reduced backup / recovery time and a better performance.
IMP Note: 'SQL Backup Compression' feature is currently available only in SQL Server 2008 Enterprise Edition, However, ALL SQL Server 2008 editions can restore a compressed backup
In a nutshell, Backup operation is I/O intensive. Backup compression writes LESS pages to disk comparing to uncompressed backup, as long as you system is not bottlenecked on CPU, backup compression should executes faster than backup without compression.
Reference Read: For more details, please refer >> SQL Server 2008 BOL - https://technet.microsoft.com/en-us/library/bb964719.aspx
Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.