This article gives an overview of Instant File Initialization and its benefits for SQL Server database creation, restoration and file growth.
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.
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
Go to Security settings -> Local policies -> 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.
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.
select instant_file_initialization_enabled ,* from sys.dm_server_services
where servicename like 'SQL Server%'
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.
CREATE DATABASE DB_withoutIFI ON PRIMARY
(NAME = N'DB_withoutIFI', FILENAME = N'E:\TestDB\DB_withoutIFI.mdf', SIZE = 70GB)
( NAME = N'DB_withoutIFI_log', FILENAME = N'E:\TestDB\DB_withoutIFI.ldf', SIZE = 1GB)
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.
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.
DBCC PAGE (DB_withoutIFI, 1, 1000, 2)
In the following screenshot, we can see all zero bytes for a database page.
We will see high wait type PREEMPTIVE_OS_WRITEFILEGATHER while the zeroing process is in progress.
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.
Execute the DBCC page command, and we can see garbage data on the page. Previously, it returned a page with zero bytes.
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.
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
- 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
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.
- DTU and vCore based models for Azure SQL Databases - September 27, 2021
- Custom Azure Policy definitions for Azure SQL Databases backup retention periods compliance - September 22, 2021
- Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server - September 16, 2021