Partager via

SQL 2016 - It Just Runs Faster: Instant File Initialization

  • Article

Database Instant File Initialization was added several SQL Server releases ago. The instant file initialization feature scales the creation and expansion (growth) of database, DATA files. The 'Manage Volume Privilege' option is off by default preventing many SQL Server installations from taking advantage of the feature.

 

SQL Server 2016 Setup provides the option to enable 'Perform Volume Maintenance Task' privilege to the SQL Server Service SID. This privilege enables instant file initialization by avoiding zeroing of data pages. For security and performance considerations see Database Instant File Initialization topic.

 

For Failover Cluster instance, each node will be configured individually for this option since the privilege belongs to local security policy. The option will show and can be enabled when adding each node.

 

 

clip_image001

 

If you are installing SQL Server using command line or a configuration file, set the SQLSVCINSTANTFILEINIT parameter to True to enable instant file initialization for SQL Server service account.

 

Example:

setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES="SQL" /INSTANCENAME="SQL2016" .. /SQLSVCINSTANTFILEINIT="True" 

 

Additionally, during SQL Server service startup the following message is written to the SQL Server error log and event log:

 

Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

 

'It Just Runs Faster' - Granting permission during SQL Server 2016 setup improves database, DATA file creation and growth capabilities.

 

Ajay Jagannathan - Principal SQL Server Program Manager

Bob Dorr - Principal SQL Server Escalation Engineer

 

Demo

 

Overview

Theis demonstration shows the difference in SQL Server 2016 database creation when 'Instant File Initialization' is enabled vs disabled.

 

Steps

  1. Use SQL Server Management Studio (SSMS) or your favorite query editor to connect to a SQL Server 2016 allowing 'Instance File Initialization'
  2. Execute the following script to create the database

 

declare @dtStart datetime = GetUTCDate()

 

create database dbTest

ON( name = 'dbTestMDF', filename = 'd:\test\dbTest16.mdf', size=10240MB)

LOG ON( name = 'dbTestLDF', filename = 'd:\test\dbTest16.ldf')

 

select datediff(ms, @dtStart, GetUTCDate()) as [Elapsed MS]

go

 

  1. Repeat the above steps after disabling 'Instant File Initialization'

Actual Scenarios

SQL Server 2016 has been vetted by a wide range of customers. The positive impact of these changes has been realized by:
 

  • Web hosted database have leveraged the feature to improve database creation
  • SQL deployments on Amazon, Rack Space and Azure are improved with reduced cost any charged I/O bandwidth
  • Development and Test Systems leverage the setup option to significantly reduce setup time
  • Restore time for disaster recovery or testing is much faster

Sample Results

Machine

56GB RAM, 8 Core Hyper-threaded enabled 2.2Ghz (Azure A7 Virtual Machine)

SQL Server

Out of the box, default installation

 

Enabled

17844ms

Disabled

309193ms

Comments

  • Anonymous
    August 13, 2016
    Thanks for taking care of it during build process !!!!