Does Instant File Initialization work for manual log file growths?

SQL Server 2022 introduced Instant File Initialization for transaction log file growth events. In the What's New in 2022 page, Microsoft notes:

In general, transaction log files cannot benefit from instant file
initialization (IFI). Starting with SQL Server 2022 (16.x) (all
editions) and in Azure SQL Database, instant file initialization can
benefit transaction log growth events up to 64 MB. The default auto
growth size increment for new databases is 64 MB. Transaction log file
autogrowth events larger than 64 MB cannot benefit from instant file
initialization.

In order to test that, I tried repeatedly growing the log file at different sizes (say, 50 and 70MB), but ... none of them were instantaneous.

DROP DATABASE LogGrowthTest;
GO
CREATE DATABASE [LogGrowthTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LogGrowthTest', FILENAME = N'Z:\MSSQL\Data\LogGrowthTest.mdf', 
    SIZE = 8192KB , FILEGROWTH = 60000KB )
 LOG ON 
( NAME = N'LogGrowthTest_log', FILENAME = N'Z:\MSSQL\Data\LogGrowthTest_log.ldf' , 
    SIZE = 8192KB , FILEGROWTH = 60000KB )
GO
DECLARE @TestStartTime DATETIME2 = GETDATE(), @i INT = 1,
    @StringStarter NVARCHAR(4000) = N'ALTER DATABASE [LogGrowthTest] MODIFY FILE ( NAME = N''LogGrowthTest_log'', SIZE = ',
    @StringToExec NVARCHAR(4000);   
WHILE @i < 101
    BEGIN
    /* CHANGE THE 63 IN THE BELOW LINE TO CHANGE FILE GROWTH SIZE: */
    SET @StringToExec = @StringStarter + CAST((@i * 63) AS NVARCHAR(10)) + N'MB );';
    PRINT(@StringToExec)
    EXEC(@StringToExec);
    SET @i = @i + 1;
    END
SELECT DATEDIFF(millisecond,@TestStartTime, GETDATE()) AS TestDurationSeconds
GO

I tried 63MB, 65MB, and it didn't seem to make much difference - the tests took around 15-16 seconds for 100 growth events.

So the question is, does Instant File Initialization just not work for manual log file growths, even at small sizes? Only autogrowth events? (I haven't been able to prove yet that it works for autogrowth events either.)

Does Instant File Initialization work for manual log file growths?

Yes it does.

I tried the following on SQL Server 2019 and SQL Server 2022 (with IFI enabled during setup)

CREATE DATABASE [LogGrowthTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LogGrowthTest', FILENAME = N'...LogGrowthTest.mdf' , SIZE = 8MB , FILEGROWTH = 60000KB  )
 LOG ON 
( NAME = N'LogGrowthTest_log', FILENAME = N'...LogGrowthTest_log.ldf' , SIZE = 8MB , FILEGROWTH = 60000KB  )
GO

ALTER DATABASE [LogGrowthTest] MODIFY FILE ( NAME = N'LogGrowthTest_log', SIZE = 64MB );

For SQL Server 2019 I can see the entirety of the newly allocated file section being written to in 8MB chunks (from the length and offset)

The stack in ProcMon shows this activity is happening in zeroing out of the file

There is no such corresponding section in 2022

Time elapsed between first and last procmon entry was 46 ms in the 2019 case (21:30:54.8866621 to 21:30:54.9330198) and 7.9 ms in the 2022 case (21:31:54.8078691 to 21:31:54.8157769).

I saw similar time differences reported via the SET STATISTICS TIME ON output too.

both were writing to the same laptop disc (on which the eagle eyed might notice from the above I accidentally named my 2022 instance 20222).

I was interested in why the loop performed so badly though. It seems that only the first entry really benefits from IFI.

When I add the following to my code above...

ALTER DATABASE [LogGrowthTest] MODIFY FILE ( NAME = N'LogGrowthTest_log', SIZE = 127MB );

The second growth takes significantly longer and writes to much more of the file (including the section that previously was not initialised)

This is not due to zeroing out of the file but also is not something I see in my SQL Server 2019 instance so seems to be additional work that may or may not be related to this feature.

Annoyingly for some reason ProcMon just shows me a blank tab instead of the call stacks for the highlighted WriteFile but windows performance recorder would imply this is spent in sqlmin.dll!SQLServerLogMgr::FormatVirtualLogFile (which ties in with Paul's answer)

StackExchange Database Administrators Q#321411, answer score: 8