What is IFI, and how do I get it?
IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.
This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.
Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.
What does SQL 2016 do differently?
Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.
If you are doing a command line install you can add the /SQLSVCINSTANTFILEINIT argument or check the box in the GUI.
Installing SQL 2016 with IFI enabled
Here we’re just going to take a quick look at the installation option, cover the places where we can validate that IFI was enabled, and also look at, what I consider to be, a security risk.
Continue reading on SirSQL.net.
Continue reading on SirSQL.net.