March 30, 2016 at 7:07 am
Hi,
I have been testing out SQL Server 2016 on some VMs so I took a full backup copy of the prod database which is about 1.9 TB and copied it to the VMs for testing. I noticed that during restore, it was taking a long time, but I let it continue to restore to see how long it'll actually take. It took eight hours to finish.
On my testing server (which is a physical server), restores take a little over two hours.
I'm curious why there's such a huge discrepancy between the two restores...
March 30, 2016 at 7:09 am
Backup and restore times are mostly a function of IO throughput. Do the VMs and the physical server have the same IO capabilities?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 7:19 am
Thank you for the quick response!
The VMs and the physical server are on different SAN groups. This is the breakdown of the volumes...
VMs:
SQL Data: RAID 6 Accelerated (Equallogic SAN with a mixture of SAS SSDs and 10k SAS hard drives)
SQL Log: RAID 6 Accelerated
SQL Archive: RAID 50 (Equallogic SAN with 7.2k SAS hard drives)
SQL Backup: RAID 50
Physical Server:
SQL Data: RAID 10 (Equallogic SAN with 15k SAS hard drives)
SQL Log: RAID 10
SQL Archive: RAID 50 (Equallogic SAN with 7.2k SAS hard drives)
SQL Backup: RAID 50
March 30, 2016 at 7:30 am
Version probably not much of a difference. Backup and restore are IO-bound operations. Test the kind of IO throughput you get on the VM, compare to the physical server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 7:48 am
Not offhand, but you can get a quick and dirty check by copying large files from one say the backup drive to the data drive and see how long it takes on each server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 8:08 am
Hence the difference in restore times.
Might be worth having a chat with the VM admins and SAN admins, see if they can determine why there' such a difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 10:25 am
I would also check if you have enabled Instant File Initialization on the restore machine. Without it, your server will zero out every bit of every byte of the entire size of the restored database BEFORE IT STARTS THE ACTUAL RESTORE. I have seen this at clients and on forums so many times I can't count them. 😎
I also recommend you get a professional in to give your systems a health check/performance review.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2016 at 3:16 pm
Sqlio or the newer diskspd should give you an idea of your IO subsystem. There's a number SQL related topics that cover the tools usage, giving warnings/pointers/parameters to use e.g.
http://sqlperformance.com/2015/08/io-subsystem/diskspd-test-storage
http://www.davidklee.net/2015/04/01/storage-benchmarking-with-diskspd/
https://www.brentozar.com/archive/2015/09/getting-started-with-diskspd/
April 20, 2016 at 3:27 pm
Since the thread is almost three weeks old, you've probably already figured this out on your end, but I figured I'd mention a couple things for thoroughness.
In the initial description of the environment, there were a couple details that make it rather unsurprising that writes are much slower for the VMs.
1) The data and log volumes for the VMs are RAID 6, while the same volumes for the physical machines are RAID 10. The write penalty for RAID 10 is much lower than the write penalty for RAID 6.
2) The hard drives for the VM volumes are 10k, while the hard drives for the physical machine volumes are 15k.
I would expect those two alone to account for a large difference between the environments, all else being equal (and all else might not be equal, for example, the number of disks in each of those pools; the above differences would be compounded if there were more disks backing the physical machines than the VMs).
Cheers!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply