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.