Improve SQL Server performance enabling Instant File Initialization

Category: Performance
Item: Instant File Initialization access right

What’s 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:

Figure 1 – Service account, SQL Server Configuration manager.

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.

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.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.