Sql-server – Backup Performance best practices

backupmaintenanceoptimizationsql server 2014standard-edition

I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup performance is optimal. I am aware of implementing the below, however I'd like to get an idea of how I can improve beyond that scope.

Current Considerations:

  • Backup Compression
  • Multiple data files for larger databases – perhaps over 100GB?
  • Writing backups to another I/O subsystem
  • Defining values for parameters including BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT
  • Purging old files
  • Deleting backup history

Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values? I appreciate it will be a bit of trial and error with testing but it would be useful to get an idea of best practices.

To give an idea of my routine, I'm to run hourly transaction log backups, daily differentials and weekly full backups.

Thanks

Best Answer

Start planning to upgrade to SQL Server 2017 as backup operation has been made faster in SQL Server 2017. How Do We Made Backups Faster With SQL Server 2017

Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values?

For SQL Server 2014 I believe you already know most of the things, I would help you in deciding how to choose appropriate value for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT. Here trace flags can be used to dump additional information into errorlog. For example

dbcc traceon(3605, 3004, 3014, 3213, -1)
go
backup database [AdventureWorks2012] to
disk='D:\Backup Parallelism\Adventureworks.Bak'

This command will force SQL Server to write additonal parameters into errorlog about internal backup operations.

Memory limit: 249MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:            7
Media Buffer size:          1024KB

Now if you see for the simple backup the SQL Server internally chooses 7 buffer buckets and and maxtransfer size of 1 MB. You can perform similar test on UAT and can play around with above 2 values. Please note these parameters are selected considering various resources available and SQL Server takes best decision to make sure the backup runs faster. Make sure not to make the values too large or you will end up with OOM error.

Choosing optimum number of backup files is also important. Brent Ozar in This Blog did some test on backup speed with single to multiple files, this may be helpful to you.

Finally some good readings about SQl Server backup and restore operation from MSDN blogs.

PS: The trace flags are undocumented so please use it ONLY in UAT environment. The same is mentioned in the MSDN links I have shared.

Related Question