Improve SQL Server Performance By Enabling Instant File Initialization

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

• Microsoft - Database Instant File Initialization
• An Overview of Instant File Initialization in SQL Server, Rajendra Gupta, SQLShack
• SQL Server Setup – Instant File Initialization (IFI) James Rhoat, SQLShack