Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Instant File Initialization (PQW)

By John Sansom,

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 instance and looking after them.

In this 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.

The code to do this is:

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.

References:

"I hope you enjoyed this article. You can find even more SQL content on my blog SQL Server DBA in the UK where I post each week about working with SQL Server and Professional Development for the DBA. Thanks for reading!"

Total article views: 4881 | Views in the last 30 days: 7
 
Related Articles
BLOG

Turn on Instant File Initialization for SQL Server Performance

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is...

BLOG

Turn on Instant File Initialization for SQL Server Performance – With Video Demo

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is...

ARTICLE

Instant File Initialization and restores

Instant file initialization also has its advantages when you restore a SQLServer database. This arti...

ARTICLE

Instant File Initialization

This article describes a way to speed up various file operations performed by SQL Server.

BLOG

Most SQL Server DBAs Still Don’t Know About Instant File Initialization

In my most recent poll, I asked DBAs if they have turned on instant file initialization on their S...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones