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

 

Testing SQL Server Backup Performance with Trace Flag 3042


By:   |   Last Updated: 2015-05-11   |   Comments (2)   |   Related Tips: More > Backup

Problem

Starting with SQL Server 2008 a new feature which allowed us to create compressed backup files for our SQL Server databases was introduced. This feature uses a pre-allocation algorithm to determine how much space the compressed backup would require and would allocate this space at the start of the backup process. This tip will look at the performance impacts of disabling this pre-allocation of space using the 3042 SQL Server trace flag.

Solution

Background Information

For those who aren't familiar with how compressed backups work let's discuss the default behavior. That is without the 3042 trace flag enabled. When you initiate a backup command using the with compression option the SQL Server engine uses an algorithm to determine how much space it thinks the backup will require. It then pre-allocates this space on the file system. If as it goes through the backup process the engine determines it needs more space to complete the backup it simply expands this file. If it completes and has not used all the pre-allocated space it simply shrinks the file. The pre-allocation of space saves us the overhead of constantly growing our backup file as we write to it and in turn our backup usually completes a little bit faster.

In my experience I have found that in most cases the algorithm allocates more space than is required, how much more really depends on the data in your database. With that said there are cases when you don't have enough disk space (especially if dealing with really large TB size databases) to have it pre-allocate a whole lot of extra space. That is where the 3042 trace flag helps. This trace flag tells the database engine not to pre-allocate any space and just grow the file as it writes to the backup file. Let's run through a couple examples and see what the performance impact is having the trace flag enabled.

Performance Test

For this performance we are going to use 3 different databases of the following sizes: 1.1GB, 1.9GB and 3.6GB. The different sizes will allow us to see if there is any pattern as the database grows. I would like to have been able to test even larger databases, but my current desktop wouldn't accommodate it. Below you'll find the T-SQL code to perform the backups. You'll notice after each command we flip the 3042 trace flag on and off.

USE master
GO

-- 1.1GB database
BACKUP DATABASE TestDB11 to DISK = N'C:\SQLBackup\TestDB11no3042.bak' WITH COMPRESSION
GO
DBCC TRACEON (3042,-1);
GO
BACKUP DATABASE TestDB11 to DISK = N'C:\SQLBackup\TestDB11with3042.bak' WITH COMPRESSION
GO
DBCC TRACEOFF (3042,-1);
GO

-- 1.9GB database
BACKUP DATABASE TestDB19 to DISK = N'C:\SQLBackup\TestDB19no3042.bak' WITH COMPRESSION
GO
DBCC TRACEON (3042,-1);
GO
BACKUP DATABASE TestDB19 to DISK = N'C:\SQLBackup\TestDB19with3042.bak' WITH COMPRESSION
GO
DBCC TRACEOFF (3042,-1);
GO
  
-- 3.6GB database
BACKUP DATABASE TestDB36 to DISK = N'C:\SQLBackup\TestDB36no3042.bak' WITH COMPRESSION
GO
DBCC TRACEON (3042,-1);
GO
BACKUP DATABASE TestDB36 to DISK = N'C:\SQLBackup\TestDB36with3042.bak' WITH COMPRESSION
GO
DBCC TRACEOFF (3042,-1);
GO
  
-- cleanup backup files
xp_cmdshell 'del C:\SQLBackup\TestDB*.bak'
go

Test Results

Since all we are really measuring here is the backup duration we don't even need to use SQL Profiler to gather our performance test results. We can just use the output from our query window. Below are the results from the test that I ran. Note that I ran through the test scenario 5 times and took the median value so I could exclude any outliers that may have been affected by something else running on my test machine.

--3.6GB DB -> backup file is 790MB
--with 3042 disabled
BACKUP DATABASE successfully processed 454146 pages in 77.311 seconds (45.892 MB/sec).
--with 3042 enabled
BACKUP DATABASE successfully processed 454146 pages in 81.606 seconds (43.477 MB/sec).

--1.9GB DB -> backup file is 260MB
--with 3042 disabled
BACKUP DATABASE successfully processed 237509 pages in 48.177 seconds (38.514 MB/sec).
--with 3042 enabled
BACKUP DATABASE successfully processed 237506 pages in 50.774 seconds (36.544 MB/sec).

--1.1GB DB -> backup file is 380MB
--with 3042 disabled
BACKUP DATABASE successfully processed 151642 pages in 24.472 seconds (48.410 MB/sec).
--with 3042 enabled
BACKUP DATABASE successfully processed 151642 pages in 24.625 seconds (48.109 MB/sec).

Test Results Analysis

You can see from this simple test that as the database got larger the database backup starts to take a little bit longer with the trace flag enabled. I really would have liked to be able to run this test on a really large database and see how much of an effect it would have on the total duration. Most people can spare the extra space and leave this trace flag disabled, but it's nice to know that it is available if you need it.

Although not part of this performance test, as I had mentioned at the start of the tip, the final compressed size of the backup really depends on the data in your database. In my case the 1.9GB database actually compressed to a smaller size than the 1.1GB database.

Next Steps


Last Updated: 2015-05-11


next webcast button


next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

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.



    



Monday, May 11, 2015 - 9:29:33 PM - Ben Snaidero Back To Top

Hi Garry,

The is no automated way to track the file during the backup other than watching the filesystem after you issue the backup command.  With the trace flag enabled you'll notice as you refresh the file continues to grow.  With it disabled note the size of the file as soon as you start the process and check the size again after it completes.  It's easier if you do this with a big database to give you some time

Thanks for reading


Monday, May 11, 2015 - 8:08:02 AM - Garry Bargsley Back To Top

I like your information.  However, you did not really outline how space was saved on the disk during the backup process.  How do you know the backup file was sized correctly and not pre-sized and shrunk with the trace flag?  Is there a way to track that operation to see the difference?

 


Learn more about SQL Server tools