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.
CREATE DATABASE TestFileZero
DROP DATABASE TestFileZero
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
· 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