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.
Type of Operation |
Default BufferCount |
Backup Database |
(NumberofBackupDevices * (1+GetSuggestedIoDepth) ) + NumberofBackupDevices + (2*DatabaseDeviceCount)
|
Restore Database/File |
(NumberofBackupDevices * (1+GetSuggestedIoDepth)) + (2*DatabaseDeviceCount)
|
Backup/Restore Log |
(NumberofBackupDevices * (1+GetSuggestedIoDepth) ) + (2* NumberofBackupDevices)
|
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)
How It Works: How does SQL Server Backup and Restore select transfer sizes
Regards,
Amit Banerjee
Support Escalation Engineer, Microsoft SQL Server.
Comments
Anonymous
May 05, 2010
good!Anonymous
May 06, 2010
Good stuff, http://www.sqlservermanagementstudio.netAnonymous
March 10, 2014
Thanks Amit that was helpful specially the formula.Anonymous
June 04, 2014
nice article amitAnonymous
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