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.
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 consuming more time than you thought it required?
- Creating new databases that are bigger in size
- Adding files
- Restoring databases from backup
In this blog we will learn about instant file initialization which will help to 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, even with greater size of data and log files, need to be grown as and when required in order to accommodate the running and future load. 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, which can be a result of disaster recovery, refreshing a database in test and development, 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 and then 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:
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 restore the database which takes lesser time. Hence, it is the zeroing out on the file which takes 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 the below 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 can be used to verify its proper functioning. 3004 shows information for backup and file creation and 3605 redirect the information to the error log.
NOTE- These trace flags should be enabled only for testing or under the guidance of Microsoft.
When Instance File Initialization is enabled for SQL server then an error log will not show the zeroing out of a data file but show only for a log file as below.
Enough with the benefits! Let us see one small downside of this. Since the operating system only deallocates the deleted disk content, it does not delete them physically and 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.
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 slightest security concern can be overlooked and data doesn’t require this much level of security then this option should be enabled by default. As a useful tip, I have added to my checklist and I recommend the same for everyone.
Reference links on the topic: