SQL server by default will zero initialize files before using them or re-using for data storage. Imagine we had one byte of storage:

1101 1001

To zero initialize means to overwrite the existing data so it becomes

0000 0000

The process of changing 1s to 0s can be time consuming, especially when requesting large chunks of storage e.g. creating a database or database file growths. We can skip this process by enabling the instant file initialization feature.

Starting with SQL server 2016 the feature can be enabled as part of the setup.

Setup

If you have an older version of SQL or didn't check the box in 2016 you can still enable IFI by granting perform volume maintenance tasks to the SQL server service account. There's a couple of ways to go about this.

  1. Run the following code in CMD session, pass the SQL Server service account to the -u parameter.
Ntrights.exe -u "NT Service\MSSQL$INSTANCE" +r SeManageVolumePrivilege
  1. Grant the permission throught the local security policy editor

localpolicy

If enabling IFI post installation then a server restart is required before permissions kick in.

Now lets run a quick test. We will create a 100 GB database both with and without IFI.

First let's check if we have IFI enabled using the whoami /PRIV command. I'm running this through Management Studio as it will return the rights of the SQL service account. If you run this through CMD or Powershell you will need to impersonate the service account.

 sp_configure 'show advanced',1
 GO
 Reconfigure
 GO
 sp_configure 'xp_cmdshell',1
 GO
 Reconfigure
 GO
 xp_cmdshell 'whoami /priv'
 GO
 sp_configure 'xp_cmdshell',0
 GO
 Reconfigure
 GO
 sp_configure 'show advanced',0

The perform volume maintenance task is not present.

results

Here's the create database script.

CREATE DATABASE [IFItest]
 ON  PRIMARY 
( NAME = N'IFItest', FILENAME = N'D:\SQLData\IFItest.mdf' , SIZE = 102400000KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'IFItest_log', FILENAME = N'D:\SQLData\IFItest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

While that's running we can go check wait stats. The interesting stat to focus on is PREEMPTIVE_OS_WRITEFILEGATHER. On systems without IFI enabled you may see high times associated with this wait type.

waitingtask

After 20 minutes the script finally finished.

Now let's enable IFI, bounce the server, run whoami, drop the DB and re-run the script.

Verified perform volume maintenance task is enabled
wegotit

The 100 GB now finishes in 10 seconds.

faster

It's hard to refuse these types of gains however there are a few things about IFI to be aware of. First IFI poses a slight security risk, hence why it probably not enabled by default. Since the previous data is not overwritten by a zero initialize it's possible for unauthorized access. For example, Tom has sensitive database. Later he deletes the file and the O.S. marks that space available for use. Steve creates a database and the files are allocated in the same area as Tom's DB. Technically Steve might be able to read some of the data from Tom's DB now. Second IFI does not impact the zero initialize process for log files. Logs must always be zero initialized.

If the security risk is acceptable then enabling IFI is a no brainier.