Backup Performance Testing

Reliable database backups are perhaps the single most important aspect of a Database Administrator’s job. The business risk from data-loss that can occur without a reliable database backup are substantial enough to bring many smaller companies close to bankruptcy. I can’t stress enough the importance of ensuring the reliability of your database backups. Please, test your backups by restoring them onto a test system. Having said that, this blog post is only going to provide a framework for testing the performance of your backup system.

Microsoft SQL Server provides a range of parameters that affect backup performance; including the number of buffers used, the maximum transfer size per I/O operation, the I/O block size, and the backup stripe count. Microsoft provides documentation about these settings here.

The code below takes multiple backups of the target database with various combinations of the parameters listed above. This code, as configured in the example, results in 2400 backups being taken. Results are saved into a global ##temp table for analysis.

NOTE: The backups taken by this process are automatically deleted immediately after the backup completes. Backups are taken with the COPY_ONLY option which does not affect the normal sequence of backups. Since Backup operations are an I/O intensive operation, and this script creates a very large number of backup operations sequentially, you’ll want to ensure you do this either on a non-production system that closely resembles your production system, or if you are doing it on your production system, ensure you do it during a quiet period to avoid impacting client activity. Be warned, this test takes a very long time to run owing to the large number of backups created.

You’ll see output similar to this while running the code:

Processed 33976 pages for database 'Test', file 'Test_DB' on file 1.
Processed 10792 pages for database 'Test', file 'MyFile' on file 1.
Processed 1 pages for database 'Test', file 'Test_Log' on file 1.
BACKUP DATABASE successfully processed 44769 pages in 3.297 seconds (106.081 MB/sec).
BACKUP DATABASE [Test]
TO DISK = N'C:/Temp/BackupTest_Test/Test_TestBackup_Stripe_0000000001.bak'
WITH BUFFERCOUNT = 7
    , BLOCKSIZE = 512
    , MAXTRANSFERSIZE = 65536
    , NO_CHECKSUM
    , COPY_ONLY
    , COMPRESSION
    , FORMAT
    , INIT
    , STATS = 10
 
EXEC sys.xp_delete_file 0, 'C:/Temp/BackupTest_Test/Test_TestBackup_Stripe_0000000001.bak';
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 33976 pages for database 'Test', file 'Test_DB' on file 1.
Processed 10792 pages for database 'Test', file 'MyFile' on file 1.
100 percent processed.
Processed 1 pages for database 'Test', file 'Test_Log' on file 1.
BACKUP DATABASE successfully processed 44769 pages in 6.351 seconds (55.070 MB/sec).
Duration in MILLISECONDS: 6390
 
BACKUP DATABASE [Test]
TO DISK = N'C:/Temp/BackupTest_Test/Test_TestBackup_Stripe_0000000001.bak'
	, DISK = N'C:/Temp/BackupTest_Test/Test_TestBackup_Stripe_0000000002.bak'
WITH BUFFERCOUNT = 12
    , BLOCKSIZE = 512
    , MAXTRANSFERSIZE = 65536
    , NO_CHECKSUM
    , COPY_ONLY
    , COMPRESSION
    , FORMAT
    , INIT
    , STATS = 10
 
EXEC sys.xp_delete_file 0, 'C:/Temp/BackupTest_Test/Test_TestBackup_Stripe_0000000001.bak';
EXEC sys.xp_delete_file 0, 'C:/Temp/BackupTest_Test/Test_TestBackup_Stripe_0000000002.bak';

You can check the global temporary table while the code is running to see progress:

Results look like this:

I use two pivots to analyse the full results. The first pivot looks at the results where the buffer count is the default count chosen by SQL Server. The second shows the non-default buffer counts:

The pivoted results look like this:

The columns numbered 1 through 10 show the duration in milliseconds for each set of parameters by stripe-count.

The sample results shown above seem to indicate there is no clear preference from any of the settings; in my case this test was ran against a reasonably small database over the LAN. Both the backup target folder and the database files reside on busy storage area networks (SANs) that appear to be masking the effects of modifying the parameters tested by my code. Overall, there seems to be very little difference in duration between any of the backups taken against my test database. In this case, it makes sense to leave the defaults in place, and simply do a BACKUP DATABASE xxx TO DISK = '\path\to\backup';. Pretty clearly, with different hardware setups, this testing rig may show certain settings are clear winners over the defaults.

FYI, Microsoft has made some significant improvements to backup speed in SQL Server 2017 for databases on large, high-end servers. Read about that on this MSDN blog post.

Thoughts? Questions? Did I do something wrong? Let me know in the comment section below.

Ads by Google, Paying the Rent: