Jaa

Benefits of Backup Compression - Faster BACKUP / RECOVERY

  • Artikkeli

 

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:

  1. A compressed backup being smaller in size takes less space on storage media
  2. 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.

Comments