Bluntly put, this is something that you always want to be taking advantage of.

As a Database Administrator I’m always on the look out for Performance Quick Wins(PQW). Simple tweaks and changes that I can make to the SQL Server servers in my care that deliver immediate and measurable payback. PQWs are a great way to show both new and existing customers just how lucky they are to have you administering their kit and looking after them .

In this weeks PQW we’re going to look at instant file initialization. What it is, why it’s cool and how you can use it in your environments.

How Instant File Initialization Works

When a SQL Server data file is created or grown, the new area must be indicated on the NTFS file system as being Trustworthy. A marker is set at the end of the file to indicate the point up to which NTFS can trust the data and allow it to be read. This is called the “high-water mark”.

In SQL Server 2000 and by default in later versions, this marking is performed by zeroing out the new space. SQL Server performs the zeroing process via a single thread that issues successive writes (of zeroes) to the file. The space cannot be used until the process completes, meaning that the allocation that triggered it will pause until it is done.

The magic of instant file initialization is that it enables this process to be skipped for data files only, in SQL Server 2005 and above.

Performance Benefits of Enabling Instant File Initialization

Enabling instant file initialization for SQL Server results in improved performance by reducing the amount of time it takes to:

• Create a new database
• Increase the size of an existing data file
• Restore a database backup

As an exercise, perhaps you wish to test the improvement to be had from performing a database restore for example. You could restore a copy of one of your databases (the larger the better to emphasise the point) to a test server and compare the difference in restore times between having instant file initialization enabled and not. Those clever folks over at SQLskills have of course already performed some analysis which you can find the details of in the references section below.

How to Check if Instant File Initialization is Enabled

You can check to see if SQL Server is able to use instant file initialization in your environment by creating a dummy database. If it’s enabled you will see messages in the SQL Server Error Log for the zeroing out of the log file only. If it is not enabled, you will in addition also see messages for the zeroing out of the data file.

Steps to check if instant file initialization is enabled:

1. Enable trace flag 3004
2. Enable trace flag 3605
3. Create a dummy database
4. Review the messages in the SQL Server Error Log.

USE master;
--Set Trace Flags 3004 and 3605 to On.
DBCC TRACEON(3004,-1);
DBCC TRACEON(3605,-1);
--Create a dummy database to see what output is sent to the SQL Server Error Log
CREATE DATABASE DummyDB ON  PRIMARY
(NAME = N'DummyDB', FILENAME = N'D:\DummyDB.mdf', SIZE = 2MB)
 LOG ON
( NAME = N'DummyDB_log', FILENAME = N'D:\DummyDB_log.ldf', SIZE = 1MB)
--Turn the two Trace Flags to OFF.
DBCC TRACEOFF(3004,3605,-1);
--Remove the DummyDB
DROP DATABASE DummyDB;
--Now go check the output in the SQL Server Error Log File
If enabled you will see an entry in the SQL Server Error log like similar to the one below. Notice there is no reference to the database data file.
How to Enable Instant File Initialization
In order for SQL Server to be able to perform instant file initialization the SQL Server service account must be granted the Perform Volume Maintenance Task security permission. This can be done by using the Local Security Policy Editor via:
Administrative Tools – Local Security Policy and then Local Policies – User Rights Management
NOTE: Pay close attention to what other Groups on your server have the right Perform Volume Maintenance Task. By default it is granted to the Local Administrators group and if your SQL Server service account is part of this group then it will have the permission too.
Should you have the need to add this right to the SQL Server service account, you will need to restart the SQL Server service in order for the change to take affect. (Note: Consider that if you wish to remove the security permission (perhaps when testing the Instant File Initialization feature) then you will need to reboot the server).
Rather than add the security right directly to the SQL Server service account, I personally prefer to add it to the appropriate Security Group for the relevant SQL Server instance. Doing so ensures that if you were to change the account for the SQL Server service you do not have to remember to grant the right again.
Performance Quick Wins (PQW)
The benefits of using instant file initialization are clear and you should now have the means to ensure that you can take advantage of them too. Look out for more Performance Quick Wins (PQW) posts in the future or better still subscribe to the blog feed (also available via email) to make sure you don’t miss out.
References and Further Reading:
Instant Initialization – What, Why and How?
Misconceptions around instant file initialization