Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Stored Procedure to Calculate Database Backup Compression Ratio


By:   |   Last Updated: 2017-12-28   |   Comments (4)   |   Related Tips: More > Compression

Problem

SQL Server offers the ability to create compressed backups, but it is hard to tell what the size of the compressed backup will be compared to the uncompressed backup.  So in this tip we look at a simple procedure to help figure what the size of the compressed backup will be before implementing backup compression.

Solution

The requirement is to create a simple T-SQL stored procedure that will take a user database and provide accurate database backup compression information without consuming disk space. It is very hard to give an estimation of the database compression ratio, because the compression ratio of a compressed backup depends on the data that needs to be compressed (data types, encryption, consistency, density, etc.).

The benefit of using database backup compression is that a compressed backup is usually smaller than an uncompressed backup of the same data.  Compressing a backup typically requires less I/O and therefore usually increases the backup speed significantly.  The drawback of using backup compression is that by default, database compression significantly increases the CPU usage, and the additional CPU consumed by the compression process might impact other concurrent operations and reduce performance.

The procedure that is shown here will act as a decision tool for deciding whether to use backup compression or not.  The solution involves creating a T-SQL stored procedure that takes the database name as a parameter and then produces the backup compression ratio percentage as an output parameter.

  1. The procedure takes the database name as @dbName.
  2. The procedure creates a BACKUP DATABASE T-SQL statement that does a compressed backup to the given database parameter, but to the 'nul' DISK device.  The 'nul' device is a special 'file' in the file system.  Anything that is written to the 'nul' device is discarded. So actually, you're not writing the backup file anywhere. The result is that you are backing up the database, but the file is never written in the first place. So no disk space is consumed. However, a row in msdb..backupset is created, containing the data needed to figure out the compression percentage.
  3. The procedure calculates the database backup compression ratio as the ratio of the compressed_backup_size and the backup_size columns that are written to the msdb..backupset table. This result is multiplied by 100 and cast as a decimal (5, 1) to the output the size of the compressed backup compared to the uncompressed backup.

Calculate Backup Compression in SQL Server

USE master
GO

-- ============================================================
-- Author:      Eli Leiba
-- Create date: 12-2017
-- Description: Compute the DB backup compression ratio %
-- ============================================================
CREATE PROCEDURE usp_Calc_DB_Compression_Ratio_Pct (
   @dbName SYSNAME,
   @compressPct DECIMAL (5, 1) OUTPUT
   )
AS
BEGIN
   DECLARE @dynaTSQL VARCHAR(400)

   SET NOCOUNT ON
   SET @dynaTSQL = CONCAT (
         'BACKUP DATABASE ',
         @dbName,
         ' TO DISK = N',
         '''',
         'nul',
         '''',
         ' with compression, copy_only '
         )

   EXEC (@dynaTSQL)

   SELECT @compressPct = cast (100.0*a.compressed_backup_size / a.backup_size AS DECIMAL (5, 1))
   FROM msdb..backupset a
   WHERE lower (a.database_name) = @dbName AND a.backup_finish_date = (
         SELECT max (backup_finish_date)
         FROM msdb..backupset
         )

   SET NOCOUNT OFF
END
GO
   

Example Use of SQL Server Backup Compression Stored Procedure

Letís calculate the database backup compression ratio of Microsoft's demonstration database - NORTHWIND.

The T-SQL code to run is as follows:

USE master
GO

DECLARE @comppct DECIMAL (5, 1)

EXEC usp_Calc_DB_Compression_Ratio_Pct @dbname = 'Northwind',
   @compressPct = @comppct OUTPUT

PRINT @comppct
   

The result is:

Processed 1184 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 1186 pages in 0.312 seconds (29.674 MB/sec).
19.4 

We can see above that the backup ran, but a backup file was not created. 

The value that is returned is 19.4.  This means the compressed backup would be 19.4% of the size of the uncompressed backup. This is pretty significant, the compressed backup would be more than 5 times smaller than the uncompressed backup.

Next Steps
  • You can create this simple procedure in your master database and use it to calculate the backup compression ratio of user databases and decide if compression is useful or not.
  • It is assumed that your server default database backup compression method is set to No compression.
  • The procedures was tested on SQL Server 2014, 2016 and 2017.
  • The procedure should be compatible with SQL Server 2012 and above.
  • If you want the procedure to be compatible with versions earlier than 2012 you should replace the CONCAT functions with the "+" concatenation operator.


Last Updated: 2017-12-28


next webcast button


next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 28, 2017 - 3:55:16 PM - Jun Back To Top

It does not work if database name has "."

It works after I changed it to '[' + @dbName + ']',

 


Thursday, December 28, 2017 - 12:11:45 PM - Gerald Back To Top

Hi,

but anyone who'll give this a try should know, that taking a backup to the nul device will have a huge impact on his disksubsystem because it'll read with full throttle...

BR

Gerald


Thursday, December 28, 2017 - 11:00:24 AM - Greg Robidoux Back To Top

Hi Jonathan,

The compression ratio will be different for each database.  It all depens on how compressible the data is.

For the one example providef, this database was compressed down to 19.4% of the uncompressed backup.

This will vary for each database.  Most databases with just textual data will yield high compression.

-Greg


Thursday, December 28, 2017 - 10:19:04 AM - Jonathan D. Myers Back To Top

Good morning, Eli,

I've read thru your tip and tested it in one of my dev environments. While it works, I do have a question concerning the compression ratio.

One of the assumptions you indicate at the end of your tip is that the server default is set to "no compression". Based on that assumption, what compression setting provides the result you've indicated in your example? I.E. does it provide the suggested compression setting to use for that particular database's compressed backup?

For example, you indicate a result showing a compressed backup would be 1/5th the uncompressed backup size. Does that suggest you should manually set the compression for that database's backup job to some particular value, or that adding "WITH COMPRESSION" to the backup statement will simply result in an 80% space savings over the uncompressed backup, for that particular database?

Thank you!

Jonathan


Learn more about SQL Server tools