When you create a new database, restore from backup or when a database has to grow, it zeroes out the content on the disk before writing on to the disk. This can affect the performance and may lead to query time outs.
The reason it zeroes out the content is because of potential security concern. With IFI enabled, we can let SQL server start writing out to the disk without zeroing out the disk content. This applies only for data files. Log files are always zeroed initialized.
To enable Instant File Initialization, SQL Server Service account should have permissions to do perform volume maintenance tasks.
To do this—> go to Local Security Policy–>Local Policies–>User Rights Assignment–>Perform Volume Maintenance Tasks and right click on it and add the sql server service account to it.
If the sql server is running as a member of the administrator group, this option is enabled by default. If the sql server is running, when you enable IFI, it has to be restarted to take into effect. Also, In a clustered environment all the nodes should have this feature enabled. To check this feature, try the sample script below after and below assigning the permissions.(If the sql server service is running with admin rights, it is already enabled).
DBCC TRACEON(3004,3605,-1)
–>Create a Sample database
–>Check the sql server error log. If IFI is not enabled, you will see zeroing out the mdf and you will not see that after it is enabled.
DBCC TRACEOFF(3004,3605,-1)
Some of the reasons why IFI does not work:
1.Database has TDE enabled and
2.Trace Flag 1806 is turned on.
Reference : http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
SQL Server Repository 