Instant File Initialization

When SQL Server reserves a new disk space, it initializes it with zeros. This behavior can be disabled and, as a result, reduce the time required to perform certain operations and reduce the load on the disk subsystem.

The ability to reserve disk space without initialization is called Instant File Initialization (instant file initialization).

This feature is not well known, although its use has become possible since SQL Server 2005 .
')
What benefits can be obtained from Instant File Initialization :

1. Accelerate the creation of a new database.
2. Reduce delays and reduce the time required to increase data files
3. Shorten the start time of SQL Server , because tempdb initialization will be faster
4. Reduce time when restoring from a backup, because before restoring SQL Server reserves space for files, and then transfers information from backup to them.

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

How to use Instant File Initialization ?

Everything is very easy to turn on. Open the SQL Server Configuration Manager and find out the name of the instance of our SQL Server .



Next in the Local Security Policy ( Local Security Policy ) look for User Rights Assignment (Name of user rights) - Perform volume maintenance tasks ( Perform maintenance tasks volumes)



Add the person we need to the settings, as shown in the picture.



The rights that are required to work Instant File Initialization , an instance of SQL Server checks only once - during startup. That is why you need to restart SQL Server to apply our settings.

Now we will conduct experiments ...

First, let's check ... Is Instant File Initialization enabled?

If it is off, then when executing the request:

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 the log you can see the filling of data files with zeros:

 Zeroing D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf from page 0 to 520 (0x0 to 0x410000) Zeroing completed on D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 0 to 130 (0x0 to 0x104000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf Starting up database 'IFI_DB'. FixupLogTail(progress) zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from 0x5000 to 0x6000. Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 3 to 33 (0x6000 to 0x42000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf 

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

 Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 0 to 130 (0x0 to 0x104000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf Starting up database 'IFI_DB'. FixupLogTail(progress) zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from 0x5000 to 0x6000. Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 3 to 33 (0x6000 to 0x42000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf 

If you are too lazy to look at the journal, 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 turn on trace flag 1806. But as practice shows, the ability to use this functionality saves time and reduces disk load.

Here are a couple of test cases and time to run 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 

If in general, using Instant File Initialization is a terrific way to reduce downtime when recovering from a crash. Creating files will not take a long time to initialize them with zeros before the restore operation itself begins. So take on arms ... that there is in the world such a chip as Instant File Initialization .

By the way, for SQL Server 2016 CTP3.0, you can enable Instant File Initialization at the installation stage:



If you want to share this article with an English-speaking audience:
Instant File Initialization - Killer Feature for SQL Server