Optimize SQL Server Database Restore Performance


By:   |   Updated: 2017-08-04   |   Comments (9)   |   Related: More > Restore


Problem

I needed to restore a database backup that was around 24 GB.  I was trying to figure out how to make the restore run faster and I found some additional restore parameters that could be used.  I tested these out and was able to make the restore complete faster and I will cover what I did in this tip.

Solution

The database backup was around 24GB and this was taking about 18 minutes to complete.  By using some additional parameters I was able to make the restore compete about 40% faster.

Review the backup file’s health and size

First, I am going to check the health of database backup file to make sure it is not corrupt using the RESTORE VERIFYONLY command. As we can see below, the backup is valid.

Backup verification - Description: Check backup, its valid or not !!

Next, I am going to check the size and file details of the backup file, so I will run a RESTORE FILELISTONLY command for getting the backup file details.  As you can see below, there are 2 files listed along with other attributes including the size of the file.

Check file list from backup file - Description: Cheking the files from DB backup

Configure Trace Flags to Return More Information

For the purpose of viewing database backup and restore statistics, I will configure two trace flags globally.

This trace flag outputs information for backups and restores.

DBCC TRACEON (3213, -1)

This outputs the information to the SQL Server error log.

DBCC TRACEON (3605, -1)
Trace configure commands - Description: Command for configure trace flag globally. Here configured trace flag 3213,3605.

Database Restore with Default Settings

 Now I am going to restore this database with the following restore command.

Restore Database Test with default settings - Description: Restore Database Test with default settings

The database has been restored. This restore process completed in 18 minutes and 44 seconds.

Now I am going to check the restore related information from the error log using xp_ReadErrorLog to just return the information for the last hour.  The information below was captured by turning on the trace flags above.

Read error log - Description: Get Backup/Restore buffer,size configuration details from errorlog

With the default settings, I can see the Max Transfer Size is 1024 KB and the Buffer Count is 6 and the Total Buffer Space is 6 MB. This is calculated as follows Total Buffer Space = Max Transfer Size * Buffer Count.

The other thing to note is the Memory Limit.  We can see the Memory Limit is 506MB, but the Total Buffer Space being used is only 6MB.  So, we will adjust some settings to increase the Total Buffer Space to see if we can speed up the restore.

Database Restore with Custom Settings

For more clarity, I am going to restore the database by using some additional parameters. I am going to adjust the Max Transfer Size and Buffer Count to see if we can make the restore run faster by using more memory.

The Max Transfer Size has a maximum of 4032 KB or 4 MB, but the Buffer Count can be adjusted higher.  So to determine this we want to stay within the Memory Limit of 506 MB.  So we can take 506 MB / 4 MB = 126 as the maximum we should use for the Buffer Count.

For this example though, I am only going to adjust the Buffer Count to 24, but use the maximum for the Max Transfer Size.

Restore Database Test with propery maxtranfersize,buffercount - Description: Restore Database Test with property Maxtransfer and Buffercount

The restore with these additional parameters took 11 minutes and 2 seconds instead of the previous 18 minutes and 44 seconds. I have concluded that we can restore the database faster, if there is sufficient memory and server availability.

By using these additional parameters, the restore was faster based on availability of memory and server specifications. This database backup was 24 GB which worked well for this restore, but when restoring larger backups you may need to adjust these values otherwise you may get errors.

Now I am going to check the error log for this restore using xp_ReadErrorLog for last 1 hour.

Read error log with applying option parameters - Description: Get Backup/Restore buffer,size configuration details from errorlog

I can see the Max Transfer Size is now 4032 KB and the Buffer Count is 24 and the Total Buffer Space is now 94 MB.

To even get faster backups and restores, you could also look at these things:

  • Parallel backup operations
  • Backup device performance
  • Instant file initialization
  • Data compression
  • Backup compression

To be more specific, check out this article on MSDN Optimizing Backup and Restore Performance in SQL Server.

Other things to note:

  • When running the restore with these additional parameters and there is other server activity it could affect server performance.
  • Prior to running, you should check memory and server resources before configure it to run automatically.
  • I would suggest that you first run in a test environment before rolling out to production.
Next Steps
  • Check out these restore tips.
  • Check out these backup tips.
  • You can also turn on the Trace Flags and just do a RESTORE VERIFYONLY to get the output in the SQL Server Error Log to find the settings.


Last Updated: 2017-08-04


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips
Related Resources





Comments For This Article




Thursday, October 03, 2019 - 12:36:35 PM - Clinton Back To Top (82660)

I'm curious, I have seen other posts where the same methodology is used for the Backup. This is to assume setup of transfer size, buffercount, blocksize, compression. When restoring a backup do i need to use the same variables I used for creating the backup? For instance in your example, would the backup also be Max Transfer Size is now 4032 KB and the Buffer Count is 24?


Sunday, November 18, 2018 - 5:58:34 AM - Bhavesh Patel Back To Top (78282)

Thanks,

it's took size as per demonstration purpose, You can configure max transfer size with respect memory limit and buffer size.
You can also increse by step and monitor log what should acquired base value then you will definetly get configuration value according your environment.


Thursday, November 15, 2018 - 2:24:26 AM - Vikrant More Back To Top (78251)

 Could you please let us know how did you calculated Max Transfer Size= 4194302 ?


Friday, August 04, 2017 - 8:53:29 AM - Jagdish Patel Back To Top (63961)

Very useful Script.

 


Friday, August 04, 2017 - 8:45:47 AM - Nikhilesh Back To Top (63960)

 

 Nice Tips , Really help me while taking minimal for restoration!!

 


Friday, August 04, 2017 - 8:44:53 AM - Ankit Back To Top (63959)

 

This Tips is very useful.

 

Good Job.


Friday, August 04, 2017 - 8:37:26 AM - Ranga Back To Top (63958)

Even virtual log file count(more than 1000) can hinder the restore performance.


Friday, August 04, 2017 - 6:04:49 AM - Ranag Back To Top (63950)

Nice one Bhavesh


Friday, August 04, 2017 - 5:23:11 AM - Nico Botes Back To Top (63948)

 Nice, thank you!  Just in addition to this, I would also check this out, to be aware of trying this on "older" versions of SQL when there is a large amount of RAM and buffer pool: https://blogs.msdn.microsoft.com/psssql/2017/06/29/sql-server-large-ram-and-db-checkpointing/

Workarounds such as manual CHECKPOINT and DBCC DROPCLEANBUFFERS, should be used with extra care, on top of enabling DPM implementation as described in the blog-post link above.



download





Recommended Reading

Identify when a SQL Server database was restored, the source and backup date

How to migrate a SQL Server database to a lower version

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Make Network Path Visible For SQL Server Backup and Restore in SSMS

SQL Server Database Stuck in Restoring State








get free sql tips
agree to terms


Learn more about SQL Server tools