If you manage your SQL Server with a “keep it tight” approach, you may be losing some performance when SQL Server needs to allocate space to expand your data files.

Instant File Initialization was added several SQL Server releases ago.  The “Perform Volume Maintenance Tasks” policy is off by default preventing many SQL Server installations from taking advantage of the feature.

How it works:

When SQL Server needs to allocate space for certain operations, it will first fill the space it needs with zeros. Here is a little test:

 IFI before

It took SQL Server almost 10 seconds to expand the data file by 1 GB. The down side is that not zeroing out the newly allocated files will leave the possibility open that some deleted files on the server may still exist in that space and be somehow accessible. The deleted files could be accessed through the backup file or if the database is detached.

Here is the same test with “Instant File Initialization” turned on:

IFI after

WOW! The difference is major.  Instant file initialization (IFI) allows SQL Server to skip the zero-writing step and begin using the allocated space immediately for data files.

When Is IFI Used?

If instant file initialization is turned on, it is used in these cases:

  • When a database is first created;
  • When a databse is manually increased;
  • When tempdb is recreated each time SQL Server is restarted;
  • When a data file is full and autogrowth kicks in;
  • When backups are restored.  SQL Server must be pre-allocated the space before a restore can occur.

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files must be zeroed out, as random data in transaction log pages can be problematic. For this reason, it is a good practice to pre-allocate your transaction log files to minimize the autogrowth events.

How to turn on IFI:

Before SQL Server 2016, we could only turn this feature on by adding the service account to the “Perform Volume Maintenance Tasks” policy in the “User Rights Assignment”

You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows:

  1. Expand the Local Policies Folder
  2. Click on User Rights Assignment
  3. Go down to the “Perform Volume Maintenance Tasks” option and double click it
  4. Add your SQL Server Service account, and click OK out of the dialog.

ifi policy

After granting this policy to the service account, you’ll need to restart the SQL Server service for the policy to take effect and for SQL Server to start using IFI.

Instant file initialization will be on for every instance on the entire server. You can use trace flag 1806 to turn it off for a particular instance.

IFI traceflag

 

New in SQL Server 2016:

SQL Server 2016 Setup provides the option to enable "Perform Volume Maintenance Task" privilege to the SQL Server service account. 

For more information, see Database Instant File Initialization topic

SQL2016 IFI Setup

Here is the code used in the demo:

DECLARE @Start datetime = GetDate()
ALTER DATABASE [test_IFI] MODIFY FILE ( NAME = N'test_IFI', SIZE = 1048576KB )
SELECT datediff(ms, @Start, GetDate()) as [Elapsed Time]

 

Eric Vaillancourt
Author: Eric Vaillancourt
Eric Vaillancourt is an SQL enthusiast. He started his career as a database programmer in 1989. He has been teaching SQL at Technologia since 2002 and has trained more than 5,600 professionals. He leads more than fifteen training sessions focused on Microsoft products. Specializing in high availability environments supporting a large number of transactions per second.


Comments (0)

There are no comments posted here yet

Leave your comments

Posting comment as a guest. Sign up or login to your account.
Attachments (0 / 3)
Share Your Location