ENABLING INSTANT FILE INITIALIZATION

When SQL Server reserves new space on a disk, it initializes the space with zeros.

This process reduces the execution time of the operations and the load on the disk subsystems.

Disk space reservation without initialization is called Instant File Initialization.

When instant file initialization is not enabled, SQL server will write binary zeroes to across all data pages that are being allocated to the database.

If instant file initialization is not turned, it takes SQL Server longer to create or expand a database.

ENABLING THE INSTANT FILE INITIALIZATION:

STEP 1: In this step open Local Security Policy window in that expand local policy and select User Rights Assignment and select Perform volume maintenance tasks.

STEP 2: In this step On the Local Security Setting tab, click Add User or Group button.

After we click then below dialog box appears. Here add the account name for which the SQL Server is running and then click ok.

STEP 3: In this Step user name will be added to the Perform Volume Task Maintenance as shown below, and then apply and click ok.

STEP 4: Now Instant File Initialization is successfully enabled.

NOTE: You also needed to restart the SQL Server service in order for SQL Server to start using Instant File Initialization.

Installation of SQL Server 2016 with Instant File Initialization enabled option:

From 2016 and later versions of SQL Server we can configure Instant File Initialization by clicking the option Grant Perform Volume Maintenance Task Privilege to SQL Server Database Engine Service on the tab Server configuration during installation as shown below.

Advantages of Instant File Initialization:

1. Time and delays will be reduced to expand data files and to restoring from a backup.

2. Accelerates the creation of new database.

3. Reducing the start time of SQL Server due to faster tempdb initialization.

4. The larger the database size growth operation, the more obvious the performance improvement can be seen with Instant File Initialization.

TEST CASE FOR INSTANT FILE INITIALIZATION:

As test case I created a very small database and then grew the data file to 10GB simulating pre-allocation of an existing database which aligns with best practices for database sizing if the size is not known at database creation time.

Before having Instant File Initialization set up, growing a data file to 10GB on my test system took around 2 minutes 15 seconds as shown below.

After enabling Instant File Initialization this data file grew to 10GB in sub second time.

To Check Instant File Initialization Is Enabled OR Not:

Now we create a database with 5GB data file and 5MB log file and note the execution time and again create a database with vice-versa and note the execution time.

Here for the creation of database 5GB data file and 5MB log file, it took 52 seconds to execute as shown above.

Here for the creation of database 5MB data file and 5GB log file, it took 55 seconds to execute as shown above.

If these two execution times are close together, then the Instant File Initialization is not configured.