This is a simple thing every DBA needs to do when they are done with installing SQL server. However it didn’t seem to be as popular as it should have been and I found out that most DBA knew about this only when Microsoft made it part of the SQL Server install process for SQL 2016.
The feature in question is Fast File Initialization.
The Theory
Before we begin on how this improves the speed of the database restores it might make more sense to understand what this feature can do for you. Fast file initialization is an OS feature which prevents the OS from preemptively zero filling free space before writing to it. Essentially when free space is being recovered such as in the case of auto growth etc. the OS first wipes out the database in the space allocated before writing to it. For small file size this process is hardly noticeable but when the database MDF, LDF file has to grow significantly it can add a serious overhead.
The Problem
E.g. when the database MDF file is already 200 GB and now needs to be restored Windows first has to grant and Zero fill 200GB space from the available free space in the disk and then grant the same to SQL Server. In this case the restore has to wait a significant amount of time before the actual file restore happens. The best way to identify this issue is to look for the resource wait.
As we can see the restore process is waiting for the IO process to complete. The query to identify his is given below:-
SELECT wait_type AS [WaitType] FROM sys.dm_exec_requests WHERE Command LIKE '%RESTORE%'
The Solution
Slow disk speeds can affect the performance of the restore or any disk based activity but in this case we assume that the issue is with the lack of Fast file initialization as was for a client of mine for whom we were doing migration recently.
The solution actually doesn’t have much to do with SQL Server actually even though it has a great impact on the performance of the system.
First you need to grant the required permissions to the SQL Server Service account which in this case is
Perform volume maintenance tasks
Which can be granted by going to START>RUN>gpedit.msc>Computer Configuration>Windows Settings>Security Settings>Local Policies>User Rights Assignment>
Double click the permission and the add the user and then restart SQL SERVER to have the changes take effect. And that’s it.
For our client a database of around 700GB was taking over 10 minutes to complete even 10 percent of the restore. After that above we were able to improve the restore time so that the whole thing completed in 20 minutes.
Please Consider Subscribing
