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 - http://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.