Have 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.
