Rajendra Gupta
SQL Server 2016 setup

An Overview of Instant File Initialization in SQL Server

September 25, 2019 by

This article gives an overview of Instant File Initialization and its benefits for SQL Server database creation, restoration and file growth.

Introduction

As a database administrator, you must ensure database availability, performance, recovery from disaster. You might have faced a scenario in that new database creation, or database restoration takes a long time. We perform various optimization techniques to ensure excellent performance of instance and database. Instant file initialization is one of the important features that enable you to reduce database creation, data file growth and minimize the database restoration, especially for large databases. SQL Server 2016 onwards, we see an option to grant volume maintenance task permissions to the SQL Server database engine.

SQL Server 2016 setup

You might ask a question or two here.

  • What is Grant volume maintenance task permission in SQL Server?
  • What benefits do we get if we allow this permission to SQL Server?
  • How do we enable it and track it?
  • Is It possible to disable it a temporary basis for any specific?

We will explore the answers to all these questions in this article. Let’s begin with the introduction of volume maintenance task permissions.

As a database best practice, you should pre-allocate space for the data and log files. It avoids unnecessary auto-growth events that might cause performance issues in SQL Server.

Suppose you are performing any of the following tasks:

  • Creating a database
  • Adding a data file to an existing database
  • Growing a data file
  • Database backup and restore

Let’s say we need to create a database with the following configuration:

  • Create a new database named TestSQLdatabase
  • Initial size of data file should be 40 GB
  • Auto growth of 1 GB
  • No volume maintenance task permissions to SQL Service account

Once we issue create database command, it allocates the required space using internal Windows API functions and initializes every byte of this with zero values (0x0). This process is known as zeroing.

Suppose we have the following storage byte 1100 1010 before SQL Server occupies this space and starts zeroing process. After the zeroing process, it converts byte into 0000 0000. Therefore, we cannot read old data in any case. It is a good thing, but you need to bear the cost of it.

SQL Server provides a feature to skip this zeroing process using this feature. We enable by providing Volume Maintenance task permissions to the SQL Server service account. It takes time to finish this zeroing process. It prevents SQL Server to access old data present in the disk. For a smaller data file or growth, it might take less time, however during large operations, and it takes considerable time.

Starting from SQL Server 2016, you get the option to grant volume performance maintenance permissions to the SQL service account as shown in the image above.

If you haven’t enabled it during installation, you can still do it using the following methods.

  • In the Windows search, look for Local Security Policy

    Local security policy

  • Go to Security settings -> Local policies -> User rights assignment

    user rights assignment

On the right side, you can see policies applicable to the user rights assignment.

In the policies, look for Perform Volume Maintenance Tasks and add SQL Service account in this policy.

Perform Volume Maintenance Tasks

If you are not sure about the SQL Service account, go to SQL Server Configuration Manager and check the service account under that SQL service is running.

We can use DMV sys.dm_server_services to check whether the instant file initialization is enabled on a connected instance or not.

It returns Y that shows this feature is enabled on the connected instance.

Before we move further and explore the benefits of it. SQL Server uses Windows API to call for storage operations specified above. It consists of the following operations.

  • CreateFile: It creates an empty file
  • Valid data length location (VDL) – It sets the logical end of a data file. Windows uses SetFileValidData function to perform this task
  • End of File (EOF) – It is the physical file size of a data file in SQL Server. It uses SetEndOfFile function to perform this task

Let’s look at the benefit of Instant file initialization in SQL Server.

Creating a Database

Suppose we require to create a database with primary data file initial size 70 GB. First, enable the trace flag 3004 and 3605 to capture additional information in the error log for the zeroing process.

  • Trace Flag 2004: It shows file creation and backup information
  • Trace flag 3603: it redirects the output to the SQL Server error log

Execute the command to create a database in SQL Server.

In my environment, it took 2 minutes 34 seconds to create a blank database with the above query. Now, look at the error log, and you can see it perform zeroing for the data file and log files. It took 151713ms for the zeroing of a data file.

SQL Server error log

Let’s go a step further and view the internal data of a page using DBCC Page command. We need to enable trace flag 3604 before executing DBCC PAGE command.

In the following screenshot, we can see all zero bytes for a database page.

DBCC PAGE

We will see high wait type PREEMPTIVE_OS_WRITEFILEGATHER while the zeroing process is in progress.

PREEMPTIVE_OS_WRITEFILEGATHER

Let’s create another database with the instant file initialization feature enable. It creates the database immediately, and in the error logs, you do not see any zeroing for the data files.

error logs

Execute the DBCC page command, and we can see garbage data on the page. Previously, it returned a page with zero bytes.

DBCC PAGE output

Similarly, we can test the scenario for backup restoration and file growth as well. Once we restore a database, it performs the following tasks.

  • Creates an empty file and zero initializes it
  • Copy the data from the backup media to the allocated space in the data files

If we enable the instant file initialization on SQL instance, it avoids zero initialization steps, and restoration is comparatively fast. In the following screenshot, you can see the following.

  • Database restoration without instant file initialization took approx. 26 seconds
  • Database restoration with instant file initialization took approx. 21 seconds

You might see it is a minimal improvement but think in terms of an extensive database. You might have a database in TB’s, and restoration takes a very long time. You can improve restoration performance if the destination instance is enabled with instant file initialization.

error logs for instant file initialization

Instant File Initialization Exceptions

  • Trace flag 1806 disabled the instant file initialization behavior even if we have provided permissions to perform a volume maintenance task

    You can enable it using the DBCC traceon(1806,-1) command

    We should enable it only if we require to do so. We can disable this trace flag using the DBCC TRACEOFF(1806) command

  • Once we run the DBCC CHECKDB command on a database, SQL Server creates an internal database snapshot and initializes it with zero. It does not any impact of the enabled instant file initialization feature

    In the following screenshot, we can see that zeroing once we execute the CHECKDB command to check database consistency

    Instant File Initialization Exceptions

  • If we create a database snapshot as well, it zeroes initialize the data files. We can see a similar kind of event in the error logs as shown above
  • Instant file initialization does not work with the Transparent Data Encryption enabled database. Once we configure TDE and try to expand data file, it uses the zeroing process
  • Instant file initialization does not have any impact on log files. Log files are always zero-initialized

Conclusion

Instant file initialization provides better performance for the data file creation, growth, and database restoration. We can read the garbage data because SQL Server does not follow the zeroing process for the data files. There might be some security concerns about the garbage data; however, most of the DBA uses this approach. You should understand the security risk and make a decision based on the requirement.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Server Setup – Instant File Initialization (IFI)
  2. Overview of the Shrink TempDB database in SQL Server
  3. How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File
  4. Changing SQL Dump file locations in SQL Server on Linux
  5. How to import a flat file into a SQL Server database using the Import Flat File wizard
168 Views