page contents

About the Post

Author Information

BEST PRACTICE: SQL Server 2005 to present: Instant Initialization for database files

This is a well-known and often mentioned best practice that I have recently found out that is not implemented much. I was even surprised to see that most installer packages to even include Microsoft’s SQL Server install do not set this feature up. Instant Initialization has been around since Windows XP and SQL Server has taken advantage of it since SQL Server 2005, but it is not built-in or even turned on by default. In fact, the installer even with SQL Server 2012 does not even mention it. Which I am hoping by now, you are wondering.. what is he taking about?

I am taking about granting the account for the SQL Server service to “Perform volume maintenance tasks” with the local security policy. Allowing this access will allow SQL Server to take full advantage of the Instant Initialization feature of NTFS. This will reduce the time taken when initializing files during database creation, restores and auto-growth events. For example, if you have a new 20 GB Database file on 15k RPM drives and it will take the following:

Performance Test with Zero Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 14:02 minutes
ALTER DATABASE BY 10 GB = 7:01 minutes
RESTORE 30 GB DATABASE (EMPTY Backup) = 21:07 minutes
RESTORE 30 GB DATABASE (11GB Backup) = 38:28 minutes

Performance Test with Instant Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 1.3 seconds
ALTER DATABASE BY 10 GB = .4 seconds
RESTORE 30 GB DATABASE (EMPTY Backup) = 5 seconds
RESTORE 30 GB DATABASE (11GB Backup) = 19:42 minutes

For more detailed information, please see the following article on Kimberly Tripp’s blog. But as you can clearly see you are saving a considerable amount of time.

I am going to show you how simple it is to set up and more importantly, show you the low risk associated in allowing this access.

First you need to see the service that starts your SQL Server as seen below from either the services applet in the Control Panel or the preferred method SQL Server Configuration Manager:

SIDE NOTE: Managing the service account password is a lot easier in SQL Server 2005 with the SQL Server Configuration Manager (SQL-CM). The SQL-CM allows you to change the password to a service without an active connection (meaning even if the service isn’t started) and it invalidates the login token so that password changes don’t require a restart of the service. SQL-CM also has a command-line interface and is scriptable with WMI. The WMI Provider allows server settings, client and server network protocols, and aliases to be scripted through the WMI Provider by means of simple VBScript code (or by using the command-line tool). What you could end up doing is creating a script that changes the password of your services on all of your servers (for example, when a password policy is enforced that requires that the passwords of service accounts be changed). 

Quote is taken from same blog mentioned above on sqlskills.com.

To use instant initialization, your SQL Server service must be running with an account that has the required privilege. You can modify it by typing gpedit.msc in the run button:

Then you will need to navigate to Computer Configuration, Windows Settings, Security Settings, User rights and then Perform volume maintenace tasks as seen below:

As you can see, the default setting is only allowing this task for Administrators of the box:

Click on the Add User or Group radio button and then add your user:

After click ok, you now notice your account is listed and once granted, SQL Server automatically uses instant initialization:

Tags: , , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.