Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Optimize SQL Server Database Restore Performance


By:   |   Read Comments (6)   |   Related Tips: More > Restore

Attend these FREE MSSQLTips webcasts >> click to register


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 Update:


signup button

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Very useful Script.

 


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

 

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

 


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

 

This Tips is very useful.

 

Good Job.


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

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

Nice one Bhavesh


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

 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.


Learn more about SQL Server tools