SQL Server Performance

Experimented with 2008 Backup With Compression

Discussion in 'SQL Server 2008 General DBA Questions' started by jbates99, Oct 23, 2009.

  1. jbates99 Member

    hi experts,

    I have been backing up my 20 GB database in various ways, just to see the benefits of the SQL Server compression feature, and I have been surprised at just how much smaller the .bak is when I request compression :)

    When I use No_Compression (which is actually the default)

    size of .bak = 20.2 GB
    duration = 19 minutes

    When I use COMPRESSION

    size of .bak = 3.4 GB
    duration = 7 minutes
    A couple of things surprised me: I expected the time or duration to be longer when compression is used. But instead it takes LESS time to do the compression. I also thought it might take LONGER to backup when compression but it completed in less than HALF the time.

    ** I have heard that compression usually results in a .bak file that's about 1/3 the size of the file when it is not compressed but, for this database, mine is about 1/5 th the size. **
    I'm curious to know how much reduction you got if you have used the 2008 compression feature.

    Thanks, John
  2. satya Moderator

    Don;t you think it is good that the compression is achieved quickly, better than you expected [:)].
    http://sqlserver-qa.net/blogs/perft...and-restore-performance-stats-with-a-dmv.aspx to get status and (BOL states) to obtain a good picture of your backup I/O performance, you can isolate the backup I/O to or from devices by evaluating the following sorts of performance counters:
    • Windows I/O performance counters, such as the physical-disk counters

      • The Device Throughput Bytes/sec counter of the /msdn.microsoft.com/mshelp" />SQLServer:Backup Device object

        • The Backup/Restore Throughput/sec counter of the SQLServer:Databases object
      • To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:
        For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space.
        SELECT backup_size/compressed_backup_size FROM msdb..backupset;
        The compression ratio of a compressed backup depends on the data that has been compressed. A variety of factors can impact the compression ratio obtained.
  3. jbates99 Member

    Thanks for the code to calculate my compression ratio, Satya. And yes, it is a very good thing to be exceeding the expected results.

  4. johnson_ef Member

    Thanks Satya and John,Though we may know the features, we never try these things unless until we experience it. Thanks for John's experiment and Satya's response.
  5. chumeniuk New Member

    This seems pretty much inline with my experience with Redgate and LiteSpeed. The increase in performance comes from the that compression is done during the back and not after the file has been created. It's much easier to do compression in memory using CPU writing a smaller amount of data to disk. My biggest disappointment is the the lack of options compared to the 3rd party tools like throttling CPU usage, throughput, or the amount of compression.

Share This Page