Incorrect BufferCount data transfer option can lead to OOM condition

SQL Server Books Online holds information on a variety of topics concerning the product which can overwhelm any user. Today, I shall demystify two parameter options for the most commonly automated DBA activity: Database Backups. SQL Server database backups can be taken using one of the following methods:

1. Native backup using SSMS GUI or T-SQL Backup Database command

2. Third party backups using either APIs exposed via SQLVDI.DLL or VSS Snapshot backups

When you are creating the BACKUP command to be sent to the server, you have the option of specifying the values for the two data transfer options: MAXTRANSFERSIZE and BUFFERCOUNT.

Books Online gives the following definition for these two options:

BUFFERCOUNT = { buffercount | @ buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

Based on the values that you provide or do not provide, SQL Server specifies the size contiguous buffers that it will use to perform the backup. This is of utmost importance in 32-bit environments as large amount of contiguous memory allocation can prove to be fatal to the non-Buffer Pool region of the SQL Virtual Address Space. This can cause your backups to fail. When SQLVDI is being used, this is of utmost importance because we cannot change the MAXTRANSFERSIZE after the VDI Configuration has been completed. The amount of contiguous virtual memory is determined by the number of backup devices and by the number of volumes on which the database files reside on.

You can use Trace Flag 3213 to review your backup/restore configuration parameters while performing a backup/restore operation. I shall proceed to show you how specifying the incorrect BUFFERCOUNT values or not providing it can prove to be fatal.

Trace Flag 3213

WARNING: This trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.

I am using the following backup command to backup a SQL Server database:

backup database dbBackup

to disk = 'C:\dbBackup1.bak',

disk = 'C:\dbBackup2.bak',

disk = 'C:\dbBackup3.bak',

disk = 'C:\dbBackup4.bak',

disk = 'C:\dbBackup5.bak',

disk = 'C:\dbBackup6.bak'

with stats = 1, format, buffercount = 10, maxtransfersize = 4194304

I am explicitly specifying that the buffer count will be 10 and the max transfer size will be 4MB.

The output of the trace flag 3213 shows me the following information. I ended up using 40MB of buffer space to complete the backup.

Backup/Restore buffer configuration parameters

Memory limit: 3954MB

Buffer count: 10

Max transfer size: 4096 KB

Min MaxTransfer size: 64 KB

Total buffer space: 40 MB

The above behavior is already mentioned in the Books Online snippet mentioned above. We now need to see what happens when a buffer count is not specified. Let’s assume that I attempted this operation without a buffer count value. The value for buffer count will now increase.

Backup/Restore buffer configuration parameters

Memory limit: 3954MB

Buffer count: 26

Max transfer size: 4096 KB

Min MaxTransfer size: 64 KB

Total buffer space: 104 MB

If you look at the above output you will find that the total buffer space has now jumped to 104MB. This is because the buffer count has increased to 26.

Default Buffer Count Calculation for database backups

SQL Server 2005 and above

(NumberofBackupDevices *GetSuggestedIoDepth) + NumberofBackupDevices + (2*DatabaseDeviceCount)

SQL Server 2000

(NumberofBackupDevices * GetSuggestedIoDepth) + NumberofBackupDevices + (DatabaseDeviceCount)  

This value is rounded up to the nearest multiple of 2 for SQL Server 2000.

DatabaseDeviceCount = The number distinct drives the database files reside on.

Note that the above value for GetSuggestedIoDepth is 3 for disk based backups. The default value returned by thisfunction differs based on type of backup device being used: disk, tape or VDI. The values returned by this function are dependent on the type of backup device (Disk, Tape, VDI) is discussed by Bob Dorr in his post on the SQL Escalation Blog.

Above is a simplified guideline of determining the buffer count value for a database being backed up (full backup) when the buffer count value is not specified in the Backup command?

So, in my case the number of backup devices is 6 and the number of volumes involved is 1. Hence, the buffer count in my example above is 26 [(6*3) + 6 + (2*1)].

I was running the above command on a 64-bit instance of SQL Server. When I switch this over to a 32-bit instance of SQL Server, this becomes a significant number. This will account for nearly one-third of the available non-BPool (MemToLeave) region of SQL Server. I tried to run the above command on a 32-bit instance of SQL Server while writing to 12 backup files instead of 6. Now the buffer count changed to 50 and it failed with the following error:

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Msg 701, Level 17, State 17, Line 1

There is insufficient system memory to run this query.

Backup/Restore buffer configuration parameters

Memory limit: 4029MB

Buffer count: 50

Max transfer size: 4096 KB

Min MaxTransfer size: 64 KB

Total buffer space: 200 MB

My largest contiguous block available in the non-BPool (MemToLeave) region of my 32-bit instance was only 157MB. Hence, the infamous OOM condition.

Most third party backup applications use a Max Transfer Size value but do not specify a Buffer Count size. If you have a very large database with multiple database files spread over multiple volumes and a large number of backup devices as-is the case of most VDI applications to improve backup performance, you can run into Out of Memory (OOM) conditions on 32-bit instances. So, if you are hitting an OOM condition while performing a backup, it would be an idea to check the Total Buffer Space being used for your backup. In such scenarios, you would either reduce the number of backup devices or reduce the number buffer count by explicitly specifying a buffer count value in the Backup command. The following table shows the default buffer count value for various backup/restore operations.

If you want to estimate the amount of Total Buffer memory that a particular full database backup to a physical disk would utilize, it can be calculated using the following T-SQL commands J (for all you DBA folks):

declare @MaxTransferSize float,

@BufferCount bigint,

@DBName varchar(255),

@BackupDevices bigint

-- Default value is zero. Value to be provided in MB.

set @MaxTransferSize = 0

-- Default value is zero

set @BufferCount = 0

-- Provide the name of the database to be backed up

set @DBName = 'dbBackup'

-- Number of disk devices that you are writing the backup to

set @BackupDevices = 1

declare @DatabaseDeviceCount int

select @DatabaseDeviceCount=count(distinct(substring(physical_name,1,charindex(physical_name,':')+1)))

from sys.master_files

where database_id = db_id(@DBName)

and type_desc <> 'LOG'

if @BufferCount = 0

                set @BufferCount =(@BackupDevices*(3+1) ) + @BackupDevices +(2 * @DatabaseDeviceCount)

if @MaxTransferSize = 0

                set @MaxTransferSize = 1

select 'Total buffer space (MB): ' + cast((@Buffercount * @MaxTransferSize) as varchar(10))

Reference:

How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

https://blogs.msdn.com/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx

How It Works: How does SQL Server Backup and Restore select transfer sizes

https://blogs.msdn.com/psssql/archive/2008/02/06/how-it-works-how-does-sql-server-backup-and-restore-select-transfer-sizes.aspx

Regards,
Amit Banerjee
Support Escalation Engineer, Microsoft SQL Server.

Comments

• Anonymous
  May 05, 2010
  good!

• Anonymous
  May 06, 2010
  Good stuff, http://www.sqlservermanagementstudio.net

• Anonymous
  March 10, 2014
  Thanks Amit that was helpful specially the formula.

• Anonymous
  June 04, 2014
  nice article amit

• Anonymous
  September 21, 2014
  Hello Amit! I've been testing backups in SQL Server 2014(Windows Server 2012 R2).  When testing backup to disk=N'NUL', it seems that GetSuggestedIoDepth is 4 rather than 3.  So with my 8 LUN setup for database files, the backup to NUL uses (1 backup device * GetSuggestedIoDepth=4) + 1 backupdevice + (2 * 8) = 21 buffers rather than (1 backup device * GetSuggestedIoDepth=3) + 1 backupdevice + (2 * 8) = 20 buffers Is that an increased GetSuggestedIoDepth value for SQL Server 2014?  Specific to just NUL disk, or for all disk targets? Thanks!

• Anonymous
  November 26, 2014
  Sorry about getting to this late. I had missed an additional calculation in this. The suggested io depth remains 1 but an additional buffer is added for queuing. Will have this post corrected. Thanks for pointing this out.

• Anonymous
  May 02, 2015
  Hi Amit, It is very helpful article. Would you please advise if one can use the same formula for the restore. suppose i have backup (multiplexed backup) consisting on 3 files while restoring I will keep those three files in one disk (not in three). What parameter i can tune to restore my backup with lesser time. best regards khalil