SQL SERVER 2022: Instant File Initialization (IFI)

Instant File Initialization in SQL server – Enable it now.

Are you looking for steps to enable Instant File Initialization in SQL Server? We can help you to enable it.

SQL Server allocates space and fills it with zeroes for certain operations like creating/restoring a database or growing data/log files which is tedious work.

With Instant File Initialization (IFI), we can skip the step of zero-writing and begin using the allocated space immediately for data files. Moreover, IFI allows for faster execution of the file operations in SQL Server.

Performance Benefits of Enabling Instant File Initialization

Enabling instant file initialization for SQL Server results in improved performance by reducing the amount of time it takes to:

• Create a database.
• Add data or log files, to an existing database.
• Increase the size of an existing file (including autogrow operations).
• Restore a database or filegroup.

SQL Server 2022 and IFI

Transaction log files cannot be initialized instantaneously, however, starting with SQL Server 2022 (16.x), instant file initialization can benefit transaction log autogrowth 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.

WHY IFI IS A GOOD IDEA

The larger the growth operation, the more noticeable the performance improvement is with IFI enabled. For instance, a data file growing by 20 GB can take minutes to initialize without IFI. 

Bonus: Enabling IFI can also make restoring databases considerably faster, too!

WHY IFI MAY NOT BE A GOOD IDEA

By not writing zeros across newly allocated space, it leaves the possibility open that deleted files may still exist in that space and be somehow accessible. The deleted files could be accessed through the backup file or if the database is detached. However, this risk can be mitigated by making sure the detached data files and backup files have restrictive permissions.

Note: Also, IFI will not happen if Transparent Data Encryption (TDE) is in use.

How to Enable It

SQL Server doesn’t have a setting or checkbox to enable IFI.

Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows. Then:

1. Expand the Local Policies Folder
2. Click on User Rights Assignment
3. Go down to the “Perform Volume Maintenance Tasks” option and double click it
4. Add your SQL Server Service account, and click OK out of the dialog.

How to Check if Instant File Initialization is Enabled

You can check to see if SQL Server is able to use instant file initialization in your environment by creating a dummy database. If it’s enabled you will see messages in the SQL Server Error Log for the zeroing out of the log file only. If it is not enabled, you will in addition also see messages for the zeroing out of the data file.

Steps to check if instant file initialization is enabled:

1. Enable trace flag 3004
2. Enable trace flag 3605
3. Create a dummy database 
4. Review the messages in the SQL Server Error Log

USE master;

 

--Set Trace Flags 3004 and 3605 to On.

DBCC TRACEON(3004,-1);

DBCC TRACEON(3605,-1);

 

--Create a dummy database to see what output is sent to the SQL Server Error Log

CREATE DATABASE DummyDB ON  PRIMARY

(NAME = N'DummyDB', FILENAME = N'D:\DummyDB.mdf', SIZE = 2MB)

 LOG ON

( NAME = N'DummyDB_log', FILENAME = N'D:\DummyDB_log.ldf', SIZE = 1MB)

 

--Turn the two Trace Flags to OFF.

DBCC TRACEOFF(3004,3605,-1);

 

--Remove the DummyDB

DROP DATABASE DummyDB;

 

--Now go check the output in the SQL Server Error Log File

 If enabled you will see an entry in the SQL Server Error log like similar to the one below. Notice there is no reference to the database data file.

  

SQL Server: Filter output of sp_who2:

sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who. However it is also one of the most frustrating as it only takes a single parameter and the results cannot be ordered. For a large server with a lot of connections this can be a real nuisance. I usually store the results in a temporary table and then filter and/or order the results from there:

sp_who2 [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

Permissions needed for sp_who2

A login can run sp_who2 and obtain information about its own connection. For a full list of SPID’s, either the login has to have sysadmin permission or VIEW SERVER STATE permission.

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE ....