Easiest way to calculate buffer count for faster SQL server backup

Occasional Contributor

 

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?

7 Replies
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.
in my case I configured buffercount to 1 and maxtransfersize to minimal if i wanted to have an performance impact to minimal.
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.
Just to mention commvault a backup solution takes buffercount 20 as default, check the below link.

https://documentation.commvault.com/11.24/expert/61539_properties_of_sql_server_sql_settings.html