SQL Server Health Check

Improve SQL Server Performance By Enabling Instant File Initialization

Mark Varnas
No comments

What is instant file initialization (IFI)?

Instant file initialization is a Windows feature that enables your  SQL Server to skip the zero-writing step and begin using the allocated space immediately for data files.

IFI allows SQL Server to reduce database creation required time (including tempdb at server startup), data file growth, and minimize database restoration, especially for large databases.

When using instant file initialization, the deleted content might be accessed by an unauthorized principal until some other data writes on that specific area of the data file, because the deleted disk content is overwritten only as new data is written to the files.

If SQL Server is in a secure physical environment, the performance benefits can outweigh the security risk and hence the reason for this recommendation.

Notes:

  • Log files do not benefit
  • If TDE (Transparent Data Encryption) is enabled, the database cannot benefit from instant file initialization
  • Instant file initialization is available only on Microsoft Windows XP Professional or Windows Server 2003 or later versions

How to check if instant file initialization is enabled?

One way to find out the status of the instant file initialization is to check the error logs. You can run the following script:

EXEC xp_readerrorlog 0
	,1
	,N'Database Instant File Initialization'

You can also use the DMV sys.dm_server_services using the script below:

SELECT ServiceName
	,status_desc
	,instant_file_initialization_enabled
FROM sys.dm_server_services

How can you enable it?

First, open SQL Server Configuration Manager to see the account name of the SQL Server instance:

Now, you can configure it using the Group Policy Editor:

  1. Run gpedit.msc or secpol.msc
  2. Go to Computer configuration
  3. Select Windows Settings
  4. Go to Security Settings
  5. Select Local Policies
  6. Go to the User Rights Assignment
  7. Go to the Perform Volume Maintenance Tasks option
  8. Add your SQL Server Service account, and click OK
  9. Restart your SQL Server services

More information

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials