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:
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:
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:
- Expand the Local Policies Folder
- Click on User Rights Assignment
- Go down to the “Perform Volume Maintenance Tasks” option and double click it
- Add your SQL Server Service account, and click OK out of the dialog.
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.
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.
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]