In this article I will try and cover the concepts in understanding the amount of memory or buffers required for completing a database backup. I would be using a couple of trace flags to get that detail and further discuss two of the parameters MAXTRANSFERSIZE and BUFFERCOUNT in the BACKUP DATABASE TSQL syntax which will help us understand a bit more about memory consumption for a database backup and time involved in completing the backup.
Throughout I will be working on a test database called DBA that is around 4GB of size. To get started ,Lets run a backup and see how long it takes to complete.
As you can see it has taken around 50 seconds to complete the backup.
Now lets turn the following trace flags 3605 and 3213 ON.
TRACE FLAG 3605: This flag is similar to 3604 which is the most commonly used trace flag for troubleshooting that sends the output of a trace to the client. And 3605 will send the trace to SQL error logs.
TRACE FALG 3213: This trace flag gives information about backup/ restore throughput and other configurations.
This trace flag 3213 should be used under the guidance of SQL Server PSS team; they are used in this post only for discussion purposes.
As we have turned on 3605 we can see some information regarding our backup in error logs. Lets read and analyze the error logs.
From above image, we now have the trace flag 3213 enabled when the backup was run for the second time and entries starting from Backup/Restore buffer configuration parameters all the information printed in the error log are from the mentioned trace flag.
The red box represents the available memory in the system (largest contiguous memory block in Windows & is non-SQL server buffer pool memory) to run this backup command ,so SQL server will choose a configuration such that the total buffers required are less than what is available in the system. Next one with green box is the total number of buffer count that SQL server has chosen for this backup operation which is 7 in this case there is a formula which SQL server uses for calculating this which I am not covering here and available in SQL PSS blog. The brown box represents the max transfer size with which the backup will be dumped to the disk or media. The last blue box representing total buffer space is obtained by buffer count multiplied with max transfer size.
Total buffer space = Max transfer size * buffer count => 1024*7= 7, so 7MB is the amount of memory needed to process this backup command. Read more about these parameters from this link http://msdn.microsoft.com/en-us/library/ms186865.aspx
Now that we have seen how to calculate the amount of memory buffers required to run a backup or how much was actually taken, lets try specifying these parameters in BACKUP DATABASE command and see if that makes any difference in time taken to complete the backup.
Well, the total buffer required now is 100 MB because of the increased buffer count value and max transfer size and it took 59 seconds to complete the backup now. The time taken to complete the backups depends on other factors such as i/o alignment , kind of media the backup is taken and changing these parameters in a VLDB backup with compression will help reducing the time taken to complete a backup with proper configuration settings considering all influencing factors , which is discussed by SQLCAT team here http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx
What happens if you specify these values too higher?
This is one important issue with these settings specified in the backup statement, if the amount of memory required to execute the backup is more than what is the non-buffer pool region and machine as a whole has it can cause Out Of Memory errors and often likely to happen in a 32 bit environment where the process address space for any application is 2GB with the default settings and MTL being much more less.
If you are running SQL server 2008 + versions of SQL server, you can also use extended events to capture the same information we captured using an unsupported trace flag. The event databases_backup_restore_throughput gives us all such information. Both the methods works for RESTORE commands too.