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

  • Article

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog.

A customer asked: "Why does backup to disk choose a transfer size of 1MB but restore 64K unless I specify BUFFERCOUNT and MAXTRANSFERSIZE?"

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.

dbcc traceon(3605, 3004, 3014, 3213, -1)

These trace flags output the following additional information.

2008-02-06 11:59:31.140 spid51 Backup/Restore buffer configuration parameters

2008-02-06 11:59:31.140 spid51 Memory limit: 3325MB

2008-02-06 11:59:31.140 spid51 Buffer count: 6

2008-02-06 11:59:31.140 spid51 Max transfer size: 1024 KB

2008-02-06 11:59:31.140 spid51 Min MaxTransfer size: 64 KB

2008-02-06 11:59:31.140 spid51 Total buffer space: 6 MB

2008-02-06 11:59:31.140 spid51 Buffers per read stream: 6

2008-02-06 11:59:31.140 spid51 Buffers per write stream: 6

2008-02-06 11:59:31.140 spid51 Tabular data device count: 1

2008-02-06 11:59:31.140 spid51 FileTree data device count: 0

2008-02-06 11:59:31.140 spid51 Filesystem i/o alignment: 512

Using SQL Server 2005 SP2 - I took a closer look at what was going on. There are a couple of key locations in the code that I focused on.

ChooseBufferParameters

This routine does all the work to figure out the MAXTRANSFERSIZE and the BUFFERCOUNT values and adjusts the combination of the two to equitably distribute the buffers to each steam that is part of the backup or restore operation.

This routine looks at the MAXTRANSFERSIZE setting. If it is not specified (0) then GetSuggestedIoSize takes over otherwise the MAXTRANSFERSIZE is used (always rounded to a proper 64K extend boundary)                               

  • If we detect this is a database < 10MB with a single device we will limit the suggested size to 64K to avoid additional memory overhead needs in any case.
  • If we detect a LOG backup is from a database in bulk logged mode we treat calculate this type of backup as if it was a database backup when distributing buffers instead of keeping them all focused on the .LDF file(s).

Each device type has its own implementation for GetSuggestedIoSize.

Disk

There are different defaults of read and write operations.

· If device is in read mode(restore) or this is a Desktop or Express Edition use 64K

· If the device is in write mode(backup) and not Desktop and not Express use 1MB (The larger write size reduces file extension needs and NTFS metadata maintenance)

Tape

Backup or Restore uses 64K default unless block size on the tape media indicates a different size should be used.

VDI

Backup or Restore uses 64K default

Each device type has its own implementation when BUFFERCOUNT is not specified.

Each device exposes a GetSuggestedIoDepth member to determine the default when the BUFFERCOUNT is not specified.

Disk

Depth defaults to 3

Tape

Depth defaults to 1

VDI

1 default and 4 max based on device spanning needs

The suggested values are all adjusted accordingly.

Backup Database

bufferCount += backupDeviceCount

bufferCount += 2 * databaseDeviceCount

Restore Database or Restore File

bufferCount += 2 * databaseDeviceCount

Backup Log or Restore Log

bufferCount += 2 * backupDeviceCount

                                                                               

Once the targets are established they are then adjusted for the combination of the transfer size and block counts to make sure each steam has buffers to work with and that the memory is appropriate.

The max size that can be used is the (Buffer Pool’s To Physical Memory / 16). As returned from the GlobalMemoryStatusEx (ullTotalPhys ) API call. This is the total physical memory on the machine so if you have /PAE enabled for a 64GB machine but the instance of SQL Server is not using AWE it is possible that larger physical memory would attempt to allow larger backups just because /PAE is enabled. This issue will be that you are still limited to the memory outside buffer pool and it could limit or fail such a backup attempt with a large MAXTRANSFERSIZE and large BUFFERCOUNT.

If the calculations exceed 1/16th of the memory the size are adjusted to accommodate a reasonable number of buffers and transfer sizes.

I did some testing and the defaults hold up well to maximize the backup and restore performance over various configurations. You may adjust the parameters for a specific system to enhance your performance.

Bob Dorr
SQL Server Senior Escalation Engineer