Hi There! We are going to discuss What is Instant File Initialization. The idea here is to explain you what does Instant File Initialization (IFI) mean and how it works. How far you should explain to an Interviewer and what has changed in SQL 2022.
What is Instant File Initialization?
It is basically a Windows Operating System (OS) feature. Whenever SQL Server creates or expands a data or log file, OS validates the disk by writing zeros to the files. This process is known as Zeroing process.
The internal steps are as follows:
- Allocate the space on disk
- Writes zeros to the entire allocated portion
- Then make it available for use
It is a single threaded process and hence for large files the delay is quite visible. Instant File Initialization allows SQL Server to allocate space for data files without zeroing out the disk space first and hence reduces the wait time. Basically it skips the zeroing process.
When you are answering in an interview this much should suffice. Continue to read for more knowledge and to be able to answer any follow up questions.
Does IFI applies to Log File?
In the past, transaction log files could not use instant file initialization. However, starting with SQL Server 2022 (16.x) — across all editions — and in Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth can now use instant file initialization for growth events up to 64 MB.
By default, new databases are configured with a 64 MB autogrowth size, which means they automatically benefit from this improvement.
If the transaction log grows by more than 64 MB at a time, instant file initialization will not apply, and the growth will take longer as the space must be zero-initialized.
What are the database Activities benefited by IFI?
- Creating a New Database
- Adding data or log file to an existing database
- Increasing the existing file size manually or due to auto growth option
- Restoring a database from backup file
How IFI works if Transparent Data Encryption (TDE) is Enabled?
If TDE is enabled for a database, IFI stops working for datafile however it is allowed for transaction log growth, because of how the transaction log file grows, and the fact that the transaction log is written into in a serial fashion.
How IFI works in SQL Azure and Managed Instance?
- In Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth events up to 64 MB can benefit from instant file initialization.
- Instant file initialization is automatically used in the General Purpose and Business Critical tiers of Azure SQL Database and Azure SQL Managed Instance. It helps improve the growth of transaction log files.
- Instant file initialization isn’t configurable in Azure SQL Database and Azure SQL Managed Instance.
What kind of Environments will be benefited by IFI?
- High-availability systems
- Large databases
- Systems with frequent restores
- Systems experiencing auto-growth delays
There are very few reasons not to enable it. Ideally it should be part of SQL server build process.
You may also like to check the following:
Junior SQL Server DBA Interview Questions and Answers
SQL DBA Interview Question Answers -Part2
Junior SQL DBA Interview Questions Answers -Part3
Junior SQL DBA Interview Question Answers: Part 4
