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?

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/do-you-have-instant-file-initialization/

Published Monday, March 9, 2009 2:41 PM by TiborKaraszi

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
New Comments to this post are disabled
Privacy Statement