Instant File Initialization (IFI)

One way to improve performance on SQL Server is with IFI or Instant File Initialization.

Normally files are zeroed out on a database growth during an AUTOGROW, RESTORE, CREATE DATABASE or ALTER DATABASE. This is done by SQL Server when the file grows, it runs through that file and writes zeroes to the entire new allocation in the file. The zeroing process can take a great deal of time, the Instant file initialization process skips this zeroing, and just allocates the file. This works since SQL Server will just write each 8k page to disk as they are used, thus overwriting the uninitialized file.

Running some tests on a local virtual machine running SQL Server 2012 (similar results tested on SQL Server 2014 and SQL Server 2016), here is what I found.

• Creating a new database (10GB file size) took 2 minutes and 6 seconds, with IFI enabled, it took just 4 seconds.
• Autogrow of a data file witha 1GB growth size took 13 seconds without IFI, with IFI it took less than a second.

For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled.

Caveats

• IFI Only works for data files, not log files.
• IFI will not work if TDE (Transparent Data Encryption) is being used.
• You can use DBCC Page to view the junk in the file when it is not zero initialized. (This may be a security concern)

IFI was Originally introduced in SQL Server 2005, and works on all version and editions of SQL Server since 2005, excluding Azure sql databases.

To Enable IFI….

1. Open Local Security Policy and go to Local Policies –> User Rights Assignment.
   
2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
   
   
3. Restart the SQL Server service.
4. IFI will now be enabled.
5. Test it…

Here are the scripts that I used to test it…

First Creating a 10gb database.

-- some variables for measuring timing.
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();

CREATE DATABASE [ifi_demo] ON PRIMARY
(
NAME = N'ifi_demo_data',
FILENAME = N'E:\IFI_TEST_DATA\TestDatabase.mdf' ,
SIZE = 10gb ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ifi_demo_log',
FILENAME = N'E:\IFI_TEST_DATA\TestDatabase_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 1024KB
);
SET @t2 = GETDATE();
SELECT DATEDIFF(second, @t1, @t2) AS elapsed_seconds, DATEDIFF(millisecond, @t1, @t2) AS elapsed_ms;

Then autogrowth of a 1gb chunk on a second database.

CREATE DATABASE [ifi_demo2] ON PRIMARY
(
NAME = N'ifi_demo_data2',
FILENAME = N'E:\IFI_TEST_DATA\TestDatabase2.mdf' ,
SIZE = 4MB ,
FILEGROWTH = 1GB
)
LOG ON
(
NAME = N'ifi_demo2_log',
FILENAME = N'E:\IFI_TEST_DATA\TestDatabase2_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 1024KB
);

GO
use [ifi_demo2];
GO
SELECT size / 128.0 as sizeMB, *
FROM sys.database_files
ORDER by type_desc;

create table TestFiller
(
id INTEGER IDENTITY(1,1),
SomeText varchar(4000) default cast(newid() as varchar(4000)),
SomeText2 varchar(4000) default cast(newid() as varchar(4000)),
SomeText3 varchar(4000) default cast(newid() as varchar(4000)),
SomeText4 varchar(4000) default cast(newid() as varchar(4000)),
SomeText5 varchar(4000) default cast(newid() as varchar(4000)),
SomeText6 varchar(4000) default cast(newid() as varchar(4000)),
SomeText7 varchar(4000) default cast(newid() as varchar(4000))
)
GO
set nocount on;
INSERT INTO TestFiller Default Values;
GO 6720

set nocount off;
SELECT size / 128.0 as sizeMB, *
FROM sys.database_files
ORDER by type_desc;
SELECT size / 128.0 as sizeMB, *
FROM sys.database_files
ORDER by type_desc;
-- some variables for measuring timing.
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
-- insert just one row to cause file autogrowth.
INSERT INTO TestFiller Default Values;

SET @t2 = GETDATE();
SELECT DATEDIFF(second, @t1, @t2) AS elapsed_seconds, DATEDIFF(millisecond, @t1, @t2) AS elapsed_ms;
--13 seconds without IFI
-- 0 seconds with IFI

set nocount off;
SELECT size / 128.0 as sizeMB, *
FROM sys.database_files
ORDER by type_desc;

And finally to view an unused page that was not zero initialized.

-- you can use DBCC Page to view the contents of non-zeroed out pages.
DBCC TRACEON(3604) with no_infomsgs;
DBCC PAGE('ifi_demo2', 1, 1000, 2)

More Info:

• http://DatabaseHealth.com/IFI
• Download Database Health Monitor

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!