CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
  • Comments 3

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.

image

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&DisplayLang=en. 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

Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • I know this blog post is a few years old, but I have to respectfully disagree with the paragraph regarding freeze and thaw of I/O with VDI and snapshot technologies.  

    At least with how VDI is called by certain EMC technologies, the freeze performed by VDI does not directly hold reads against database files.  

    Read queries, all the way down to disk (not just queries serviced by buffer cache,) will execute and return complete while there is a VDI freeze against a database.  

    The exception to this... If there are write transactions against specific rows of a database, any read query against those same rows will be held until the write transactions complete.  This is not VDI freezing the reads, this is locking within SQL Server preventing the read to complete until the write transaction (which is frozen) to the same area of the database finishes.

    I hope these posts are still monitored as this particular post is confusing some of our customers.  I'd also happily stand corrected, although I've confirmed the behavior I've described in my lab.

  • So I get the 'freeze/thaw' cycles. However, I really don't like having the SQL Error log flooded with 'I/O is frozen' and 'I/O was resumed' messages. (Using EqualLogic snapshots every 50 minutes creates a lot of unnecessary messaging.)

    Is there a way to suppress these messages -perhaps a trace flag...

  • This is pretty old post but if you can clear this .....

    Did you mean all backup restore choose memory to leave portion of memory or this is only for backup threw third party tools.

Page 1 of 1 (3 items)