By: Ben Snaidero | Comments (2) | Related: > 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
- Test using the trace flag on even larger databases and verify if the pattern continues
- Read other tips on compression
- Other backup/compression tools
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
