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 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)
I read at many places but, I really don’t get 100% of what is mentioned above to calculate buffercount, please help me to understand it, as I am not an expert in SQL administration.
Also, is there an impact of the SQL version on buffercount?
- 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.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.
