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):
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:
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:
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).
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
;True for IFI = ON, False for OFF:
;1024 for 8 GB total, 8 for 64 MB total:
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.
@echo off 2>nul
echo. > %outputfile%
rem Remove Eight and/or Sixteen if you only have 4 cores!
FOR %%e IN (Baseline Four Eight Sixteen) DO (
FOR %%x IN (IFI_ON IFI_OFF) DO (
FOR /L %%A IN (1,1,3) DO (
echo INSERT #x VALUES('%%e', '%%x', '!TIME!', >> %outputfile%
D:\setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ConfigurationFile=%%e_%%x.ini
echo '!TIME!' ^) >> %outputfile%
D:\setup.exe /Q /ACTION=UNINSTALL /INSTANCENAME=ABTEST%%x /FEATURES=SQL
rem del /Q /S "C:\Program Files\Microsoft SQL Server\MSSQL13.ABTEST%%x\*.*"
rem rd /Q /S "C:\Program Files\Microsoft SQL Server\MSSQL13.ABTEST%%x\"
echo …test complete.
A few notes:
- You may need to change the two lines from
D:\setup.exeto 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):
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.
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.
(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.)