Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Instant File Initialization

By Sujeet Singh,

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 :-)

Total article views: 3846 | Views in the last 30 days: 126
 
Related Articles
ARTICLE

Instant File Initialization and restores

Instant file initialization also has its advantages when you restore a SQLServer database. This arti...

ARTICLE

SQL Server Instant File Initialization (PQW)

In this article we’re going to look at instant file initialization. What it is, why it’s cool and ho...

BLOG

Turn on Instant File Initialization for SQL Server Performance

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is...

BLOG

Turn on Instant File Initialization for SQL Server Performance – With Video Demo

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is...

BLOG

Enable Instant File Initialization to accelerate database restore

Today my colleague come to me and ask me why her database restore query was hang. She was going to.....

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones