Do you have Instant File Initialization?

re: Do you have Instant File Initialization?

jerryhung — Mon, 09 Mar 2009 19:31:30 GMT

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.

re: Do you have Instant File Initialization?

Scott R. — Tue, 10 Mar 2009 21:34:45 GMT

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.

re: Do you have Instant File Initialization?

TiborKaraszi — Wed, 11 Mar 2009 17:14:43 GMT

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

re: Do you have Instant File Initialization?

Gy. Herman — Tue, 11 May 2010 09:59:13 GMT

(sysinternals)

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

Does the tempdb Log file get Zero Initialized at Startup?

The Rambling DBA: Jonathan Kehayias — Fri, 14 May 2010 00:13:57 GMT

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

re: Do you have Instant File Initialization?

Kipb7 — Tue, 27 Nov 2012 01:27:55 GMT

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