I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk.
I want to restore database as quickly as possible as part of a "reset my dev environment" process. The system is mostly idle when I do this. The system runs SQL Server and Tomcat 7. I usually have an RDP session which has one or more of the following open Perfmon, SSMS, Visual Studio 2013, Visual Studio 2010. Process Monitor. I'm willing to tune this restore to the point where this RDP session becomes completely unresponsive during the restore process. No one but me is using this server.
Using Nic Cain's post I was able to get my restore process from 6xx seconds to ~100-120 seconds with the following parameters:
RESTORE DATABASE [XXXX] FROM DISK = N'$(BackupFile)' WITH REPLACE, MOVE N'RMS_SIMULATION' TO N'$(MdbPath)\XXXX.mdf', MOVE N'RMS_SIMULATION_log' TO N'$(LDBPATH)\XXXX.LDF', STATS = 5, MAXTRANSFERSIZE = 4194304, BUFFERCOUNT = 72
By adding MAXTRANSFERSIZE and BUFFERCOUNT I was able to peg the
I/O Data bytes/sec and
I/O Data operations/sec counters to the top of the perfmon chart. Before that, bytes/sec would spike to the top, but spend more time at the bottom of the scale, and operations/sec was spikey in the middle of the scale.
Now I know I can tweak with the numbers and run the restores 100 times at different numbers and measure the restore time. While, there is a certain scientific vigor in that approach, I'd like something more data-centric.
I know I don't want SQL Server page faulting during the restore, because hitting virtual memory is a big slow down. However, what other perfmon counters should I be looking at?