THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Do you have Instant File Initialization?

You might ask yourself: What is Instant File Initialization and why does it matter?

What Instant File Initialization is:
Instant File Initialization allow SQL Server to allocate storage (space from disks) very very quickly. As you probably know, when you delete files they are not physically deleted from the disk - they are only marked as deleted in the file system allocation structures. This is why various undelete programs can recover deleted files. So imagine a process requiring disk space. The disk area given to the process from the file system and operating system could potentially contains some secret information from deleted files. This is why the file system/OS insist to zero out the storage before the process can actually see it. (I'm not OS / file system expert so anyone is free to correct me here.) That is, unless the one allocating the storage has some special privileges.

When does the zeroing out of disk space occur?
Whenever SQL Server need disk space, such as:

  • Create database
  • Add file to database
  • Restore (if the restore process includes database creation)
  • File growth (both manual and auto-grow)
  • Backup [edit 2011-08-26: Not sure how this got here, AFAIK shouldn't be here]

Can we avoid the slow zeroing out space?
Yes, but only if you are on SQL Server 2005 or higher and for some operations: creation and allocation of data database files (mdf and ndf). The SQL Server service account need to have appropriate permissions from the OS. To be more precise, it need to have a privilege called SE_MANAGE_VOLUME_NAME. This is by default granted to Administrators. Do you run your SQL Server as an account being member of Administrators? I hope not. Did you grant this permission to the service account?

How do I grant this privilege?
This is easy. Add the SQL Server service account to the "Perform Volume Maintenance Tasks" security policy.

Does it matter?
You be the judge. Just to give you an idea, I created a database with a data file of various size (I had the log file at 1MB for all tests in order for it to influence the least). I timed it both with and without Instant File Initialization. I ran it on my desktop machine which has a RAID0 of two 7200RPM disks:

Size without IFI with IFI
1GB 10.3 s 0.3 s
10GB 128 s 1.3 s
50GB 663 s 4.5 s

The difference is roughly a factor of 100!

When does it hurt?
Basically every time disk storage is allocated. But let us focus of the occasions where you can do anything about it, i.e., when you can have Instant File Initialization. Such occasions include:

  • Whenever a database is created. Space need to be allocated for the data file(s).
  • Whenever a data file is manually expanded in size.
  • Whenever you have auto-grow for a data file. Note that potentially some poor user will now sit and wait for the auto-grow to complete.
  • When you start SQL Server. Why? Tempdb need to be re-created.
  • When you perform restore, if the destination database not already exists with matching database file structure.

How can I tell if I have Instant File Initialization?
I find it easiest to just create a database and time it, using some reasonable size for your data file, like 5GB. Actually, run two test: One with 5GB data file and really small log file. And then another with very small data file and 5GB log file. Remember that we never have Instant File Initialization for log files. For instance, run below and you will see (adjust the file path for the database files). You need to adapt your code for file path name, possibly database name and the datetime handling if you are lower then SQL Server 2008:

DECLARE @t time(3SYSDATETIME()
CREATE DATABASE IFI_test_ld
 
ON  PRIMARY 
 
(NAME N'IFI_test'FILENAME N'C:\IFI_test\IFI_test_ld.mdf'SIZE 5GBFILEGROWTH 100MB)
 
LOG ON 
 
(NAME N'IFI_test_log'FILENAME N'C:\IFI_test\IFI_test_ld.ldf'SIZE 1MB,  FILEGROWTH 10MB)
SELECT DATEDIFF(ms@tCAST(SYSDATETIME() AS time(3))) AS LargeDataFile
SET @t SYSDATETIME()
CREATE DATABASE IFI_test_ll
 
ON  PRIMARY 
 
(NAME N'IFI_test'FILENAME N'C:\IFI_test\IFI_test_ll.mdf'SIZE 3MBFILEGROWTH 100MB)
 
LOG ON 
 
(NAME N'IFI_test_log'FILENAME N'C:\IFI_test\IFI_test_ll.ldf'SIZE 5GB,  FILEGROWTH 10MB)
SELECT DATEDIFF(ms@tCAST(SYSDATETIME() AS time(3))) AS LargeLogFile

Are numbers for above two about the same? If yes, then you don't have Instant File Initialization. If the one with large data file is much quicker, then you do have Instant File Initialization. And now you also know approx how long it takes to allocate 1 GB with of data and log file for your SQL Server.

John Samson blogged about an alternative way to check, involving trace flags.

I'm curious: Did you have Instant File Initialization?

Published Monday, March 09, 2009 2:41 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jerryhung said:

Good article I always use

http://www.mssqltips.com/tip.asp?tip=1572

For some reason I kept thinking this is a 2005 Enterprise feature, guess not.

March 9, 2009 3:31 PM
 

Scott R. said:

Tibor,

Great post!  A lot of energy was spent on promoting this feature when SQL 2005 was being released, but the attention has fallen off over time.  A reminder of this benefit and how to get it is a good thing.

As you mentioned, Instant File Initialization applies only to database files, and does not apply to log files (still initialize the old, slower way).

I found another way to confirm if you have the Manage Volume privilege for the SQL Server service account: use the Sysinternals command line utility AccessChk (free download at http://technet.microsoft.com/en-us/sysinternals/bb664922.aspx), using the following command line parameters:

accesschk.exe -p sqlservr.exe -t -v -q –f

The SeManageVolumePrivilege will show up in the list followed by either “ENABLED” or “DISABLED”.

I believe AccessChk will also report on the status of the “Lock Pages in Memory” privilege that can be important to some SQL Server instances (32-bit using AWE, possibly x64 64-bit?).

These privileges are associated with the service account used by the SQL Server DB engine service, and can be harder to confirm through conventional security administration tools if you are not a security administrator (as most of us are not).

Paul Randal had a few good posts on this topic awhile back:

http://sqlskills.com/BLOGS/PAUL/post/How-to-tell-if-you-have-instant-initialization-enabled.aspx

http://sqlskills.com/BLOGS/PAUL/post/Follow-on-from-instant-initialization-privilege-checking.aspx

Scott R.

March 10, 2009 5:34 PM
 

TiborKaraszi said:

Hi Jerry and Scott,

Thanks. I realized after posting this that there already are plenty of blog posts on this. But, as Scott said, a reminder can't harm. After some discussion, it seems I was incorrect in who does the zeroing out of disk (when we don't have IFI) - it seems it is SQL Server.

Also, Paul Randal just yesterday blogged about some commong misconceptions around IFI:

http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx

March 11, 2009 1:14 PM
 

Gy. Herman said:

(sysinternals)

Process Explorer->sqlservr.exe->right click Properties->Security tab\Privilage listbox also shows if SeManageVolumePrivilege is assigned to the service account user.

May 11, 2010 5:59 AM
 

The Rambling DBA: Jonathan Kehayias said:

While working on a problem today I happened to think about what the impact to startup might be for a

May 13, 2010 8:13 PM
 

Kipb7 said:

Thanks, very handy.

Another pattern for timing, expressing time in seconds:

declare @t1 datetime2=SYSDATETIME()

SELECT TOP 1 * FROM sys.columns A --slow thing

JOIN sys.columns B ON A.object_id>B.object_id

WHERE A.object_id<1000 ORDER BY A.object_id^B.object_id

print datediff(ms, @t1, SYSDATETIME())*.001

print convert(varchar,datediff(ms,@t1,sysdatetime())*.001)+' seconds'

SELECT datediff(ms, @t1, SYSDATETIME())*.001 AS LargeLogFile

November 26, 2012 8:27 PM
 

Tibor Karaszi said:

Instant File initialization, IFI, is generally a good thing to have. Check out this earlier blog post

October 30, 2013 11:17 AM
 

Rob Nicholson said:

I have created a Connect Item to make it easier to check whether Instant File Initialization is enabled on your instance: https://connect.microsoft.com/SQLServer/feedback/details/809901

November 27, 2013 7:12 PM
 

TiborKaraszi said:

Voted, Rob.

November 28, 2013 1:59 AM
 

Tibor Karaszi said:

Read this if you want to read more about instant file initialization (IFI). In an earlier blog post ,

March 12, 2014 7:32 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement