To get the better performance for big environment like where database size more than 200 GB at cluster environment then we can enable Instant File Initialization (“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 we have to grant the right on all nodes. If there are multiple instances on a server or cluster, we have grant this right to each instance’s security group. I have tested the same in our test environment and it is really very good option during allocation of the large space .
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 deallocated 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 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”
- Click on “Locations” and choose either your local computer name (for local groups/standalone) or your domain (for domain groups/clusters)
- Click on “Object Types” and check “Groups”
- In “Enter the object names to select” enter your SQL Server group created by SQL setup (standalone) or your cluster domain group (for clusters).
- Choose “OK”
- Restart SQL Server