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 (
). 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:
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.
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:
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
loop of the VDI provider. A write command will provide a data buffer to the VDI provider. When the provider calls
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.
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:
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
databases even though only a single database snapshot was occurring.
The freeze and thaw activity is logged in the SQL Server error log.