Configuring Windows Instant File Initialization for SQL Server

After Reading Brad McGehee’s ebook on ‘How to Optimize tempdb Performance’, I came across a performance enhancement which is available to all databases called Instant File Initialization. This feature is available on Windows Server 2003 onwards, using SQL Server 2005 onwards to significatly reduce the amount of time required to create or grow a sql server data file.

Note: This is only applicable for SQL Server Data Files and Not Log Files. The reason being that Log files have to be completely Zeroed out before being written to disk.

The problem…

When you create a SQL Server data file, the operating system has to go through the file and ‘Zero out’ the entire file after it is allocated for one of the following operations:

  • Create a database.
  • Add files, log or data, to an existing database.
  • Increase the size of an existing file (including autogrow operations).
  • Restore a database or filegroup.

This can take a quite a bit of time for a very large file which can become critical in disaster recovery and restore operations.

Windows Instant file initialization removes the need to Zero out the file when it is allocated making the process almost instantaneous. The operating system just allocates the disk space, but the contents of the file is actually what is originally on the disk.

Making use of this feature…

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.

Grant the right to the windows account that the SQL Server Service is running under, by doing the following:

  1. Open Group Policy Editor (or run gpedit.msc)
  2. Expand out the following – Computer Configuration –> Windows Settings –> Security Settings –> Local Policies
  3. Highlight User Rights Assignment
  4. In right hand pane, go to the properties for ‘Perform Volume Maintenance tasks’ and add the service account.
  5. The change will only take affect after the SQL Server instance has been restarted.

image

MSDN Article here

See the difference…

Test it by trying to restore a large database (>50gb) on a SQL Server 2005 instance

  • On a test server, without the permission, restore the database backup and measure the restore time
  • Drop the restored database and stop the SQL Server service
  • Grant the “Perform Volume Maintenance Task” permission on the SQL Server service account and restart the service
  • Restore the database backup again and measure the restore time
Advertisements

2 Responses to Configuring Windows Instant File Initialization for SQL Server

  1. Orlando Colamatteo says:

    One thing worth mentioning that you glossed over is that Log files cannot be instantly initialized…only data files can. This is true in SQL 2008 R2 and as of now is what the Denali docs say as well so it’s a good idea to still monitor your log file sizes and plan manual grow operations during a maintenance window.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: