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.

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#output', N'U') IS NOT NULL
DROP TABLE #output;
CREATE TABLE #output
(
    txt nvarchar(1000) NULL
);

IF OBJECT_ID(N'tempdb..##backup_test_results', N'U') IS NOT NULL
DROP TABLE ##backup_test_results;
CREATE TABLE ##backup_test_results
(
    rownum int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , DBName sysname NOT NULL
    , StartDate datetime NOT NULL
    , BackupPath varchar(260) NOT NULL
    , StripeCount int NOT NULL
    , [BufferCount] int NOT NULL
    , [BlockSize] int NOT NULL
    , [MaxTransferSize]  int NOT NULL
    , [Checksum] int NOT NULL
    , CopyOnly int NOT NULL
    , [Compression] int NOT NULL
    , [Format] int NOT NULL
    , [Init] int NOT NULL
    , Duration int NOT NULL
    , IsDefaultBackup bit NOT NULL --indicates default buffer counts
);

--Trace flag 3213 displays backup/restore parameters used.
--Trace flag 3604 sends output to the client instead of the errorlog.
--DBCC TRACEON(3213, 3604);

DECLARE @DebugOnly bit = 0;
DECLARE @DatabaseName sysname;
DECLARE @BackupPath nvarchar(260);
DECLARE @MaxStripes int;
DECLARE @CurrentStripes int;
DECLARE @BufferCount int; --BUFFERCOUNT
DECLARE @BufferCountLoop int;
DECLARE @BlockSize int; --BLOCKSIZE  (512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536)
DECLARE @MaxTransferSize int;  --MAXTRANSFERSIZE (64KB to 4MB)
DECLARE @Checksum bit;
DECLARE @CopyOnly bit;
DECLARE @Compression bit; --COMPRESSION or NO_COMPRESSION
DECLARE @Format bit; --FORMAT or NOFORMAT
DECLARE @Init bit; --INIT or NOINIT
DECLARE @DatabaseDeviceCount int; 

DECLARE @ToClause nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE @StartTime datetime;
DECLARE @EndTime datetime;
DECLARE @Connector nvarchar(max);
DECLARE @DelConnector nvarchar(max);
DECLARE @DelCmd nvarchar(max);
DECLARE @msg nvarchar(100);

SET @MaxStripes = 10;
SET @DatabaseName = DB_NAME();
SET @BackupPath = N'C:/Temp/BackupTest_' + @DatabaseName;
SET @BufferCount = 1;
SET @BlockSize = 512;
SET @MaxTransferSize = 1048576;
SET @Checksum = 0;
SET @CopyOnly = 1;
SET @Compression = 1;
SET @Format = 1;
SET @Init = 1;

DECLARE @res TABLE (
    [File Exists] bit NOT NULL
	, [File is a Directory] bit NOT NULL
    , [Parent Directory Exists] bit NOT NULL
    );

INSERT INTO @res 
EXEC sys.xp_fileexist @BackupPath;

IF (SELECT r.[File is a Directory] FROM @res r) = 0
BEGIN
    SET @msg = N'Backup Folder "' + @BackupPath + N'" does not exist.';
    RAISERROR (@msg, 18, 1);
END
ELSE
BEGIN

--distinct I/O paths, i.e. individual disks, not database files
SELECT @DatabaseDeviceCount = COUNT(DISTINCT SUBSTRING(mf.physical_name, 1, CHARINDEX(':', mf.physical_name))) 
FROM sys.master_files mf
WHERE mf.database_id = (SELECT database_id from sys.databases d WHERE d.name = @DatabaseName)
    AND mf.type_desc = 'ROWS';

IF @DebugOnly = 0 
BEGIN
    /* warm up the I/O path to reduce issues with timimg for first backup */
    BACKUP DATABASE @DatabaseName TO DISK = 'NUL' WITH COPY_ONLY;
END

--buffercount loop (5 iterations)
SET @BufferCountLoop = 0; --0 is special case for default number of buffers
WHILE @BufferCountLoop < = 128
BEGIN

--maxtransfersize loop (6 iterations)
SET @MaxTransferSize = 65536;
WHILE @MaxTransferSize <= (2 * 1048576)
BEGIN

--blocksize loop (8 iterations)
SET @BlockSize = 512;
WHILE @BlockSize <= 65536
BEGIN
SET @CurrentStripes = 1
WHILE @CurrentStripes <= @MaxStripes
BEGIN
    SET @Connector = N'';
    SET @DelConnector = N'';
    SET @ToClause = N'';
    SET @DelCmd = N'';
    DECLARE @i int = 1;
    WHILE @i <= @CurrentStripes
    BEGIN
        SET @ToClause = @ToClause + @Connector + N'DISK = N''' + @BackupPath + N'/' + @DatabaseName 
            + N'_TestBackup_Stripe_' + RIGHT(N'00000000000' + CONVERT(nvarchar(10), @i), 10) + N'.bak''';
        SET @DelCmd = @DelCmd + @DelConnector + N'EXEC sys.xp_delete_file 0, ''' + @BackupPath 
            + N'/' + @DatabaseName + N'_TestBackup_Stripe_' + RIGHT(N'00000000000' + CONVERT(nvarchar(10), @i), 10) 
            + N'.bak'';';
        SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + N', ';
        SET @DelConnector = CHAR(13) + CHAR(10);
        SET @i += 1;
    END
    IF @BufferCountLoop = 0
    BEGIN
        /* default buffer count algorthim (wrapped for readability)
            https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/06/
               incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition/
        */
        SET @BufferCount = (@CurrentStripes * 4) + @CurrentStripes + (2 * @DatabaseDeviceCount);
    END
    ELSE
    BEGIN
        SET @BufferCount = @BufferCountLoop;
    END
    SET @cmd = N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + N'
