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.
Storage Details:
- 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.
9 thoughts on “Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers)”
I think this is a fantastic enhancement. While after installing earlier versions of SQL Server you always had the ability to go turn on IFI before creating user databases, it was a pain, and easy to forget about. This checkbox is much easier than fumbling around in gpedit, and allows you to create massive tempdb files during installation without paying for the wait (or without having to accept the tiny defaults only to have to remember to do one other thing post-install).
Excellent point about the added benefit of tempdb file creation!
This permission keeps SQL Server from zeroing out new space when you create or expand a data file
I’m obviously being thick headed today, but what’s the difference in the 4 storage types? Hard drives @ 15K rpm are magnetic and SSDs are a type of flash.
You’re not being thick headed! Glenn and I each ran two tests and then I pooled the results. He says “flash” whereas I say “SSD”. I say 15K disks where he says “magnetic.” The differences are really in the version of the FusionIO cards, and the RAID array for the 15K disks. I’ll update the post to reflect that info. Thanks for the catch!
Someone needs to post about 2016 changes to DBCC CHECKDB – you know, the multi object scanner latch contention (or the lack of)… that got me excited alright.
Have you seen this post on the PSS blog? Not sure what other information you’re looking for…
https://blogs.msdn.microsoft.com/psssql/2016/02/25/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/