Instant File Initialization (IFI)
One way to improve performance on SQL Server is with IFI or Instant File Initialization.
IFI stands for “Instant File Initialization”. It is a feature that allows SQL Server to perform faster initializations of data files during the creation or expansion of a database.
Without Instant File Initialization
Without IFI, SQL Server needs to initialize the entire file with zeros before using it, which can take a significant amount of time for large data files. With IFI enabled, SQL Server can skip the zero-initialization step and immediately begin using the newly created or expanded file.
IFI is especially beneficial for creating or expanding large data files. By reducing the time it takes to initialize these files, it can improve overall database performance and reduce the impact of long-running operations.
However, it is important to note that IFI requires certain permissions to be granted to the SQL Server service account. In particular, the service account must be granted the “Perform volume maintenance tasks” privilege, which can be done through the Local Security Policy tool on the server. Additionally, IFI only applies to data files, not log files, and it only affects the initialization process, not subsequent file operations.
How Instant File Initialization works
Normally files are zeroed out on a database growth during an AUTOGROW, RESTORE, CREATE DATABASE or ALTER DATABASE. This is done by SQL Server when the file grows, it runs through that file and writes zeroes to the entire new allocation in the file. The zeroing process can take a great deal of time, the Instant file initialization process skips this zeroing, and just allocates the file. This works since SQL Server will just write each 8k page to disk as they are used, thus overwriting the uninitialized file.
For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled.
Caveats
- IFI Only works for data files, not log files.
- IFI will not work if TDE (Transparent Data Encryption) is being used.
- You can use DBCC Page to view the junk in the file when it is not zero initialized.
IFI was Originally introduced in SQL Server 2005, and works on all version and editions of SQL Server since 2005, excluding Azure sql databases.
To Enable IFI….
- Open Local Security Policy and go to Local Policies –> User Rights Assignment.
- Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
- Restart the SQL Server service.
- IFI will now be enabled.
- Test it…
