Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable SQL Server Instant File Initialization for Time Savings


By:   |   Last Updated: 2016-05-24   |   Comments (7)   |   Related Tips: More > DBA Best Practices

Problem

I have heard that enabling instant file initialization is a SQL Server best practice.  How do I enable it and what is the impact of doing so?

Solution

The status of instant file initialization is determined by permissions granted to the account used to start the SQL Server database engine service.  It is not enabled by default unless a Local Administrator account is used.  However, this is not recommended.  See this tip for additional information on SQL Server service accounts.

Before enabling SQL Server instant file initialization, let's first examine the impact of not using this feature.  As with any advice found on the internet, be sure to test these steps in a non-production environment to ensure that the full impact is understood before deploying to production.

Enable SQL Server Trace Flags for Instant File Initialization

These undocumented trace flags, 3004 and 3605, will output additional information related to creating or altering files to the SQL Server instance log.

-- Listing 1
USE [master]
GO
DBCC TRACEON (3004, 3605, -1)
GO

See these links for more information on SQL Server trace flags and the DBCC TRACEON command.

Create a Test Database

Listing 2 creates a database while instant file initialization is disabled.  The file paths and sizes may need to be modified for other environments.

-- Listing 2
CREATE DATABASE [MSSQLTips_Zero]
ON PRIMARY (NAME = N'MSSQLTipsZero_Data', 
FILENAME = N'e:\mssql\data\MSSQLTipsZero_Data.mdf', 
SIZE = 8192MB, 
FILEGROWTH = 0)       
      
LOG ON (NAME = N'MSSQLTipsZero_Log', 
FILENAME = N'c:\mssql\log\MSSQLTipsZero_Log.ldf', 
SIZE = 256MB, 
FILEGROWTH = 0)
GO

Review the SQL Server Error Log

After creating the database, the SQL Server Error log should show something similar to the following:

SQL Server Error Log noting the time to create the database and transaction log

Both the data file and transaction log file for the new database are zero-initialized.  This indicates that the operating system is filling the files with zeroes when it is created.  Also note the time stamp of the log entries.  On this test system, creating the database took approximately 2 minutes and 30 seconds.

For more information on the SQL Server Error log, check out these tips.

Enable Instant File Initialization in SQL Server

The first step is to identify which account is used to start the SQL Server database engine service.  The Services console can be used to check this.  The service display name may be different based on the version of SQL Server and whether a default or named instance is used.

SQL Server Service Account

Once the service account has been identified, additional rights must be granted to the account.  To do this, open the Local Security Policy console from Control Panel\Administrative Tools.

Windows Local Security Policy

Next, navigate to User Rights Assignment under Local Policies.

User Rights Assignment

Then, select the Perform Volume Maintenance Tasks policy and add the service account (sql_svc in this example) to the list.  As mentioned previously, the Local Administrators group should already have this permission by default.

Perform Volume Maintenance Tasks policy

Finally, restart the SQL Server service via your method of choice. This is required for the permission changes to take effect on the service account.

Re-Enable Trace Flags

Since the database engine service was restarted, the code from Listing 1 must be executed again to re-enable the SQL Server Trace Flags described above.

Create Another Test Database

Listing 3 creates a database with instant file initialization enabled.  The location and size of the files are identical to the database created in Listing 2.

Again, be sure to update the file paths and sizes as needed.

-- Listing 3
CREATE DATABASE [MSSQLTips_Init]
ON PRIMARY (NAME = N'MSSQLTipsInit_Data', 
FILENAME = N'e:\mssql\data\MSSQLTipsInit_Data.mdf', 
SIZE = 8192MB, 
FILEGROWTH = 0)
      
LOG ON (NAME = N'MSSQLTipsInit_Log', 
FILENAME = N'c:\mssql\log\MSSQLTipsInit_Log.ldf', 
SIZE = 256MB, 
FILEGROWTH = 0)
GO

Review the SQL Server Error Log Again

SQL Server Error Log noting the time to create the database and transaction log

Only the transaction log file is zero-initialized when instant file initialization is enabled.  For additional information on why instant file initialization does not apply to transaction log files, see this blog post. Also, the time to create the database on the same test system decreased from 2 minutes 30 seconds to 2 seconds.

Conclusion

This tip shows one way to enable instant file initialization for SQL Server along with the I/O and time savings that can be realized by doing so.  The example was created using SQL Server 2014 SP1 on Windows 8.1, but should be relevant to any currently-supported SQL Server configuration.  Be sure to fully test prior to implementing in production.

Next Steps


Last Updated: 2016-05-24


next webcast button


next tip button



About the author
MSSQLTips author Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 31, 2018 - 1:31:20 PM - Mike Back To Top

 Now you can enable IFI during install! This was added at SS 2016. See:

 https://blogs.msdn.microsoft.com/psssql/2016/03/25/sql-2016-it-just-runs-faster-instant-file-initialization/

Cheers!


Wednesday, June 01, 2016 - 10:05:56 AM - Mike Back To Top

 

Nirav - I assume that it would be compatible with tempdb.  But you could prove this out by following the steps in the tip.  Just enable the trace flags as outlined above and growing tempdb in the same way that took too long.  There should be a message in the SQL Server instance log indicating whether or not zero-initialization took place.  That being said, there are many other factors that could influence this, such as the speed of the disk subsystem, the load on teampdb at the time it was expanded, etc.


Wednesday, June 01, 2016 - 3:26:25 AM - Nirav Gajjar Back To Top

 Is IFI is compatible with tempdb growth? Recently I need to increase my tempdb tby 40 Gb and took 30+ minutes to complete. The user already has already added to "Perform Value Maintenance Task".

 


Tuesday, May 24, 2016 - 1:28:01 PM - Mike Back To Top

 Ann - It should apply to adding new data files to an existing database or growing existing data files in an existing database.


Tuesday, May 24, 2016 - 12:26:56 PM - Ann Back To Top

Is it only applied to the time when you create a database?

 
 Thanks


Tuesday, May 24, 2016 - 10:27:50 AM - Mike Back To Top

 Sajal - I am not aware of any negative impact related to enabling instant file initialization.


Tuesday, May 24, 2016 - 1:20:14 AM - Sajal Bagchi Back To Top

 

I wonder if there is any drawback associated with instant fie initialization ?


Learn more about SQL Server tools