RESTORE DATABASE Performance

reuvygroovy 776 Reputation points
2021-04-12T13:52:44.623+00:00

We are trying to restore a DB from a backup taken on ServerA to ServerB.

The .bak file is 55.3GB.

We are using the following TSQL:

ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [DB] FROM  DISK = N'\\share\Backup\DB_backup_2021_04_12_021002_3975291.bak' WITH  FILE = 1,  
MOVE N'DB_Data' TO N'D:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\DB_Data.MDF',  
MOVE N'DB_Log' TO N'D:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\DB_Log.LDF',  
NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE [DB] SET MULTI_USER
GO 

When running in SSMS it takes approximately 4:38 minutes to restore the DB.

If I copy the file locally first, to the same destination drive as the SQL DB currently rests, it takes 1.77 minutes. Then I subsequently restore the file in SQL took another 2 minutes (all together about 3.5 minutes).

When I tried the same thing with a 286GB backup file, it took 19 minutes to (a) copy locally and (b) restore from local disk, as opposed to 1 hour when restoring directly from the network share.

I know the storage/share backend is not a bottleneck, since I can see how long a standard copy takes.

Another anomaly: our source share is a enterprise storage array, when we can see (both in Wireshark) and via the storage monitoring that we are successfully utilizing ODX when copying the file over the network (as was done in the second test). But when we directly restore to SQL from network storage (as opposed to local disk), we don't see any ODX transfers at all!

So, our questions are:

  1. Why isn't ODX being utilized together with SQL?
  2. Could that be the difference to explain the 1-step vs 2-step restore detailed above?
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
0 comments No comments
{count} votes

Accepted answer
  1. Cris Zhan-MSFT 6,601 Reputation points
    2021-04-13T08:27:46.897+00:00

    Hello @reuvygroovy ,

    I am not familiar with the ODX feature in Windows. But I think SQL Server may not use ODX when restoring a database from a remote location, this is not only a simple data transfer, but also a restore operation.

    With the 2-step restore, first copy the backup file to the local with ODX can improve the transmission efficiency, and restore the database locally much faster than restoring from a network share.
    Restoring SQL Server databases from network storage relies on external network resources, and restore performance becomes unpredictable.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-04-12T21:10:09.733+00:00

    File operations are always faster locally than over the network. There are many variables when running across the network and Windows does not use the full bandwidth to access files.

    You should always copy the file locally, if possible.

    1 person found this answer helpful.
    0 comments No comments

  2. reuvygroovy 776 Reputation points
    2021-04-13T05:48:08.52+00:00

    You're saying this is a limitation within SQL Sever or Windows?


  3. reuvygroovy 776 Reputation points
    2021-04-13T13:42:19.227+00:00

    You have by change any documentation on this?

    0 comments No comments