One Windows setting that I think is extremely important for SQL Server usage is the “Perform volume maintenance tasks” right, which enables Windows Instant File Initialization. On a Windows Server 2003 or newer operating system, using SQL Server 2005 or newer, you can take advantage of this feature to dramatically reduce the amount of time required to create or grow a SQL Server data file. This only works on SQL Server data files, not on SQL Server log files.
Normally when you create a SQL Server data file, the operating system goes through and “zeros out” the entire file after the file is allocated. This can take quite a bit of time for a large data file, and this comes into play in several situations, including:
1. Creating a new database
2. Adding a new data file to an existing database
3. Growing an existing data file to a larger size
4. Restoring a database from a full database backup
5. Restoring a database from a full database backup to initialize a database mirror
Windows Instant File Initialization allows the operating system to skip the zeroing out process (for authorized Windows users), which makes the file allocation process nearly instantaneous even for very large files, hence the name. This can have a huge effect on how long it takes to restore a database, which could be very important in a disaster recovery situation.
You have to grant this right to the Windows account that the SQL Server Service is using. This would normally be a Windows domain account. You can do this by using the Local Group Policy Editor on the machine where SQL Server will be running. You can just type GPEDIT.MSC in a Run window, which will bring up the Local Group Policy Editor shown in Figure 1.
Then you go to Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment. Next, in the right hand portion of the dialog window, you simply right-click on “Perform volume maintenance tasks” and select Properties, and click on the Add User or Group button. Then you need to add the name of the SQL Server Service account, and click Ok. After you make this change, you need to restart the SQL Server service for the change to go into effect.
Figure 1: Using the Local Group Policy Editor to grant the “Perform volume maintenance tasks” right to the SQL Server Service account
Personally, I always use this feature with any SQL Server instance under my control. I think you should strongly consider doing the same thing.
Glenn Berry's SQL Server Performance 
Glenn,
I also use this feature on all of my database servers and find file-growth management, intended and otherwise, is much improved.
The first time we needed to use this functionality was in our Production environment during a growth spurt. A planned 64GB growth in one of our databases was needed sooner then the scheduled downtime we had set aside to perform it. Without this option enabled, the growth process would have been disruptive and necessitated shutting down must of the environment to prevent timeouts. As the option was already enabled on the server, the additional space was added within 10 seconds from when I initiated file growth.
Depending on your perspective, it may, or may not, be important to note the Windows Instant File Initialization option does not apply to SQL Server Transaction Log files as newly added space in a transaction log file must also be formatted at the time file growth occurs.
The absence of SQL Transaction Log Files from your above list implies this, but I have needed to point this out within my organization and it is an easy misconception to have.
Our Systems guys aren’t what I would call Netapp / SAN experts but they don’t believe this applies to that type of storage so my request to enable it was ignored.
Pingback: @GlennAlanBerry posts Windows Instant File Initialization and SQL Server | sqlmashup
Robert,
I did point out that this only applies to data files, not log files, in the first paragraph. It is good of you to reinforce that, because many people don’t realize that. Thanks!
Randy,
I really don’t think the storage type matters here. I have experience with both DAS and 3PAR SANs, where it behaves the same way. It is a Windows thing.
It would be easy enough to prove with a test server, to convince your admins. Good luck!
Glenn,
My apologies and thank you for pointing out the sentence. I read your blog post three times before adding that to my first reply and still missed that last sentence in the first paragraph. I guess this makes me a horrible reader.
Always enjoy your posts and look forward to the next one.
I’m curious about why the SQL Server product developers would choose to perform the ‘zeroing out process’. Could there be a downside if artifacts were left in the newly-apportioned disc space?
Mark,
The operating system is what does the zeroing out process (by default) when you allocate space for a new file. It has nothing to do with SQL Server. It is done for security reasons by the OS. Granting the right to “Perform volume maintenance tasks” to the SQL Server Service account lets SQL Server skip this step.
got it… sorry. Hadn’t had my coffee…
thanks for the tip!
Pingback: Something for the Weekend – SQL Server Links 18/02/11 | John Sansom - SQL Server DBA in the UK
Is there any reason why you would NOT use this? Are there any downsides?
There is a very slight (in my opinion) security risk, in that whatever used to be on the disk where the data files gets allocated to, could still be there (instead of getting zeroed out). I don’t know of anybody that thinks that this is a realistic threat.
Hi There. Love your blog. One security risk is when other instances are on the same server. One could run DBCC PAGE against antoher tempdb and read the content. Very remotely but still.
Am using IFI all the time
TOnny
Glen, I proved this feature usin a ISCSI SAN and it doesn’t work as good as I expected, I created a DB with 27 GB size and it taked 27 Mins I made the same test in my laptop and taked 6 mins creating the DB. Do I have to add any additional configuration at the SAN level???
You have to give the “Perform volume maintenance tasks” right to the SQL Server Service account. THen you have to restart the SQL Server Service for it to take effect. This only works for data files, not log files. If you create a database with a very large transaction log file, it will take longer.
I already did that. The behavior is strange, cause works fine in my laptop, but not in the server.