SQL Server 2016 loaded with enhancements and features. Recently, they added the ability to enable the Instant File Initialization (IFI) during the installation of SQL Server 2016 (CTP 3.0).

Installation of SQL Server 2016 with Instant File Initialization enabled option

From SQL Server 2016 (CTP 3.0) on, you can enable IFI by just clicking the option “Grant Perform Volume Maintenance Task Privilege to SQL Server Database Engine Service” on the tab “Server configuration” during installation. You can see this in the following image:

Background of Instant File Initialization

Whenever SQL Server needs to allocate disk space for operations like creating/restoring a database or growing data/log files, it has to first zero out the newly allocated space. Fundamentally, the newly allocated disk pages, which have been allocated for the operations, will be filled with zeroes. In most cases, zeroing out newly allocated disk space may not be useful.

By enabling Instant File Initialization, SQL Server skips the zero-writing step for the data file. The transaction log file still needs to be zeroed out because of data integrity and security reasons.

Prior to SQL Server 2016 (CTP 3.0), if you want to enable this feature, you have to follow below mentioned steps;

• Open the Local Security Policy application.
• Expand Local Policies, click on User Rights Assignment.
• Now double click on “Perform Volume Maintenance Tasks”.
• Go to Add User or Group and add Service Account.
• Click on Apply

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

Benefits of enabling Instant File Initialization

The larger the database size growth operation, the more obvious the performance improvement can be seen with Instant File Initialization. SQLSkills team has done a great performance testing on Zero Initialization VS Instant Initialization where you can clearly see that ALTER DATABASE by 10 GB and Restore of 30 GB database can take minutes to initialize if you have not enabled the IFI.

PS: Keep in mind that there is a slight security risk when you enable IFI, but it’s up to the company to determine if the benefits overshadow the risks.