What is Instant About Instant File Initialization for SQL Server?

This feature surfaced for the first time in SQL Server 2005. Though in my opinion, this is a critical feature, not many people know about the impact it has from a performance point of view. In this blog, I will show you how to enable this feature and provide a quick demo that exhibits how it drastically improves performance.

What does instant file initialization achieve? Faster creation of databases, faster data file growth (whenever it happens), and faster restores. It is critical, however, to know that this works only on the Data files and doesn’t apply to Log files. The necessary privilege is Perform Volume Maintenance Tasks when we are talking about Instant File Initialization.

I always enable this on my laptop whenever it gets refreshed; here’s how to do it: Make sure to open up SQL Server Configuration manager -> Select SQL Server Services -> SQL Server and select the Properties. Under the “Log On”, make sure to take a note of the “Account Name”. In your environment, this can be different or in a domain joined machine where the service logon was changed, these will be different values.

Before I start taking you through the process, I’ll create a database (50GB Data File and 10GB Log file). This is to check the amount of time it takes to create this database on my laptop. It took almost 2:59 mins to perform this operation. This is shown below:

For the sake of completeness, let me give you the script that I used. These timings will vary in your environment based on HDD speed.

SETSTATISTICSTIMEON

GO

USE [master]

GO

DROPDATABASEIFEXISTS [Instant-File-DB]

GO

CREATEDATABASE [Instant-File-DB]

 CONTAINMENT =NONE

ONPRIMARY

( NAME =N'Instant-File-DB',FILENAME=N'C:\Data\Instant-File-DB.mdf',

      SIZE = 51200MB , MAXSIZE =UNLIMITED, FILEGROWTH = 65536KB )

LOGON

( NAME =N'Instant-File-DB_log',FILENAME=N'C:\Data\Instant-File-DB_log.ldf',

      SIZE = 10240MB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

GO

The next logical step is to add the permission mentioned earlier. For this run, the secpol.msc on your machine. Now under “Local Policies” -> “User Rights Assignment” -> Perform volume maintenance task -> “Add user or Group…” and make sure to add the “Log On” MSSQLServer Service account as we noted in step 1. In my example, I have added “NT Service\MSSQLSERVER” user account as shown in the figure below.

After adding this permission, make sure to restart the SQL Server Service once. After this, just rerun the database creation script as shown before. The same script now runs faster at just under 23 seconds.

The improvement we got by enabling the Instant File Initialization feature is substantial; from 3 minutes to 23 seconds. Though this capability seems to deliver ultimate and mind blowing performance, in a few of the production environments I have seen this is being disabled because of security concerns. Since the file creation doesn’t quite zero the sectors, there might be a way to do DBCC PAGE and get data that was stored inside the filesystem. Though this is beyond the scope of this blog, we can surely take a look at those edge cases in a future post. Were you aware of this feature? Have you enabled it in your environments? Let me know in the comments.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.