Sql-server – What perfmon counters should I use to get the fastest restore possible on an idle system

perfmonperformanceperformance-tuningrestoresql serversql server 2014

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?

Best Answer

If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx). This can also be less resource-intensive than a full restore.

The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot was taken so it can be significantly faster than a normal restore in many cases. Like a regular full restore, the log is initialized so keeping the log size small will reduce the restore time.

Related Question