TO ' + @ToClause + N'
WITH BUFFERCOUNT = ' + CONVERT(nvarchar(10), @BufferCount) + N'
    , BLOCKSIZE = ' + CONVERT(nvarchar(10), @BlockSize) + N'
    , MAXTRANSFERSIZE = ' + CONVERT(nvarchar(10), @MaxTransferSize) + N'
    ' + CASE WHEN @Checksum = 1 THEN N', CHECKSUM' ELSE N', NO_CHECKSUM' END + N'
    ' + CASE WHEN @CopyOnly = 1 THEN N', COPY_ONLY' ELSE N'' END + N'
    ' + CASE WHEN @Compression = 1 THEN N', COMPRESSION' ELSE N', NO_COMPRESSION' END + N'
    ' + CASE WHEN @Format = 1 THEN N', FORMAT' ELSE N', NO_FORMAT' END + N'
    ' + CASE WHEN @Init = 1 THEN N', INIT' ELSE N', NO_INIT' END + N'
    , STATS = 10';
    PRINT @cmd;
    PRINT N'';
    PRINT @DelCmd;
    IF @DebugOnly = 0 
    BEGIN
        SET @StartTime = GETDATE();
        --INSERT INTO #output (txt)
        EXEC sys.sp_executesql @cmd;
        SET @EndTime = GETDATE();
        INSERT INTO ##backup_test_results (DBName, StartDate, BackupPath, StripeCount, [BufferCount]
            , [BlockSize], [MaxTransferSize], [Checksum], CopyOnly, [Compression], [Format], [Init]
            , Duration, IsDefaultBackup)
        VALUES (@DatabaseName, @StartTime, @BackupPath, @CurrentStripes, @BufferCount, @BlockSize
            , @MaxTransferSize, @Checksum, @CopyOnly, @Compression, @Format, @Init
            , DATEDIFF(MILLISECOND, @StartTime, @EndTime), CASE WHEN @BufferCountLoop = 0 THEN 1 ELSE 0 END);
        PRINT N'Duration in MILLISECONDS: ' + CONVERT(nvarchar(10), DATEDIFF(MILLISECOND, @StartTime, @EndTime));
        IF @DelCmd <> '' 
        BEGIN
            INSERT INTO #output (txt)
            EXEC sys.sp_executesql @DelCmd;
        END
    END
    SET @CurrentStripes += 1;
    PRINT N'';
END

SET @BlockSize = @BlockSize * 2;
END

SET @MaxTransferSize = @MaxTransferSize * 2;
END

PRINT '===============================================================================================';
SET @BufferCountLoop = @BufferCountLoop * 2;
IF @BufferCountLoop = 0 SET @BufferCountLoop = 4; --reset the special case so we can try non-default-buffer-count backups
END

END

--DBCC TRACEOFF(3213, 3604);

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:

SELECT *
FROM ##backup_test_results;

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:

SELECT p.DBName
    , BufferCount = 'Default'
    , p.MaxTransferSize
    , p.BlockSize
    , p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10]
    , AvgDuration = (p.[1]+ p.[2]+ p.[3]+ p.[4]+ p.[5]+ p.[6]+ p.[7]+ p.[8]+ p.[9]+ p.[10]) / 10
FROM (
    SELECT btr.DBName
        , btr.BlockSize
        , btr.MaxTransferSize
        , btr.StripeCount
        , btr.Duration
    FROM ##backup_test_results btr
    WHERE btr.IsDefaultBackup = 1
) src
PIVOT (
    MIN(src.Duration)
    FOR src.StripeCount IN (
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
    )
) p
ORDER BY p.DBName
    , p.MaxTransferSize
    , p.BlockSize;


SELECT p.DBName
    , p.BufferCount
    , p.MaxTransferSize
    , p.BlockSize
    , p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10]
FROM (
    SELECT btr.DBName
        , btr.BufferCount
        , btr.BlockSize
        , btr.MaxTransferSize
        , btr.StripeCount
        , btr.Duration
    FROM ##backup_test_results btr
    WHERE btr.IsDefaultBackup = 0
) src
PIVOT (
    MIN(src.Duration)
    FOR src.StripeCount IN (
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
    )
) p
ORDER BY p.DBName
    , p.BufferCount
    , p.MaxTransferSize
    , p.BlockSize;

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.