SQL Server and Instant File Initialization

Performance tuning and disaster recovery have always fascinated me in regards to SQL Server.  Whether it be an index tweak, RAID subsystem configuration or a proper disaster recovery plan.  Today, I want to briefly talk about a feature called Instant File Initialization, added to SQL Server 2005 and on.  A lot of people have never even heard of it.  What is it you ask?  Well let me tell you.

Instant file initialization is based on an NTFS feature that was added to Windows Server 2003 and on.  Basically, and I am being  very basic on the description as this post will not be very long, when a SQL database file is created in on the OS, it is prefilled with zeros.  So, if a new database is created, a database is grown, or a database is restored, the first thing that happens is that the file is created, or extended.  The very next thing that happens is the OS will prefill the pages in file with zeros (SQL Server Databases are created using 8KB pages).  The process of prefilling the pages with zeros can be very time consuming and I/O intensive.  With Instant File Initialization, this process is skipped, the file is made available once the file is created, and SQL Server can start populating it with data.

To show an example of this, I am going to use a test database that I created called MyDB.  This database is roughly 5GB in size.  I will first restore a backup without using Instant File Initialization.

Notice that the time it took to restore is 11:49 minutes?  Now, I'm going to allow SQL Server to utilize Instant File Initialization.  To do this, you have to go to the local security policy editor under Administrative tools, and make a change to the "Perform Volume Maintenance Tasks".  The change that needs to be made is, you have to add the user or group of the service account that starts the SQL Server service.  This is done in the Security Settings->Local Policies->User Rights Assignment->Perform Volume Maintenance Tasks in the Local Policy Editor.  

*FYI, best practices for SQL Server is not to start the SQL Server service using an account that is part of the administrators group.  Normally, I create a group, and add a user to that group.  I will add that group to the "Perform Volume Maintenance Tasks" in the local policy.  Then we have to restart the SQL Server service.

Now, after making the appropriate changes, and restarting the SQL Server service, I start the restore.

Look at that!  It finished in 6:37 minutes!  That is 5:12 minutes faster for a restore, or 44% faster.  This not only works for restores, but with file growth and creating new files as well.  It does not work for the Log file though.  That still has to be prefilled with zeros due to the circular nature.

*There is a slight security risk with using this option.  Windows Administrators have access to all files stored on the server.  By giving the SQL group the "Perform Volume Maintenance Tasks" permission, the SQL Administrator can then use an undocumented command to view individual pages on the hard drive even if they are encrypted.  This command is "DBCC PAGE".

The options for DBCC PAGE are as follows:

0 - print just the page header 

1 - page header plus per-row hex dumps and a dump of the page slot array

2 - page header plus whole page hex dump 

3 - page header plus detailed per-row interpretation

One other thing about Instant File Initialization.  With Windows operating systems, when a file is deleted, it is not really deleted.  The disk space is just marked as usable.  So, when you create a new file or grow a file, it is actually potentially overwriting deleted files.  So, if you like to recover deleted files, don't use this option.