Search
Close this search box.

Instant file Initialization and performance impact

Intiant File InitializationHave you ever wonder why some of the file operations in SQL Server is slow? For example, when a database is being restored you felt like it is slow! Here in this post, let’s discuss one of the reasons why there is a slowness – Instant file initialization and performance impact. Let’s first understand what is Instant File Initialization (IFI).

 

 

 

What is Instant File Initialization(IFI):  It is the permission which will help SQL Server to perform some operations faster. Basically, when a new file is created, new database is created or a database is altered to adjust the existing size of the database it need some time to perform this operation. This operation has been made faster, this feature is introduced in SQL Server 2005. Earlier, when a file is created, added or modified it will fill out the space with zero. Imagine a scenario where one want to  increase the database file with 100 gigs or creating a new database with 100 gig of size. Without enabling an SQL Server account for IFI it will take longer time as it will first fill out the file with zeros. With this new feature IFI it skips this process and the new file or new database is instantly available for one to perform further operations.

What kind of operations are impacted if IFI is not granted?

  • Create Database
  • Adding file to an existing database
  • Modifying the size of existing files
  • Restoring a database or file

Now since we have understand Instant File Initialization and performance impact it make, let’s see how to make these processes faster – one will need to enable Perform volume maintenance task right to the SQL Server service account.  This can be done invoking the Local Security Policy > Local Policies > User Right Management. So to make the above mentioned operations can be made faster if one enable IFI and this is how Instance file initialization can have performance impact which can be resolved and improve performance by providing proper permissions.

Enhancement in SQL Server 2016:

With the release of SQL Server 2016, during the time of setup where one is configuring the Service accounts and start up types for services can select an option which will grant Perform Volume Maintenance Task to SQL Server service account. SQLSkills have a very good example for this very topic that one would like to read as a reference.

photo credit: Ezu Slowness via photopin (license)

Leave a Reply

Your email address will not be published. Required fields are marked *

Picture of Hemantgiri Goswami

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.