Instant File Initialization–Do You Know It’s Value?

This weeks post by our US Senior PFE Susan Van Eyck is about the value of using Instant File Initialization.

Lets have a look!

Be convinced of it’s value!

This week’s Byte is about something the SQL Server world refers to as “Instant File Initialization” or IFI.  Interestingly, it’s not really a SQL Server thing at all, but rather an OS-level security policy (Perform Volume Maintenance Tasks) that can be granted the SQL Server service account to make data file related operations, well…instant – or darn close to it!  It works by skipping the step of zeroing out disk space before SQL Server is allowed to use it.  This can save significant time when you create, manually grow, autogrow or restore your databases.

Its one drawback is a slight security risk since data that was on the disk previously is not overwritten and could be viewed in SQL Server’s data files.  You can read about that here.  But, that being said, it’s recommended that you use IFI if your security policies do not forbid it.

So, I found myself working with a customer this week who wasn’t familiar with IFI and needed to be convinced of its value.  I put together a little demo script to make the point and thought it was worth sharing.

We need to start with a SQL Server instance where IFI is enabled.  In SQL Server 2012 SP4 and SQL Server 2016 SP1 and later you can query a DMV to see if IFI is enabled.  [I don’t why this wasn’t back-ported to SQL Server 2014, but perhaps it will be in a future Service Pack.]

[sql] SELECT servicename, service_account, instant_file_initialization_enabled

FROM sys.dm_server_services;[/sql]

For other versions of SQL Server you'll need to bring up the "Local Security Policy" console on the server (run secpol.msc to open it) then drill down...

Local Policies > User Rights Assignment > Perform Volume Maintenance Tasks

Double click on the policy to see which accounts have permission to use it.   If your service account isn't listed, find a dev/test server where it's safe to experiment, grant the permission to the service account then restart the SQL Server instance to enable its use.   A nice feature added in SQL Server 2016 is the ability to enable IFI during installation!

Here’s how we can test the impact of using IFI.  You’ll need to update the file paths to something valid for your machine.

[sql]-- Enable trace flag 3004 to gather information when files are zeroed out (3004)

-- and trace flag 3605 to write it to the error log

-- 2 - direct that information to the error log (3605)

DBCC TRACEON (3605, 3004);

-- Time the creation of a small database with IFI enabled

[sql]DECLARE @start_time DATETIME2 = SYSDATETIME();

CREATE DATABASE IFI_Enabled

ON  ( NAME = N'IFI_Enabled', FILENAME = N'C:\SQL_2016\Data\IFI_Enabled.mdf', SIZE = 512MB );

SELECT DATEDIFF(MILLISECOND, @start_time, SYSDATETIME()) AS 'ms to create database with INI'

GO

DROP DATABASE IFI_Enabled;

GO

-- Enable trace flag 1806 to disable IFI for this session only

DBCC TRACEON (1806);

GO

-- Time the creation of a small database with IFI disabled

DECLARE @start_time DATETIME2 = SYSDATETIME();

CREATE DATABASE IFI_Disabled

ON  ( NAME = N'IFI_Disabled', FILENAME = N'C:\SQL_2016\Data\IFI_Disabled.mdf', SIZE = 512MB );

SELECT DATEDIFF(MILLISECOND, @start_time, SYSDATETIME()) AS 'ms to create database without INI'

GO

DROP DATABASE IFI_Disabled;

GO[/sql]

On my laptop it took about 500 ms to create IFI_Enabled and about 5000 ms to create IFI_Disabled – a 10x difference.  Curious about how the time differences would scale up for larger files, I did some additional testing that really made the value of IFI clear to me.  At 10 GB the time difference jumped to 100x.  Imagine the potential time-savings if you had to restore a multi-TB database after a disaster!  [Note:  If you’re restoring to the same server you won’t need to zero out pre-existing files so you’re better off overwriting them then deleting them and starting from scratch.]

Lastly, let’s take a look at what we wrote to the error log:

First note that the transaction log files for both databases were zeroed out.  Log files must be zeroed out to work properly.  Think about this when setting autogrowth sizes and creating backups.  An autogrowth is going to happen in the middle of somebody’s transaction and you don’t want it to take minutes.  And when you restore a database the log and data files have to be sized to match what was on the source server.  If someone ran a huge, anomalous transaction that bloated the transaction log on the source server, shrink the log before backing up the database to avoid wasting disk space time spent zeroing out a 75 GB tran log during the restore.

You might notice that there are 2 lines for zeroing out each .ldf file.  I don’t know what that additional 12 ms of zeroing is about, but these 3 lines show up consistently after the database is started on my laptop and in every screenshot I could find online:

Lastly, as expected, you can see that that only the data file for IFI_Disabled appears in the error log with a zeroing time of 4260 ms.