Instant File Initialization - Oh you didn't know?
One feature that (in my opinion) hasn't gotten the attention that it deserves is Instant File Initialization. As documented in the Siemens Teamcenter and SQL Server Best Practices whitepaper is it recommended to have this options enabled but you might be asking yourself what this is and why you should do it.
With Instant File Initialization turned on SQL Server can initialize database files instantly, without physically zeroing out each and every 8K page in the file. The space is just allocated to SQL Server by the Operating System thus saving your a great deal of time (depending on the size of files you are working with).
So what operations can take advantage of this feature:
- Creating a new database
- Increasing the size of data file (manual and autogrowth)
- Creating tempdb when SQL Server gets restarted
- Restoring a database or filegroup
NOTE: Transaction Log files cannot make use of Instant File Initialization
Just how much time can be saved? I tested creating 3 different new databases
Database Drive Configuration: 3 X 7200 RPM disks configured (RAID 5)
| Database Size | BEFORE Instant File Initialization is Enabled | AFTER Instant File Initialization is Enabled |
| 1 GB | 12 seconds | 0.4 seconds |
| 10 GB | 2 minutes 19 seconds | 1.8 seconds |
| 50 GB | 12 minutes 4 seconds | 4.9 seconds |
How to Enable It:
- Start the local security policy tool (as an administrator)
- Expand Local Policies
- Expand User Rights Assignments
- Find the "Perform Volume Maintenance" Policy
- Right click on "Perform Volume Maintenance" and select Properties
- Add the SQL Server service account
- Restart the SQL Server Service
Requirements:
- Windows Server 2003, 2008, or 2008 R2
- SQL Server 2005, 2008, or 2008 R2 (any edition)
Small Print:
From MSDN on Database File Initialization: Because the deleted disk content is overwritten only as new data is written to the files, the deleted content might be accessed by an unauthorized principal. While the database file is attached to the instance of SQL Server, this information disclosure threat is reduced by the discretionary access control list (DACL) on the file. This DACL allows file access only to the SQL Server service account and the local administrator. However, when the file is detached, it may be accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME. A similar threat exists when the database is backed up. The deleted content can become available to an unauthorized user or service if the backup file is not protected with an appropriate DACL.
What does that mean?
Because Instant File Initialization doesn't zero out the data when allocating it to SQL Server, if the disk being allocated previously belonged to a now deleted database (with the right tools) the data could potentially be accessed. This is a VERY low risk and almost a non-issue but it important to call it out as something to just be aware of.
Please review the Siemens Teamcenter and SQL Server Best Practices whitepaper for more information on using Instant File Initialization with Microsoft SQL Server.