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.
The virtual device interface (VDI) backups allow 3rd party back-up solutions to integrate with the SQL Server. This means you don't have to use the built in SQL Server output destinations. You can use solutions that place the backup on alternate storage, snapshots, use compression and other alternatives.
A common issue Microsoft SQL Server support sees is out of memory conditions. SQL Server uses the majority of the memory for the buffer pool. The remaining virtual address space has been referred to as memory outside buffer pool and memory to leave ( MemToLeave ). When SQL Server starts up all but 256MB + (sizeof(stacks) * max worker threads) is reserved by the buffer pool. On an x86 installation the calculation results in ~386MB left outside the buffer pool.
Backup and restore work very similar so I will only cover the backup example in this post.
When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The target is commonly 4MB for each buffer resulting in approximately 4 to 8 buffers. Details about the calculation are located in: http://support.microsoft.com/kb/904804/en-us
The buffers are transitioned between the free and data queues. The reader pulls a free buffer, fills it with data and places it on the data queue. The writer(s) pull filled data buffers from the data queue, process the buffer and return it to the free list.
You get a writer per backup device, each retrieving from the data queue. So a a backup command with four(4) to disk specifications will have four writers and a reader. The reader uses async I/O so it can keep up with the writers.
For more details about the buffer sizes and counts refer to: http://msdn2.microsoft.com/en-us/library/ms186865.aspx For VDI the max transfer size is locked in when the VDI component completes the ' Configure ' operation.
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.
The total space used by the buffers is determined by: buffercount * maxtransfersize .
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }
Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
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 ran a backup of pubs on my machine using the trace flags 3213 and 3605 as outlined in my previous post about how to tell what backup is doing: http://blogs.msdn.com/psssql/archive/2008/01/23/how-it-works-what-is-restore-backup-doing.aspx ... . The following is logged in the error log showing the backup buffer decision made.
2008-01-28 09:40:50.910 spid52 Backup/Restore buffer configuration parameters
2008-01-28 09:40:50.910 spid52 Memory limit: 3325MB
2008-01-28 09:40:50.910 spid52 Buffer count: 6
2008-01-28 09:40:50.910 spid52 Max transfer size: 64 KB
2008-01-28 09:40:50.910 spid52 Min MaxTransfer size: 64 KB
2008-01-28 09:40:50.910 spid52 Total buffer space: 0 MB
2008-01-28 09:40:50.910 spid52 Buffers per read stream: 6
2008-01-28 09:40:50.910 spid52 Buffers per write stream: 6
2008-01-28 09:40:50.910 spid52 Tabular data device count: 1
2008-01-28 09:40:50.910 spid52 FileTree data device count: 0
2008-01-28 09:40:50.910 spid52 Filesystem i/o alignment: 512
Output Target
It does not matter if you are performing a disk, tape or VDI based backup. The buffer exchange works the same. The writer accepts the buffer and processes it as previously described.
In the case of VDI the buffers are exchanged with the GetCommand loop of the VDI provider. A write command will provide a data buffer to the VDI provider. When the provider calls CompleteCommand the buffer is returned to the free list.
Other memory used by the VDI provider will be allocated from memory outside the buffer pool and is up to the provider to control. If the provider is doing additional buffering, compression or other activity refer to the vendors documentation on how much memory they need to sustain the backup/restore activity.
The VDI Specification can be downloaded from: http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&D... . Review the ' Configuration ' section for more details about backup buffer counts and sizes.
Downgraded Buffer Sizes
When larger buffer sizes can't be obtained because of the memory outside the buffer pool is fragmented or under pressure you end up with smaller sizes and more I/O requests. This can change the performance of the backup or restore: http://support.microsoft.com/kb/904804/en-us .
Free and Thaw I/O
VDI is often used with snapshot backup implementations. To allow a snapshot/mirror to be properly split the I/O to the database must be stopped (frozen). The backup solution can then separate the data and tell SQL Server to restart the database I/O (thaw).
This commonly happens in a very short time frame. Just be aware that when the I/O is frozen, all connections needing to read or write a page to any database file are held until the activity completes.
SQL Server 2005 updated its handling of the free and thaw behavior. Version of SQL Server before SQL Server 2005 could encounter a freeze of all databases even though only a single database snapshot was occurring.
The freeze and thaw activity is logged in the SQL Server error log.
Bob Dorr
SQL Server Senior Escalation Engineer