Instant File Initialization – Killer Feature for SQL Server

When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducing the execution time of some operations and the load on the disk subsystem.

Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005.

The benefits of the Instant File Initialization are the following:

1. Accelerating the creation of a new database.

2. Reducing delays and time required to expand data files.

3. Reducing the start time of SQL Server due to faster tempdb initialization.

4. Reducing time for restoring from a backup, because SQL Server reserves space for files before restoration and then transfers the information from the backup into the files.

It is important to note that Instant File Initialization works only for data files (MDF and NDF). Log files (LDF) are always zero-initialized.

How to use Instant File Initialization?

The option may be enabled fairly simply. Open SQL Server Configuration Manager to see the name of SQL Server instance.

After that, in the Local Security Policy window, navigate to User Rights Assignment and select Perform volume maintenance tasks.

On the Local Security Setting tab, add the server instance, as shown in the following picture.

SQL Server instance verifies the rights required for working with Instant File Initialization only once – during the launch. That is why you need to restart SQL Server to apply the configuration.

Test Cases

First, let’s examine whether Instant File Initialization is enabled?

If it is disabled, upon execution of the query:

USE [master]
GO

IF DB_ID('IFI_DB') IS NOT NULL
    DROP DATABASE IFI_DB
GO

DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS
CREATE DATABASE IFI_DB
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS
GO

IF DB_ID('IFI_DB') IS NOT NULL
    DROP DATABASE IFI_DB
GO

EXEC sp_readerrorlog 0, 1

in SQL Server log, you will see that data files are filled with zeros:

But if Instant File Initialization is enabled, then only the log file is filled with zeros:

Alternatively, you can use the following script:

USE [master]
GO

SET NOCOUNT ON;

IF DB_ID('IFI_DB') IS NOT NULL
    DROP DATABASE IFI_DB
GO

IF OBJECT_ID('tempdb.dbo.#IFI') IS NOT NULL
    DROP TABLE #IFI
GO

CREATE TABLE #IFI (dt DATETIME, info VARCHAR(50), txt VARCHAR(MAX))
GO

DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS
CREATE DATABASE IFI_DB
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS
GO

IF DB_ID('IFI_DB') IS NOT NULL
    DROP DATABASE IFI_DB
GO

INSERT INTO #IFI
EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS(
    SELECT 1
    FROM #IFI
    WHERE txt LIKE 'Zeroing completed%'
        AND txt LIKE '%IFI_DB.mdf%'
        AND dt > DATEADD(HOUR, -1, dt)
)
    PRINT 'Instant File Initialization = OFF'
ELSE
    PRINT 'Instant File Initialization = ON'

In case you need to temporarily disable Instant File Initialization, you can enable trace flag 1806. However, as practice shows, using this functionality greatly saves time and reduces disk load.

Here are a couple of test cases and time spent executing them with and without Instant File Initialization:

USE [master]
GO
IF DB_ID('IFI_DB') IS NOT NULL
	DROP DATABASE [IFI_DB]
GO
CREATE DATABASE [IFI_DB]
    CONTAINMENT = NONE
    ON PRIMARY (NAME = N'IFI_DB', FILENAME = N'D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf', SIZE = 102400MB
    LOG ON (NAME = N'IFI_DB_log', FILENAME = N'D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf', SIZE = 2048KB)
GO
-- OFF: 00:16:04
-- ON:  00:00:12

ALTER DATABASE [IFI_DB] MODIFY FILE (NAME = N'IFI_DB', SIZE = 204800MB)
GO
-- OFF: 00:14:32
-- ON:  00:00:11

BACKUP DATABASE [IFI_DB]
    TO DISK = N'D:\DATABASES\SQL_2012\BACKUP\IFI_DB.bak'
    WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION
GO
IF DB_ID('IFI_DB') IS NOT NULL
    DROP DATABASE [IFI_DB]
GO
-- OFF: 00:00:59
-- ON:  00:00:58

USE [master]
GO
RESTORE DATABASE [IFI_DB]
    FROM DISK = N'D:\DATABASES\SQL_2012\BACKUP\IFI_DB.bak'
    WITH FILE = 1, NOUNLOAD
-- OFF: 00:28:03
-- ON:  00:00:16

Summary

The use of Instant File Initialization is a terrific way to reduce downtime during failure recovery. It won’t take you much time to initialize files for filling them with zeros before the recovery. So keep in mind that there is such useful thing as Instant File Initialization.

P.S: In SQL Server 2016, you can easily turn on this option upon installing a new instance.