Impact of Pre-grown Data & Log Files on the Restore Time of the Database

Today, I was asked the following question by one of my colleague who specializes in Sharepoint

If the actual data is 50GB in size & if we create a pre-grown data file of 200GB to avoid autogrow & fragmentation, the database restore time for the pre-grown database should be much larger than the restore time of the database without pregrowth with  datafile size of 50GB.

My initial guess was, the restore time shouldn’t vary too much since the data file creation should be fast due to instant file initialization, while the database restore operation only restores the active pages with data which should be same in both the scenarios & hence restore time shouldn’t vary too much. However this was purely based on my theoritical knowledge and it was always important to validate & test it.

So I used a sample database with initial primary datafile size of 4GB  & Log File size of 1GB with around 3GB of active data within the database.

To see the impact of the pre-grown datafiles, I kept the initial log file size constant to 1GB, while increased the initial datafile size in increments of 5 GB  keeping the active data in the database  constant at 3GB.

For my testing, I used SQL 2012 with SP1 & further I used backup compression while taking the backup each time I have pregrown the data file. As noted in the table above, irrespective of the data file size, the backup size was consistent at 266MB since the data was consistent & static in the database which was as per the expectation.

However, the restore time increased proportionally as the initial data files size increases which didn’t make much sense.

So I turned on the Trace Flag 3004 & 3605 to trace the restore events and identify why the restore time varies during each restores.

After turning on the Trace Flags, from the errorlog I discovered the following events which justified the time difference between the restores

Errorlog for Restore with 5GB data file size

Errorlog for Restore with 20GB data file size

As seen in the data above, the additional time spend during each restores were due to Zeroing of data files which means the instant file initialization was not working for me. I realized my SQL Server was running using the managed service accounts (NT Service\MSSQLServer) which does not possess Perform Volume Maintenance Task permissions by default. I assigned  the permissions to the NT Service\MSSQLServer and restarted the SQL Server & performed the above tests again. The following results were found

The above results does make sense & meets our expectation since with Instant File initialization, zeroing of the datafile is avoided which makes the restore time more or less same.

However instant file initialization doesn’t apply to Logfiles and we see the following results when we test the restore, keeping the initial size of data file constant while increasing the initial size of the Log file.

So the restore time does increase with the increase in the Log File size. Again when I turned on the tracing  to identify where the majority of the time is spent, I found that major time is spent in zeroing of log files.

However in case of the Log Files, the performance advantages of pre-grown Logfiles is significant due to number of VLFs created for every Autogrow  can slower the Database recovery time proportionally.

Hence with instant file initialization for data files & some compromise for restore time, it would still be recommended to pre-grow the data file & log files for the database to avoid Auto grow & fragmentation on the disk.

Autogrow should be used as insurance which you would want to have but not use it until you are in the dire need of it.

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer