Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Effect of Instant File Initialization within SQL Server


By:   |   Last Updated: 2012-08-17   |   Comments (6)   |   Related Tips: More > Database Administration

Problem

It is a best practice to pre-allocate databases and not to rely on autogrow to expand your database. Often times during a server migration, or setting up a new SQL instance, or during disaster recovery where downtime needs to be minimized you need to create a large database or you may want to just pre-allocate an exiting database file. I used to run into this scenario at my last company where it would take 30-45 minutes to create a large database.

Solution

When enabled, Instant File Initialization can create almost any sized data file instantaneously since it bypasses the zeroing out of the underlying bits on the file system. This privilege is set in the Local Security Policy and Administrators get this right by default, so if your service account happens to be an Admin (shame on you) then it already has this privilege set.

What Operations Are Affected Inside SQL Server

  • Database Creation
  • Add data file to existing database
  • Growing a data file
  • Restores
  • Backup Operations

Test Case 1 - Creating a Database

As a test I created a 50GB database before and after Instant File Initialization was enabled. My testing was performed using SQL Server 2012 CU1 on Windows Server 2008 R2. Also since this was a testing environment I was utilizing local disk.

Before Instant File Initialization was enabled it took 2 min to create this database (Note: This was local disk and would have taken longer on a SAN or external array).

Create Before INI

After setting Instant File Initialization this 50GB database was created in sub second time.

Create After INI

Test Case 2 - Growing a Database File

As a second test case I created a very small database and then grew the data file to 50GB simulating pre-allocation of an exiting 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 50GB on my test system took around 2 min as shown below.

Alter Before INI

After setting Instant File Initialization this data file grew to 50GB in sub second time.

Alter After INI

Do I have Instant File Initialization Enabled?

The one way to tell without opening the Local Security Policy is to perform 2 quick tests:

  • Create a test database with a 10GB data file and a 1MB log file and note the duration
  • Create a test database with a 1MB data file and a 10GB log file and note the duration

Since Instant File Initialization applies to data files only, if these times are close to the same, odds are you do not have Instant File Initialization configured.

How To Enable Instant File Initialization

1. Open Local Security Policy and go to Local Policies --> User Rights Assignment.

Local Security Policy

2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.

Perform Volume Maintenance Tasks

3. Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.
Next Steps
  • Check your environment to see if you have Instant File Initialization enabled
  • Set your service account to use the "Perform Volume Maintenance Tasks" policy
  • Test the duration for creating databases and growing log files


Last Updated: 2012-08-17


next webcast button


next tip button



About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 12, 2013 - 10:11:16 AM - Pavel Pawlowski Back To Top

@daDbDeveloper, in the article it is mentioned correctly.

Related to the 10 GB DB. Sure, if the IFI is off, it will take longer to initialize the database file, however in the sample is 10 GB Database and 1  MB Log file. The second scenario I 1 MB Database and 10 GB Log File. Since Log files is always zeroed and IFI is not applied on the log, then the times for both scenarios should be close in case IFI is disabled.


Monday, September 23, 2013 - 11:18:23 AM - daDbDeveloper Back To Top

I would take exception to your comment, regarding testing if Instant File Initiation is set: Since Instant File Initialization applies to data files only, if these times are close to the same, odds are you do not have Instant File Initialization configured.

The opposite should be true, shouldn't it? if IFI is off, it should take longer to initialize the DB with 10GB data file.


Monday, August 20, 2012 - 6:44:01 PM - Chad Churchwell Back To Top

sqlfriend, after re-reading your comment I see what you are asking, since it is named "local" would it still apply to SAN and the answer is yes it would after the drives have been presented through disk management


Monday, August 20, 2012 - 4:15:55 PM - Chad Churchwell Back To Top

Yes to my knowledge this has the same effect on SAN drives a slocal drives.  There is not a setting called local security policy.  local security policy is a group of policies and the policy this is referring to is called "Perform Volume Maintenance Tasks"


Monday, August 20, 2012 - 12:41:59 PM - sqlfriend Back To Top

This setting is called local security policy. All our database files are on SAN drives.

So does this local setting have the same effect on SAN drives?

 

Thanks


Monday, August 20, 2012 - 4:31:27 AM - Tony Back To Top

It's implied in your article but it's probably worth mentioning explicitly that, if for some reason your database is not fixed in size, as per best practice, autogrowth times of the data files (as per your example of manually adding a data file) are also improved by this setting.

Tony


Learn more about SQL Server tools