SQL Server Health Check

Improve SQL Server Performance by Enabling Instant File Initialization

Updated November 11, 20263 min read

Instant File Initialization, explained. A pop-up tent springs open by itself next to a bundle of unused tent poles.

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 the time required for database creation (including tempdb at server startup), data file growth, and 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:

  • Transaction log files did not benefit before SQL Server 2022. In SQL Server 2022 and later, log autogrowth events of up to 64 MB can benefit, but larger log growths cannot
  • If TDE (Transparent Data Encryption) is enabled, data files 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:

T-SQL
EXEC xp_readerrorlog 0
	,1
	,N'Database Instant File Initialization'

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

T-SQL
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:

Figure 1 - SQL Server configuration manager - service
Figure 1 - SQL Server configuration manager - service

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
Figure 2 - Group policy editor
Figure 2 - Group policy editor

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