SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

GUI Enhancement on Instant File Initialization (IFI) in SQL Server 201

By Dharmendra Keshari,

SQL Server 2016 loaded with enhancements and features. Recently, they added the ability to enable the Instant File Initialization (IFI) during the installation of SQL Server 2016 (CTP 3.0).

Installation of SQL Server 2016 with Instant File Initialization enabled option

From SQL Server 2016 (CTP 3.0) on, you can enable IFI by just clicking the option “Grant Perform Volume Maintenance Task Privilege to SQL Server Database Engine Service” on the tab “Server configuration” during installation. You can see this in the following image:

Background of Instant File Initialization

Whenever SQL Server needs to allocate disk space for operations like creating/restoring a database or growing data/log files, it has to first zero out the newly allocated space. Fundamentally, the newly allocated disk pages, which have been allocated for the operations, will be filled with zeroes. In most cases, zeroing out newly allocated disk space may not be useful.

By enabling Instant File Initialization, SQL Server skips the zero-writing step for the data file. The transaction log file still needs to be zeroed out because of data integrity and security reasons.

Prior to SQL Server 2016 (CTP 3.0), if you want to enable this feature, you have to follow below mentioned steps;

  • Open the Local Security Policy application.
  • Expand Local Policies, click on User Rights Assignment.
  • Now double click on “Perform Volume Maintenance Tasks”.
  • Go to Add User or Group and add Service Account.
  • Click on Apply

You also needed to restart the SQL Server service in order for SQL Server to start using Instant File Initialization

Benefits of enabling Instant File Initialization

The larger the database size growth operation, the more obvious the performance improvement can be seen with Instant File Initialization. SQLSkills team has done a great performance testing on Zero Initialization VS Instant Initialization where you can clearly see that ALTER DATABASE by 10 GB and Restore of 30 GB database can take minutes to initialize if you have not enabled the IFI.

PS: Keep in mind that there is a slight security risk when you enable IFI, but it’s up to the company to determine if the benefits overshadow the risks.

 
Total article views: 1153 | Views in the last 30 days: 2
 
Related Articles
BLOG

Enabling Instant File Initialization

Found a couple good walkthroughs on enabling instant file initialization. However, I'm becoming more...

BLOG

Enabling Instant File Initialization

Found a couple good walkthroughs on enabling instant file initialization. However, I’m becoming more...

ARTICLE

SQL Server Instant File Initialization (PQW)

In this article we’re going to look at instant file initialization. What it is, why it’s cool and ho...

BLOG

Instant File Initialization in SQL Server on Linux

Earlier this week Ned Otter (@NedOtter) brought up a question about Instant File Initialization on S...

BLOG

Verifying Instant File Initialization

Ran into a few issues verifying instant file initialization. I was trying to ensure that file initia...

 
Contribute