One way to improve performance on SQL Server is with IFI or Instant File Initialization.
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.
- 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…