instant-file-initialization-performance-improvement
Contributions, Troubleshooting

Instant File Initialization – Performance Improvement

When your database files grow (either manual or automatically), does it take time and lead to performance issues?

The answer is yes! Do you find that the following operations are consuming more time than you thought required?:

  • Creating new databases that are bigger in size
  • Adding files
  • Restoring databases from backup

 

Instant file initialization help make the operations mentioned above faster. Creating bigger databases initially is always recommended to ensure that there is enough room to support the workload during peak utilizations. It also helps keep pace with the future growth.

Databases need to be grown when required in order to accommodate the running and future load, even with greater size of data and log files. Whenever database files expand, either manually or automatically, SQL Server impedes the database processing until the time it completes the operation. This slows down the running of queries during automatic or manual expansion. As a best practice you should disable the auto grow for production databases and proactively grow them manually.

Restoring a database is one of the tasks that DBAs need to do more often than not.  It can be a result of disaster recovery or refreshing a database in test and development. Other examples include restoring a database to recover a corrupt database, or restoring database in a secondary server while setting up database mirroring among other activities. The primary objective for restoration of database is to meet the recovery time objective (RTO) and complete the task in the least possible time.

What happens internally when you perform the tasks above? Let’s see.

Whenever a database is created, grown, a file is added, or a database restored, SQL Server first zeros out all the pages physically one by one in empty data and log files. Then, the database performs the requested tasks – create a database, expand the file or restore it.

Example: If a 50GB database is created and 48GB space is allocated for the data file and 2GB for log file with 500MB auto grow setting for both files, SQL Server will zero out first all 50GB disk space as seen below:

Data File
Data File
Log File
Log File

Similar things happen while growing and adding the files as well as restoring the database. When a database already exists on a destination then SQL Server skips zeroing out and restores the database which takes less time. It is the zeroing out on the file which takes a considerable amount of time.

Instant File Initialization

Instance File Initialization is a feature that allows SQL server to skip zeroing out of the data file and instantly allocate space by overwriting the new data as and when it comes over the old content. Log files are always initialized with zeros due to circular nature as SQL Server reuses the log space again and again. Additionally, a transaction log is architecturally designed in such way to let SQL Server avail benefits during a crash recovery. Instant File Initialization doesn’t work with sparse files and database having transparent data encryption (TDE) enabled.

SQL server can utilize this benefit if SE_MANAGE_VOLUME_NAME (Perform Volume Maintenance Tasks) permission granted to SQL Service account. An administrators group should already have this permission. Therefore, if a SQL Service account is in this group then SQL Server is already availing this benefit.

In order to assign this permission to a SQL service account, follow these steps:

  1. Run secpol.msc on the server.
  2. Under Security Settings on the left, go to Local Policies and then User Rights Assignment.
  3. Go to “Perform volume maintenance tasks” on right side, double click on it.
  4. Add User or Group then click Ok.
  5. Restart SQL Service.

 

This permission should be assigned to all the nodes in case of a clustered server and to all instances of SQL Server. Permission will be in effect post SQL service restart. However, if you need to revoke permission, then server reboot is a must. To disable Instant File Initialization from SQL Server, trace flag 1806 can be enabled and this will be in effect even if permission is assigned.

Traces 3004 and 3605 are used to verify its proper functioning. 3004 shows information for backup and file creation. 3605 redirect the information to the error log.

Note- These trace flags should be enabled only for testing or under the guidance of Microsoft.

Instant File Initialization- Performance Improvement
When Instance File Initialization is enabled for SQL server, an error log will not show the zeroing out of a data file. It will only show for a log file, as seen above.

Instant file initialization clearly has many benefits. However, there is one small downside.  Since the operating system only deallocates the deleted disk content, it does not delete them physically. The SQL server does not zero out data file because of the Instant File Initialization. People can use tools like Hex Editor or DBCC page or any other available tool to see the old contents over the disk which is a data security threat depending on the data. However, understanding the data using these tools will be difficult task for non DBAs or even DBAs. You may see the data as below using the hex editor.

Hex Editor Data

Conclusion

Considering a small security risk, this option is disabled by default. DBAs have added this to an instance checklist and enabled this whenever any new instance is added or while reviewing existing servers. Drastic improvement can be seen in terms of time taken in completing the task as discussed initially. If the data doesn’t require a higher level of security then this option should be enabled by default. As a useful tip, I have added this to my checklist and I would highly recommend the same for everyone.

Reference links on the topic:

SQL Skills: Instant Initialization – What, Why, and How?

Microsoft Developer Network

About The Author