Instant File Initialization
Published: 16th May 2014
This is actually a Windows level setting, but something that is hugely beneficial to a SQL Server and should be enabled where possible.

It basically allows database data files to instantly allocate new space (autogrowth, creating new database, restoring a database, adding datafiles etc) rather than zero initializing them.

Basically when SQL Server grows or creates a data file it will request the new space from Windows and then go through the entire new allocation bit by bit making sure every bit is set to zero. This can be incredibly time consuming.

What you’re doing with this setting is allowing SQL Server to just accept the new space and immediately start using it. This can be a HUGE time saver.

Here’s a quick example to demonstrate the benefits, then I’ll go straight into how you can set this on your Windows server.

This script creates a new database with a 10GB data file:

use master
go

if exists (select name from sys.databases where name = 'autoInit')
  
drop database autoInit
go

create database autoInit
on primary
(
  
name = N'autoInit', filename = N'D:\SQLData\autoInit.mdf',
  
size = 10GB, maxsize = unlimited, filegrowth = 512MB
)
log on
(
  
name = N'autoInit_Log', filename = N'D:\SQLLogs\autoInit.ldf',
  
size = 100MB, maxsize = unlimited, filegrowth = 50MB
)
go


Now, running this on my test machine (which is all SSD) took just over 4 mins.

Running this with Instant File Initialization turned on reduced this to just 2 seconds!!!

So how do we go about checking if this is on or not before we start to play with Windows?

You need two trace flags, 3605 and 3004. If you turn these on, create a database, and then look in the SQL Server Error Log you’ll see information about SQL Server “Zeroing” the transaction log. Basically, if you see the same information about the data file then you do NOT have Instant File Initialization turned on.

Therefore run the following script:

use master
go

dbcc traceon (3605)
dbcc traceon (3004)
go

if exists (select name from sys.databases where name = 'autoInit')
  
drop database autoInit
go

create database autoInit
on primary
(
  
name = N'autoInit', filename = N'D:\SQLData\autoInit.mdf',
  
size = 10GB, maxsize = unlimited, filegrowth = 512MB
)
log on
(
  
name = N'autoInit_Log', filename = N'D:\SQLLogs\autoInit.ldf',
  
size = 100MB, maxsize = unlimited, filegrowth = 50MB
)
go

exec xp_readErrorLog
go


In my case the output included the following:

As you can see, there is no mention of the data file and therefore I have it turned on.

So, let’s presume it’s not turned on… how do we set this?

Well, we go to the Windows server, then select Administrator Tools -> Local Security Policy:

Once in here, head to Local Policies -> User Rights Assignment and you will see “Perform Volume Maintenance Tasks”

You can then add your SQL Server Service account into this policy. You do need a single reboot of the Windows Server in order to set this change, but then you’re good to go.

Hope this helps.
Comments:
NB: Comments will only appear once they have been moderated.