Sql-server – SQL restore duration

backuprestoresql server

I am trying to determine the duration of backup restore. I executed a few restore command to restore some backups located on network share drive. Here is command and summary,

RESTORE DATABASE [test]
FROM DISK = '\\network\test.bak'
WITH
    MOVE 'test_data' TO 'D:\test_data.mdf',
    MOVE 'test_log' TO 'E:\test_log.ldf',
    CHECKSUM, STATS;

Backup A
1st restore take about 5 min. Ok, after restore complete, I delete the database
2nd restore take about 1.8 min. Hm.. Let's delete the database and try again.
3rd restore take about 1.5 min. Hm..

Backup B (almost same size as backup A, at the same network location)
1st restore take about 1.8 min. I deleted it and try again
2nd restore take about 1.5 min. Ok..

The service account is enabled for instant file initialization.

My question is why the restore duration varied on different restore attempt on the same backup file to the same machine? Is it purely on network throughput (perhaps someone was doing something on the network share and stuff), or something else like cache or something in SQL internal?

Best Answer

Some of it will have to do with caching on the remote machine. You've also got to account for what else is flying across the network. Also some of those blocks may be in cache on the SQL Server's disk cache (not SQL's buffer pool) as they are the same exact blocks the disk may use the cached copy not the actual page you are restoring.

Don't forget things like witches, gremlins, boogie men, etc.

Related Question