SIZE

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.

Running some tests on a local virtual machine running SQL Server 2012 (similar results tested on SQL Server 2014 and SQL Server 2016), here is what I found.

• Creating a new database (10GB file size) took 2 minutes and 6 seconds, with IFI enabled, it took just 4 seconds.
• Autogrow of a data file witha 1GB growth size took 13 seconds without IFI, with IFI it took less than a second.

For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled.Read More »Instant File Initialization (IFI)