Moved from bobsql.com
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.
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.
Demo
Overview
Theis demonstration shows the difference in SQL Server 2016 database creation when ‘Instant File Initialization’ is enabled vs disabled.
Steps
- Use SQL Server Management Studio (SSMS) or your favorite query editor to connect to a SQL Server 2016 allowing ‘Instance File Initialization’
- 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
- 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 |
