Whenever I teach a class I’m amazed at the number of DBAs who don’t know about instant file initialization. Without wanting to redo blog posts that others have done, in a nutshell it allows a file to be create or grown ‘instantly’ by not having to zero the space in the file. This is especially useful in disaster recovery situations, where restoring a database may have to create the data files first – and with instant file initialization turned on it cuts out a bunch of downtime. See Kimberly’s blog post Instant Initialization – What, Why and How? for more details.
Anyway, the reason I’m posting today is to clear up some misconceptions that I keep hearing.
Misconception: instant file initialization is on by default. No it isn’t. There’s a tiny security risk with having it enabled (for systems where the SQL data drives are shared by sensitive non-SQL files) so it’s off by default and requires granting the SE_MANAGE_VOLUME_NAME permission (more commonly known as Perform Volume Maintenance Tasks). Kimberly’s blog post explains how to enable it.
Misconception: instant file initialization is Enterprise-only. No it isn’t.
Misconception: instant file initialization applies to log files too. No it doesn’t. Log files cannot be instant initializated – my blog post Search Engine Q&A #24: Why can’t the transaction log use instant initialization? explains why not.
Misconception: NTFS performs the zeroing when instant file initialization is not enabled. No it doesn’t. SQL Server does it, because it can do it faster than NTFS. When instant file initialization is enabled, SQL skips zeroing the file and instead just calls the Windows API SetFileValidData. The special permission is required to be able to call that API. MSDN has more details at http://msdn.microsoft.com/en-us/library/aa365544(VS.85).aspx. Ok – that took a bit of remembering, and digging around on MSDN.
Misconception: when instant file initialization is enabled, pages are zero’d before they’re written. No they’re not. An entire 8k image is written to disk, which overwrites that uninitialized 8k portion of the file. Another flavor of this misconception is that if a page is allocated from an extent in an instant initialized file, the unallocated pages in the extent are zero’d out – that’s not true either.
Hope this helps
PS And a perfect opportunity for another survey:
17 thoughts on “Misconceptions around instant file initialization”
Can anyone confirm if this feature still works in SQL2008? We’re now running ~30 SQL2008 x64 (build 1787) installations in production and have found that creating a new database takes forever as it did back in SQL2k. All of the new databases we tried to create were under no load b/c the servers were not in production yet. Example: Creating a 270GB database on an idle server took 2 hours consistently across 6 servers. We have the proper PVMT permission set and we’re seeing this slowness across all of the SQL2008 servers. Expanding active databases is also a chore that causes contention with user transactions. If anyone has any insight regarding this feature in SQL2008 that would be great. Thanks.
Leon
Yup, it’s the data files that are taking the longest. I’m only concerned with the data files, I know about the log files being zero’d out. The log file is 20GB, data files combined are 250GB. I purposely left out the OS b/c we’re all Win2008 x64, SQL2005 or SQL2008, and the slowness only happens with SQL2008. I was testing it just now and expanded a data file by 20GB and it took almost 5mins to add the 20GB. I should also note almost all of my SQL2008 installations are Std. Ed. on Win2008 Std. Ed., which shouldn’t matter but I’m going to test on an enterprise cluster and see. I’ll let you know what I find.
leon
It seems we’re both right. I’ve just tested on 3 different SQL2008 Ent on Win2008 Ent x64 installations and you’re right, expanding a database data file by 20GB happens in literally 1-2secs. Perfect.
I then tested it on 3 different SQL2008 Std on Win2008 Std x64 installations and it’s painfully slow. Taking ~4mins to expand a datafile by 20GB.
All 6 SQL installations are running the same build, 1787. All have the permission set. I have a terrible feeling MS took this feature out of the Std edition sort of like what they did with the lock pages in memory feature for SQL2005 Std. That lack of transparency by them burned us for months until they fessed up that lock pages in memory doesn’t work in 2005 Std. Ed.
I’ll run Kimberly’s little test using dbcc page to verify my claim. If true then I can take that to MS and ask wth? Thanks.
leon
Absolutely it still works. Are you sure it’s the data files that are taking the time? How big is the log file? The log is always zero-initialized.
It’s definitely supposed to be there, there’s no reason for removing it. Are you sure you bounced SQL Server after enabling it? Might be a Windows bug too. Let me ping the dev team.
Ok – checked with the dev team and nothing changed whatsoever in SQL Server. Maybe a Windows issue? Try SQL Enterprise on Windows Std and see if that makes the difference?
My fault. The group that was added to the permission didn’t include the service account (it was removed as part of a security initiative). Adding the service account to the PVMT privilege of course works. Sorry for the runaround. Thanks for checking anway.
leon
Hmmm… We’re building a new cluster at my workplace, have installed SQL Server 2008 R2 SP1, on Windows Server 2008 R2 on a Dell platform; have added the SQL Server service account (for a named instance) to the ‘Perform Volume Maintentance Tasks’ permissions in Local Security Policy… and yet the instantaneous file allocation appears to be anything but instantaneuous, taking several minutes to allocate 4 GB of data file. Log file is only specified to be 1024 KB.
We thought it might be the disk or the server configuration, but the server vendor simulated a database create and it ran in just a couple seconds, appearing to rule out hardware. Everybody agrees that it should work; it just doesn’t.
Is there some setting deep in the bowels of this stuff that, when SQL Server asks for the fast-initialize, it counters with an ‘As you were, soldier’?
Did you restart SQL Server after granting the permission? SQL Server only checks for that permission when the service is started. Apart from that, the only thing that will stop SQL Server doing instant initialization is trace flag 1806 enabled, or Transparent Data Encryption enabled for the database.
Hello Paul,
Thanks for wondering article on Instant File Initialization. I was trying this feature on my test system and when looked into the sql server error log, It zero initialized the log two times; one right creating the database and one after starting the database and second one says “FixUP Tail log Process” and I am not sure why it has to do two times. Any thoughts on this?? Thanks.
Once it’s done with recovery it writes zeroes into the unused portion of the active VLF.
Hello All!
Enable the undocumented traceflag with
DBCC TRACEON(3004,3605,-1)
to see in SQL Error Log if the Zeroing is happening or not (if it works, you should only see the LDFs being zeroed out).
If you are doing this on a cluster, ensure the ‘Perform Volume Maintenance Tasks’ is done on ALL nodes before the instance restart
Yup – I explain that in this post https://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/
Great … very helpful information thanks for sharing
I’ve been doing a series of tests on instant file initialisation on database recovery. I found that recovery completes in approaching half the time with it enabled. Nice!
http://rule30.wordpress.com/2014/04/18/how-to-halve-your-database-recovery-time-in-60-seconds/
Cheers!
Paul sir, where in SQL server instant file initialization takes place except data files. Do sparse files and buffer pool extension , checkpoint files in in memory also zero initialized.
No – only for data files.