Quest for SQL Server: Effect of Instant File Initialization within SQL Server

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

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

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.

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

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.