Instant File Initialization(Restore or Allocate Disk Space Faster)

Instant File Initialization is a feature that introduced with SQL Server 2005 and Windows Server 2003-Windows XP. To enable this feature, the SQL Server service account must be granted the necessary rights on Windows. When this feature is activated, operations on the data files of SQL Server can be performed more quickly.

When any of the following operations occurs on SQL Server, SQL Server requests to allocate files from Windows;

1. Creating a database,

2. Adding data files to an existing database,

3. Increasing the data file size of an existing database or performing this operation with autogrowth,

4. Restore a database that does not exist

These spaces requested by SQL Server are filled with 0(zero) and then allocated to the sql server.

Of course, it takes some time to fill them with 0(zero) when the requested spaces are large.

If SQL Server Service account is granted on perform volume maintenance tasks on Windows, then windows give this space to SQL Server without filling with 0(zero).

The process will be accelerated as there is no need to fill these spaces with 0(zero).

Now let’s test the effect when we activate Instant File Initialization.

First, we will perform the operations without authorization and record the completion times. In the second stage, we will perform authorization and check the completion time of the operations.

CPU for the server that I run the tests: Intel Xeon X5670, 2.93GHz,2933Mhz, 8 Core(s)

1) Create Database:

In my test environment, it took 5 minutes and 18 seconds to create a new database with a size of 100 GB when Instant File Initialization was not active with the following command. Of course, this time may increase or decrease according to the power of your server.

After activating Instant File Initialization, the same process took 3 seconds as shown below.

2) Adding data files to an existing database:

Let’s add a data file to a database with a size of 20gb.

When Instant File Initialization was inactive, it took 1 minute and 1 second to perform this operation.

After activating Instant File Initialization, the same process was completed in less than 1 second as shown below.

3) Increasing the data file size of an existing database or performing this operation with autogrowth:

When Instant File Initialization was inactive, it took 1 minute and 42 seconds to increase the size of the file we added.

After activating Instant File Initialization, the same process was completed in less than 1 second as shown below.

The size of the database we’ve created has reached 150 GB. Now let’s take a backup of this database and delete it. When we restored this backup when Instant File Initialization was inactive, it took 12 minutes and 1 second.

After activating Instant File Initialization, the same process took 3 seconds as shown below.

Authorize SQL Server Service Account on Perform Volume Maintenance Tasks:

We are writing gpedit.msc as shown below on search.

From the Local Group Policy Editor, select User Rights Assingment like below. Then right click on Perform Volume Maintenance Tasks to select properties.

Click “add user or group” and add the sql server service account. This process is completed when you restart the SQL Server service account.

Security Risks for enabling Instant File Initialization:

When the data saved on this space allocated to sql server is deleted, it is returned to windows again without writing 0(zero). And if there is no encryption on the data, an authorized person on the server can read this field returned by SQL Server.