Tips to Improving SQL Server Backup Performance
Backups are one of the most important and core DBA task which every DBA needs to expertise. If there are any short comings or issues with backup planning, implementation, monitoring and testing, then it can result in data loss or can increase the time taken to recover the data with data loss. SQL Server supports multiple databases on SQL Server instances with each database size can be small of few MBs to very large in 100s of GBs to Tera Bytes TBs of late. There is additional care to be taken to plan and implement back strategy for large databases as the time taken to perform the backup increases significantly, so we need to diligently use the different backup methods available to backup the large databases like performing weekly full backups, daily differential backup and every 10-15 minutes log backups. The backups are important even when there are High availability and disaster recovery solutions implemented.
As a DBA some times we may need to look into possibilities of speeding up the backup process to save time taken for the backups of critical databases which are used 24×7. Performing backup can slowdown the databases to an extent due to IO load put by the backup on reading from database files and writing to backup file. There are different ways which we can consider to speed up the SQL Server database backup process to avoid problems for critical servers. Some of the methods or things which we can consider are below. It is important to note that some of the below mentioned options may be available only in latest versions of SQL Server instances, like backup compression available only starting with SQL Server 2008.
– Starting with SQL Server 2008, Microsoft introduced compressed backup. Using the backup compression not only reduces the size of the backup files, but also reduces the amount of IO performed to write to backup files, thus reducing the amount of time taken to perform the backups.
– SQL Server backups allow us to perform backup of one database into multiple backup files, thus increases the use of parallelism which speeds up the backup process. If we point the multiple backup files to different disk drives, then this will further spread out the IO load and makes the backup even faster. We can split database backup to be performed to many backup files, but we need to find the best number of files, as if we use too many files can impact negatively too.
– Some environments, it is common to perform backup directly to the network share or tape drives, which will cause the backup to take more time because the data has to be moved across network. If the backup of large databases is taking long time, then we can consider performing backup locally first to dedicated backup drive and then move the backup file from local backup drive to backup share or tape drive.
– We need to ensure that file system level backups and SQL Server backup timings does not overlap, as both running together will slowdown the system and causes the backup to take more time.
– For large databases we can choose alternative backup plans like performing weekly full backups, daily differential backup and every 10-15 minutes log backup, this we can restrict long time of full backups to weekly once. Differential backups are often fast compared to full backups. It is important to move all full, differential and log backups to backup share or tape so that they can be used in case the server or drive where backups are performed is lost or corrupted.
– We can also use some of the backup parameters like BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE to improve the backup performance. There is no good or bad values for these parameters, it is trial and error approach, where we need to test different values and come to conclusion on what works best for your environment.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings