Select Page

What is Instant About Instant File Initialization for SQL Server?

Pinal Dave | | August 3, 2016

Instant File

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.

Instant File Initialization - Manager

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:

Instant File - Database

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.

Instant File - Policy

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.

Instant File - Restart

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.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS