Windows Instant File Initialization and SQL Server

Hi SQL Server Folks,

Recently I was working on very huge database, approximately size was ~2 T B for that db. I faced a problem that when the data file was growing for a db then it was taking more time. The reason is when we we create any data file OR when data files grows then operating system do not assign the space directly to that data file. First it will allocate desired space then that space is filled by Zero’s and then this data file can be used by any operation that user is performing.

But we can reduce that time through a feature called 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 dramaticallyreduce 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 file to an existing database.
  3. Growing an existing file to a larger database.
  4. Restoring a database from full database backup.
  5. Restoring a database from full database backup to intilize 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. 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 as shown in below Figure.

Now go on to the left hand side of this screen and 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.

After done this definitely your SQL Server Performance will be better for larger db.

Hope it will help some one. Thanks all to stay tune here.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: