How and Why to Enable Instant File Initialization
You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks" local security policy. In a cluster you have to grant the right on all nodes. If there are multiple instances on a server or cluster, you should grant this right to each instance’s security group.
This permission keeps SQL Server from "zeroing out" new space when you create or expand a data file (it is not applied to log files). This helps performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW. It can have a significant positive impact on how long it takes to create or expand a data file, but there is a small security risk in doing so. That is because a file "delete" really just deallocates the space and a new allocation can reuse that space which may still have data in it. When you do not zero out the existing space there is a possibility that someone could read data that you thought had been deleted. It is very common to turn Instant File Initialization on. Many shops consider the increased performance benefit to far outweigh the small security risk, but you must weigh the cost and benefits within your own environment.
How to grant this right/permission (tested on Windows 2008) to each instance of SQL Server:
· Run lusrmgr.msc on the server to find the appropriate group name for each instance of SQL Server. For example: SQLServer2005MSSQLUser$SERENITYHOME$KAYLEE (SQL 2005 named instance), SQLServerMSSQLUser$SerenityHome$WASH (SQL 2008 named instance), or SQLServerMSSQLUser$SerenityHome$MSSQLSERVER (SQL 2008 default instance).
· Run secpol.msc on the server.
· Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.
· Under Policy on the right side, go to "Perform volume maintenance tasks" and double click on it
· On the Local Security Setting tab click on the "Add User or Group" button
· In "Select Users, Computers, or Group"
o Click on "Locations" and choose either your local computer name (for local groups/standalone) or your domain (for domain groups/clusters)
o Click on "Object Types" and check "Groups"
o In "Enter the object names to select" enter your SQL Server group created by SQL setup (standalone) or your cluster domain group (for clusters).
o Choose "OK"
· Restart SQL Server
Note that if you grant the right directly to the current SQL Server service account rather than to the group, you will have to remember to grant the right again when you change the account used to start SQL Server. In general you should grant the right to the group rather than the current account (any new account should be in the standard group and inherit its permissions).
Adding the permission takes affect with a SQL Server restart, but removing the permission requires a reboot. Make sure you pay attention to what other groups have been granted this right. Often you will see local administrators having the permission and if you have the SQL Server service account in the local administrators group then it will have the permission through that group membership.
Once a SQL Server instance has this permission, SQL Server can take advantage of not having to zero out a file allocation if:
· The file is not a log file
· The OS supports the call to SetFileValidData function (https://msdn.microsoft.com/en-us/library/aa365544(VS.85).aspx )
· The account (directly or through group membership) has the privilege required
· It is not a sparse file ( a.k.a. Snapshots)
· Transparent Data Encryption (TDE) is not enabled
· Trace flag 1806 to disable instant file initialization is not on.
To see which files are being zeroed out, you can use the undocumented trace flag 3004 and the undocumented sp_readerrorlog. Undocumented means it is unsupported and may change or be removed with no notice at any time. Trace flag 3004 shows information about backups and file creations. Trace flag 3605 redirects the output to the SQL error log.
WARNING: These trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.
DBCC TRACEON(3004,3605,-1)
GO
CREATE DATABASE TestFileZero
GO
EXEC sp_readerrorlog
GO
DROP DATABASE TestFileZero
GO
DBCC TRACEOFF(3004,3605,-1)
If Instant File Initialization is not enabled, then you will see SQL Server zeroing out both the mdf/ndf (data) and ldf (log) files. You may see a wait type of PREEMPTIVE_OS_SETFILEVALIDDATA in sys.dm_exec_requests while the growth occurs.
2009-12-16 10:16:27.000 spid52 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero.mdf from page 0 to 160 (0x0 to 0x140000)
2009-12-16 10:16:27.020 spid52 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero.mdf
2009-12-16 10:16:27.190 spid52 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF from page 0 to 63 (0x0 to 0x7e000)
2009-12-16 10:16:27.200 spid52 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF
2009-12-16 10:16:27.720 spid52 Starting up database 'TestFileZero'.
2009-12-16 10:16:27.740 spid52 FixupLogTail(progress) zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF from 0x5000 to 0x6000.
2009-12-16 10:16:27.740 spid52 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF from page 3 to 32 (0x6000 to 0x40000)
2009-12-16 10:16:27.740 spid52 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF
If Instant File Initialization is enabled, then you will see SQL Server zeroing out only the ldf (log) files.
2009-12-16 11:04:33.000 spid57 Zeroing d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF from page 0 to 72 (0x0 to 0x90000)
2009-12-16 11:04:33.000 spid57 Zeroing completed on d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF
2009-12-16 11:04:33.330 spid57 Starting up database 'TestFileZero'.
2009-12-16 11:04:33.340 spid57 FixupLogTail(progress) zeroing d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF from 0x5000 to 0x6000.
2009-12-16 11:04:33.340 spid57 Zeroing d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF from page 3 to 32 (0x6000 to 0x40000)
2009-12-16 11:04:33.340 spid57 Zeroing completed on d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF
References:
· Instant Initialization - What, Why and How? https://www.sqlskills.com/blogs/kimberly/post/Instant-Initialization-What-Why-and-How.aspx
· Misconceptions around instant file initialization https://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx
· Paul Randal's blog - category = Instant Initialization https://www.sqlskills.com/BLOGS/PAUL/category/Instant-Initialization.aspx
· Database File Initialization https://msdn.microsoft.com/en-us/library/ms175935.aspx
“Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy. For more information about assigning user rights, see the Windows documentation.”
-- Cindy Gross and Denzil Ribeiro
Comments
Anonymous
November 10, 2010
Great work and article, keep it up....ThanksAnonymous
November 15, 2010
This article has been very helpful. Bonus points to the author for using Firefly/Serenity examples in their instance names.Anonymous
May 30, 2012
Taking a peek at SQL RAP recommendations-Instant File InitializationAnonymous
June 25, 2012
How to do this on SQL 2012? My instance runs under "NT ServiceMSSQLSERVER" which I cannot find. BTW, Kaylee is my favorite.Anonymous
July 17, 2012
Thanks for this post mate! I needed to get some rich info as this one.Anonymous
July 15, 2013
Really helpful information thanks for sharingAnonymous
July 15, 2013
Thanks for the Feedback, glad you enjoyed the articleAnonymous
November 27, 2013
I have created a Connect Item to make it easier to check whether Instant File Initialization is enabled on your instance: connect.microsoft.com/.../809901Anonymous
January 19, 2014
Another reason to enable Instant File initialization is if the operation fails with Msg 3013 when attempting to restore large database backups from Azure Blob storage. Enabling Instant File Initialization appears to solve the problem.Anonymous
March 19, 2014
How about tempDB.Instant file initilization works differently for Tempdb log files.Anonymous
March 30, 2014
The comment has been removedAnonymous
April 18, 2014
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: rule30.wordpress.com/.../how-to-halve-your-database-recovery-time-in-60-seconds Cheers!Anonymous
June 17, 2015
This is a life saver!!! I found this (zero initialization) when I set up my SQL Server 2+ years ago and it makes creation/restoration of DB's (especially large ones) very FAST!!!! I had to make a copy of our 2 GIG accounting software DB and it completed in less than a minute. Not sure how long it would have taken without it, but the rep from the accounting software company told me before I created it that I should create a backup and call him back... when it completed 20 seconds later he was convinced that an error occurred as (in his words) "there was no way it could complete that quickly". According to brentozar.com it's about 85% quicker on a 1 GB file... the bigger the file the better the speed improvement..Anonymous
July 15, 2015
The comment has been removedAnonymous
January 15, 2016
This article has been very helpful....Anonymous
March 15, 2017
Excellent article. Too bad it won't print, at least under Windows 10 using IE11 nor Chrome :(