Forum Discussion
Easiest way to calculate buffer count for faster SQL server backup
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
WITH STATS = 1, COMPRESSION, CHECKSUM, buffercount=20, maxtransfersize=2097152
GO buffercount = NumberofBackupDevices* 3 +...
There must be throughput threshold of your strage box.
so even you increase the buffer count number to the maximum.
it will reach the throughput limitation of your storge box.
so i suggest you to monitor your storage box while your backup is running and change a buffercount from lowest count to highest and check the changes of stroage performance.
so even you increase the buffer count number to the maximum.
it will reach the throughput limitation of your storge box.
so i suggest you to monitor your storage box while your backup is running and change a buffercount from lowest count to highest and check the changes of stroage performance.
mcdasa Thanks for the reply, but I am not having access to storage, so I cannot able to check its threshold from my end.
Do you have any information about the calculation mentioned? In my case servers are virtual, so disks on it might be coming from the same storage or different ones, how to calculate the real number of drives involved in backup? What has involved disk means here virtual disks or real disks on the storage server?
- you need to think about logical volumes even you're using vm.
do you have an access to terminal? open perfmon.msc through run command and check the disk object counters if you are available.- Yes, I have access to VM and can check performance counters.
For example, if the data file .mdf is in drive D, the log file .ldf is in drive L and the single backup file .bak is created in drive B, so buffercount should be 10?
buffercount = NumberofBackupDevices* 3 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)
buffercount = 1* 3 + 1+ (2 * 3) = 10
By default, MS SQL Server uses 7 as BufferCount checked for my many SQL servers.- this is what we do when we want to perform a backup faster. we change buffercount from 1 to 10(yours will be 7 to 10) and performs backup and we watch a performance counter to check up server uses more storage throughput and so on.
- in my case I configured buffercount to 1 and maxtransfersize to minimal if i wanted to have an performance impact to minimal.