The ability to have SQL Server data files skip zero initialization when they are created or grown has been available since SQL Server 2005. By default, when you create a new data file in SQL Server, or extend the size of an existing one, zeroes are written to the file. Depending on the size of the file or its growth, and the type of storage, this can take a while. With Instant File Initialization (IFI), space is allocated for the data file but no zeroes are written. Prior to SQL Server 2016, to enable this feature you had to edit the Local Security Policy to give the account that runs the SQL Server service the “Perform volume maintenance tasks” right (from Start | Run, type secpol, within the Local Security Policy expand Local Policies, then User Rights Assignment). This was a task that DBAs had to perform separate from the SQL Server installation (or have a server admin do it for them), and if you did not make the change before installing SQL Server, then it required restarting SQL Server after making the change for it to take affect. This has changed with the SQL Server 2016 installation, as you can now select the option “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service” when you specify the service accounts, and this will grant the right to the service account at that time.
There is a potential security risk to using this feature, which Kimberly discusses in her Instant Initialization – What, Why and How? post. The information presented in her post is still valid and worth the read, but Glenn and I did re-run some tests recently, just to get some current numbers to show the benefits of IFI. We ran the same four tests that Kimberly ran way back in 2007 (!) on four different sets of storage: two sets of 15K disks (one in a RAID10 array, the other in a RAID1 array) and two sets of flash storage (FusionIO cards). More information on the storage at the end of the post. The tests were:
|1||Create 20GB database|
|2||Grow existing database by 10GB|
|3||Restore 30GB empty database|
|4||Restore 30GB database with 10GB data|
The tests were run on two different physical servers, both running SQL Server 2014. Details for each storage system are listed below for reference, and the test results were as we expected:
The time to zero out a file and write data is a function of sequential write performance on the drive(s) where the SQL Server data file(s) are located, when IFI is not enabled. When IFI is enabled, creating or growing a data file is so fast that the time is not of significant consequence. The time it takes to create or grow a value varies in seconds between 15K, SSD, flash, and magnetic storage when IFI is enabled. However, if you do not enable IFI, there can be drastic differences in create, grow, and restore times depending on storage.
- 15K RAID10 = Six (6) 300GB 15K disks in RAID 10
- Flash Drive1 = 640GB Fusion-io ioDrive Duo
- Flash Drive2 = 2.41TB Fusion-io ioDrive2 Duo
- 15K RAID1 = Two (2) 300GB Seagate Savvio 15K drives in RAID 1
Note: This post was edited on April 13, 2016, to clarify the storage configuration based on a helpful comment.