question

reuvygroovy avatar image
0 Votes"
reuvygroovy asked reuvygroovy answered

RESTORE DATABASE Performance

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

reuvygroovy avatar image
0 Votes"
reuvygroovy answered TomPhillips-1744 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It is the way Windows accesses files across the network.

1 Vote 1 ·
reuvygroovy avatar image
0 Votes"
reuvygroovy answered

You have by change any documentation on this?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.