Notes on SQL

Background
When SQL Server extends data or log files it fills the newly-acquired space with zeroes, to overwrite any data that may have been there beforehand. This action takes place when a database is created, restored, files are extended or added.
This can take a long time, depending upon the amount of space being initialised. This is one good reason for sizing your file requirements accurately and choosing when to make any extensions, rather than having the system perform them when the system is probably busy and holding up any number of processes.

What is Instant File Initialization?
Instant File Initialization (IFI) removes the zero-fill operation, so once the space is allocated it is immediately available. This can save a considerable amount of time.

How do I check for IFI?
One way of being notified of the status of IFI is via SQLCop, a free add-on that can be integrated within Redgate’s SQL Test framework.

However, this requires that xp_cmdshell is enabled and so might not be suitable for all environments.

Another method is via Brent Ozar’s ‘sp_blitz’ procedure. For versions from SQL Server 2014 SP2 this is fine, as it reads the SQL Server error log for information message relating to IFI.

EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization: enabled';

From SQL Server 2008R2 SP1 a DMV has been available, which shows (amongst other things) if IFI is enabled and the Service Account, which is required should you need to enable IFI.

SELECT  d.servicename ,
		d.service_account ,
		d.instant_file_initialization_enabled
FROM    sys.dm_server_services AS d;

Caveats
Prior to SQL Server 2022, IFI does not work against the log file – this must zero-fill as part of normal operation. It pays to size your log file correctly. Even with SQL 2022, it only works where the autogrowth is 64Mb or less.

IFI does not work if Transparent Data Encryption (TDE) is used.

IFI does not work on sparse file (for example – database snapshots).

Trace flag 1806 disables IFI.

Because IFI does not overwrite newly-acquired disk space, it is technically possible to see the data within the deleted files that occupy this new disk space. This isn’t a trivial task (with DBCC PAGE) but may need to be considered.

Additional Links
sp_blitz
SQLCop
IFI and a DBCC PAGE example
Microsoft Docs on IFI