How to Turn on Instant File Initialization

Do you have instant file initialization turned on?  If you do, then allocating your database data pages will go much faster.  Not having instant file initialization turned on slows down the process of adding pages to your database.  It slows down because prior to allocating the data pages to your database, SQL Server needs to zero out the newly allocated pages.

What is Instant File Initialization

Instant file initialization means SQL Server does not initialize your data pages with binary zeroes.    The default configuration for instant file initialization is to not perform instant file initialization.  When instant file initialization is not enabled, SQL server will write binary zeroes to across all data pages that are being allocated to the database.  This means every time SQL Server needs to create a database, or grow a data file the new pages allocated to the database first need to be filled with binary zeroes.  The process of writing binary zeroes takes time.  Therefore, when instant file initialization is not turned it takes SQL Server longer to create or expand a database.  The more space added to a database the longer it takes.

Why does SQL Server Not Perform Instant File Initialization by Default?

By writing binary zeroes to newly allocated disk space SQL Server makes sure pages allocated to the database don’t contain old disk images.  Without overwriting those data pages, a person might be able to peek into the internal structure of a data page and see those bits and bytes of old data stored on the disk.  By not zeroing out those data pages there is a security risk that old data can be viewed by outputting raw database pages.

How to Turn on Instant File Initialization

In order to turn on instant file initialization you need use the following steps:

1. Logon to your SQL Server with an account that is a member of the local Windows Administrator group. 
2. Open the Local Security Policy application by running secpol.msc from a command prompt.
3. In the left pane, click the “Local Policies”:
   
4. In the left pane double-click on the “User Rights Assignment”
   
5. Double-click on “Perform volume maintenance” item.
   
6. Click the “Add User or Group” button
   
7. Add the account name for which the SQL Server is running under in the dialog below:
   
8. Click the “Apply” button, and then close the “Local Security Policy” dialog box.

Effects of Turning on Instant File Initialization

It takes time to write binary zeroes to newly allocated database pages, which means users may experience delays while the data pages are being allocated to your database.  By turning on instant file initialization SQL Server doesn’t have to write binary zeroes to the newly allocated data pages, thus eliminating the wait time to write binary zeroes.   

See all articles by Greg Larsen