Data Transfer options

DonFerguson

From SQL 2012 BOL...

Data Transfer Options
The options enable you to optimize data transfer from the backup device.

BUFFERCOUNT = { buffercount | @buffercount_variable }
Supported by: RESTORE

Specifies the total number of I/O buffers to be used for the restore 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 }
Supported by: RESTORE

Specifies the largest unit of transfer in bytes to be used between the backup media and SQL Server. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).


Key point is that SQL Server supports a value up to 4194304 bytes form MaxTransferSize whereas SQLBackup limits this value to 1048576 bytes. It also doesn't have an option for buffercount.

The reason I ask is that I am trying to reduce the restore time of the REDO phase of a highly active transaction log. I have read that increasing these values can help that.

petey

During the development of SQL Backup, we found that it made very little difference in backup throughput when bumping up the buffer size from 1 MB to 4 MB, but it increased the memory requirements fourfold.

We also found out that errors sometimes occur when during a restore, a MAXTRANSFERSIZE value that's different from the value used during the backup is used. Thus, to avoid situations where the secondary server cannot meet the memory requirements and fail to restore the backup, we decided to limit the MAXTRANSFERSIZE to a conservative 1 MB.

Could you please post the link to the article you mentioned?

Thank you.

DonFerguson

http://sirsql.net/blog/2011/9/26/optimi ... tores.html

BTW, I did find that increasing the thread count helped a lot too. It did increase the buffer count to by 6 * Threadcount up to 28 buffers max, which sped the redo phase up. But I do think that that if SQL Server allows up to a 4MB transfer size natively then Red Gate SQL Backup should allow it too. I'm not suggesting that you change the 1MB default, but should allow for manual adjustment of MaxTransferSize and buffercount parameters.