Don't Forget to Enable "IFI" on New Installations
Instant File Initialization (IFI) is a simple feature with performance benefits often left behind on installations of SQL Server that have seen their share of upgrades or migrations. If it wasn’t available in previous versions of Windows Server or Microsoft SQL Server, there’s a good chance someone unfamiliar with its purpose didn’t enable it during an upgrade. Why risk enabling a new feature to a system that’s been stable and passed the test of time? During installations of SQL Server 2016 onwards, this presents itself as the “Grant Perform Volume Maintenance Task” checkbox SQL Server asks you to check on or leave off (1). It can be enabled in older SQL versions as well, though by different means.
The benefits of enabling this means being able to write data to disk faster. Without IFI enabled, anytime SQL Server needs to write to disk it first must zero out previously deleted files and reclaim any space on the disk that was once used. This happens anytime a new database is created, data or log files are added, database size is increased (including autogrowth events), and when restoring a database. Enabling the IFI feature can bypass this “overwriting the disk with zeros” process used in the Windows file initialization process. The resulting benefits to disk performance compound as data grows and especially when non-solid-state media is used.
An analogy to what’s happening here is when you’re formatting a USB thumb drive and being presented with “Perform a Quick Format” checkbox. This would be like enabling IFI where Windows basically just claims all the diskspace quickly and lets you go about your day. Without the Quick Format, Windows goes through and writes zeros to each sector of the drive (which also reveals bad sectors – but unrelated to SQL’s IFI usage) which takes much longer. It’s essentially writing enough to cover all available space, hence taking longer. You’ve probably noticed these differences in formatting speeds before. The performance benefit of Quick Format is like SQL Server with IFI enabled. It’s becomes more evident as the size of storage or data increases.
Note (1) : If you’re using a SQL Domain User Account as a Service Logon Account instead of the service account (NT Service\MSSQLSERVER) SQL Server defaults to, you’ll need to grant the account “Perform Volume Maintenance Tasks” separately under the “Local Policies”. Double check your SQL service account has this right granted to be safe. For instructions on granting permissions, you can follow Microsoft’s documentation here.
If you want to know other ways to enable IFI on your server without the re-installation SQL or want to know how to check if IFI is enabled, feel free to reach out. Any questions, comments, or feedback are appreciated! Leave a comment or send me an email to aturika@newtheme.jlizardo.com for any SQL Server questions you might have!
