Instant File Initialization : Impact During Setup

Recently, Erin Stellato (@erinstellato) blogged about the performance impact Instant File Initialization (IFI) can have when creating or restoring databases. She explains that SQL Server 2016 setup now offers you the ability to grant the appropriate rights to the SQL Server service during installation (we also talked about this in the CTP 3.0 section of Latest Builds of SQL Server 2016):

Now you can enable Instant File Initialization during SQL Server setup

The key is a new option (which you can also specify in a configuration file):

It's nice that you can really reduce the amount of time it takes to create or restore databases later, without having to remember to go into gpedit, assign the rights correctly, and restart the service. But a much bigger benefit to me is the ability to configure larger tempdb files during setup, taking early advantage of IFI.

Now, there are some limits during setup; for example, the number of tempdb files is limited to 8 (or the number of cores, whichever is less), and the size of each file can only reach a max of 1,024 MB. These limits are enforced in the UI, and I thought that I might be able to get around them by specifying higher sizes in a configuration file for an unattended install, but that didn't work either. (The logs said: "The value 8192 for the TempDB file size exceeds 1024 MB and may have impact to installation time. You can set it to a smaller size and change it after installation.") Personally, I think that in this day and age, with the speed and size of the storage we can obtain, a 1 GB cap on data file size is artificially low. So I filed a Connect suggestion:

• Connect #2457759 : tempdb data files should not be limited to 1024 MB

And then it was pointed out that Brent Ozar (@BrentO) filed a similar item earlier in the CTP cycle, when the limit was actually enforced as 256 MB instead of 1 GB:

• Connect #1841076 : TempDB setup initial size too low

I don't have any monster machines that could support 64 x 1 GB files, and that wouldn't be a realistic test either, so I resolved to testing the impact of IFI on 8 tempdb data files of 1 GB each. I'm kind of old school, so I built four different .ini files, and I've highlighted the lines I would change for each test (I wanted to baseline a minimal install with the 4 x 8 MB data files, using IFI and not, and then compare it to 8 x 1,024 MB files). Since I would be running these loops multiple times, it was important to use different instance names depending on whether IFI was enabled or not, because once you grant the right to a service account, it doesn't get taken away by simply removing the instance (and I could have set those accounts up independently, but I wanted to make these tests easy to reproduce).

And here is the batch file I used (placed in the same folder as the config files), which installed and then uninstalled the instance using each combination three times, and logged the setup times to a text file – ignoring uninstall and cleanup.

A few notes:

• You may need to change the two lines from D:\setup.exe to the path to the setup directory.
• You may need to restart your system before running this.
• You'll want to run the batch file from an elevated command prompt so that UAC doesn't interrupt you on every iteration.

I ran tests on three different systems:

• A Windows 10 VM with 4 cores and SSD storage
  Baseline test of 4 x 8MB and then 4 x 1,024 MB
• A Windows 10 VM with 8 cores and PCIe storage
  Baseline test of 4 x 8MB, 4 x 1,024 MB, 8 x 1,024 MB
• A Windows 2012 R2 VM with 16 cores and a dual-channel RAID 10 array of 8 10K SAS drives
  Baseline test of 4 x 8MB, 4 x 1,024 MB, 8 x 1,024 MB, and 16 x 1,024 MB

The output files generated a bunch of insert statements I could paste here:

CREATE TABLE #x
(
  [server] varchar(32),
  [test]   varchar(32),
  [start]  time(2),
  [end]    time(2)
);

-- inserts pasted here

SELECT [server],[test],AVG(DATEDIFF(SECOND,[start],[end])*1.0)
FROM #x
GROUP BY [server],[test];

Here were the timings across ten tests each, averaged and rounded (click to enlarge):

Predictably, IFI becomes important with larger files on slower drives

Setup takes a little over a minute across the board (how nice it is to run setup without Management Tools). The only deviation, really, was when the file sizes started to get bigger on the mechanical drives and with instant file initialization disabled. I can't pretend to be shocked by this.

Conclusion

If you are on SSD or PCIe, instant file initialization can't make things worse, but there is no clear benefit during setup, as long as the archaic file size limitations for tempdb data files remain intact. With the current rules it doesn't seem possible to test this impact beyond (1 GB x the number of cores available). If you are on slow mechanical drives, though, there is a noticeable difference, even when only initializing 8 GB or 16 GB of data – that zeroing out is rather expensive when the disk heads have to move. That said, whether setup takes 75 seconds or 2 minutes is pretty inconsequential in the grand scheme of things (unless you're installing hundreds of servers, but not automating that for some reason), so I think the bigger advantage here is convenience – not having to remember to go grant the service account the volume rights necessary some time after installation has succeeded. If you think about it, this new configuration option can actually pay off much better in automated installs of large numbers of servers, outside of any time saved during the actual installation.

• Download the config files and batch file

(My next test will take a look at the time it takes to expand the existing tempdb files to a much larger size than 1,024 MB after installation.)