Instant File Initialization (IFI) is a feature which has been available to us since Windows Server 2003 and SQL Server 2005 onwards, but somehow, it is still underutilized because many of us are not aware of it. In this article, we are going discuss what Instant File Initialization is and how it can help us in improving the performance of various file operations frequently performed by SQL Server.

A few such operations are given below:

• Restoring a database backup
• Creating or adding a database file
• Increasing the size of an existing database file (either manually or by AutoGrowth)

What is Instant File Initialization

To understand the Instant File Initialization, first we need to understand what File Initialization means. Whenever a file is created or its size is increased, some new storage space will be allocated to the file. Before this storage space is allocated to the file, this space will be first filled by zeros. This is so that any old data that earlier existed in this space cannot be read by the current application/user.

This process of filling out the file with zeros is knows as zeroing out the file or File Initialization. This File Initialization process consumes some time and makes thing a bit slow whenever SQL Server performs any file operation listed above.

Fortunately, there is a way to reduce the amount of time taken by this File Initialization process.  Yes, we are talking about Instant File Initialization. When Instant File Initialization is enabled, this step of zeroing out the new storage space is skipped. It means that the new space is immediately allocated to the file without zeroing it out and is overwritten as the new data comes in.

Demo

Let’s examine the effects of IFI with a demo. First we create a database with a data file of 10 GB size when IFI is not enabled and check how much time it takes.

IF DB_ID('IFITest') IS NOT NULL
BEGIN
      DROP DATABASE IFITest
END
GO
DECLARE @ScriptExecutionStartTime DATETIME
DECLARE @ScriptExecutionEndTime DATETIME
DECLARE @TotalElapsedTime INT
--============================= Recording script execution start time
SELECT @ScriptExecutionStartTime = GETDATE()
PRINT ('/*'+REPLICATE('=',200))
PRINT 'Script execution started (With Instant Initiallization OFF) on SQL Server instance ['+@@SERVERNAME+'] at: [' + CONVERT (VARCHAR(30),@ScriptExecutionStartTime,120)+']'
PRINT (REPLICATE('=',200)+'*/')
--============================= Creating a database of 10 GB Size with Instant Intiallization ON.
CREATE DATABASE IFITest
ON
      (NAME='IFITest', FILENAME='D:\SQLTestLab\DatabaseFiles\DataFiles\IFITest.mdf', SIZE=10GB, FILEGROWTH=10MB, MAXSIZE=50GB)
LOG ON
      (NAME='IFITestLog', FILENAME='D:\SQLTestLab\DatabaseFiles\LogFiles\IFITest_Log.ldf', SIZE=1GB, FILEGROWTH=1MB, MAXSIZE=10GB)
--============================= Recording script execution end time
SELECT @ScriptExecutionEndTime = GETDATE()
SELECT @TotalElapsedTime = DATEDIFF(ss,@ScriptExecutionStartTime,@ScriptExecutionEndTime)
PRINT ('/*'+REPLICATE('=',200))
PRINT 'Script execution completed at: [' + CONVERT (VARCHAR(30),@ScriptExecutionEndTime,120)+']'
PRINT 'Total elapsed time in script execution: '+ CAST ((@TotalElapsedTime/60) AS NVARCHAR(30)) +' Minute(s) ' + CAST ((@TotalElapsedTime%60) AS NVARCHAR(30)) +' Seconds(s).'
PRINT (REPLICATE('=',200)+'*/')

This script will create a database having a data & log file of size 10 GB & 1 GB, respectively. Here is the output:

As we can see, it took 2 minutes and 13 seconds to create the database with 10 GB data file.

Now, let’s check the performance of the same operation after enabling IFI.

Enabling Instant File Initialization

Enabling Instant File Initialization is a quite simple. In Windows Server 2003 & above we have a security policy known as “Perform Volume Maintenance Tasks”. To enable the Instant File Initialization in our environment, we need to give permission to our SQL Server Service Account to this security policy.

I should mention here, that all the users who are a member of “Administrators” group already have this permission. Therefore, if SQL Server Service Account is already a member of the “Administrators” group (which is not recommended by the way) then you don’t need to do anything further; your SQL Server is already taking advantage of IFI.

Here’s how IFI is enabled. Go to Control Panel -> Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Assignment -> Perform Volume Maintenance Tasks. Shown below in this image.

Double click on this policy. You should see the Properties dialog box as shown below:

Click on “Add User or Group” and add the SQL Server Service Account here. Click “Apply”.

Note:

You need to re-start the SQL Server Service after this for the changes to take effect. Once the SQL Server Service is re-started after providing the required permissions, we can test further.

Let’s run the same query again that we executed earlier. This time SQL Server should take the advantage of IFI.

This time it took only 13 seconds to create the same database with same size. Enabling IFI reduced the processing time by 90% in this case. In general the amount of time IFI will reduce mostly depends on the size of the data file it is working upon.

General Considerations

Instant File Initialization or IFI works for Data files only. It does not reduce the processing time for Log files. Log files are always zero initialized.

IFI will not work when TDE (Transparent Data Encryption) is enabled.

Security Considerations

Always use this feature with caution as it comes with a security caveat. We know that after IFI is enabled, new or extra storage space that is added\allocated to the data file is not zeroed out. The space is just allocated to SQL Server data file as it is, and it is overwritten afterwards as new data comes in. This means the data that existed on this storage space earlier can still be read by someone with proper tools, in case it is not yet overwritten. It might look a minor security threat, however if your company doesn’t allow for this you shouldn’t enable this feature.

So, that's all for now, friends. Thanks a lot for your time 🙂