SQL SERVER – Database Instant File Initialization

Instant file initialization is a setting that can make a positive impact at instance level on SQL Server I/O performance.

All I/O operations are done via the OS and the storage layer. For example:

• Claiming disk space for creating a new database file
• Autogrowing a database file
• Restoring a database from backup

Whenever SQL Server needs to allocate space for these operations, it will first fill the space it needs with zeros. This zeroing phase, or zero initialization, can take a significant amount of time, depending on storage performance and size of the disk space to zeroed. Therefore, it can adversely impact file creation, autogrow, and database restore performance.

How to Turn-On Instant File Initialization

SQL Server doesn’t have a setting or checkbox to enable Instant File Initialization.

Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. 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.

After granting this policy to the service account, you’ll need to restart the SQL Server service in order for the policy to take effect and for SQL Server to start using Initial File Initialization.

Cheers!