At in order to understand how we accumulate these waits over time first we need to know how  SQL server backup works

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

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)so there are lot of parameters considered here when we have buffer and io waits first and important thing is the backup media

everything depends on the backup media some times it is very slow when they use tape devices which accumulates the ASYNC_IO_COMPLETION , so the data buffer is waiting to become free buffer but it can not as backup device is slow so the writer thread has to wait for the IO operation to complete on the backup device

Always try to avoid the network backup,

Always test different test scenario using different buffer counters

This is not considered a best practical solution in case of VLDB proper testing should be performed



Technical Case Study-Backup VLDB Over Network_Final


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s