Every time SQL Server creates, expands, or restores a database, or adds data or log files to an existing database, it must initialize the disk space. This involves writing binary zeros (“0”) across all the bits of that space. Essentially, the database engine ensures that any previously stored data is completely overwritten before allocating disk space to the required database. However, this process noticeably consumes time and resources.
Instant file initialization (IFI) is a feature that allows skipping the stage of writing zeroes. Instead, the disk space is directly overwritten by the new data, enabling SQL Server to immediately use the allocated space. Consequently, processes become significantly faster. In this article, we will delve deeper into the instant file initialization feature, examine its benefits and potential drawbacks, and explore how to easily enable this feature.
Contents
- How to enable instant file initialization
- How instant file initialization improves SQL Server performance
- Practical test cases demonstrating the efficiency of IFI
- Security considerations and best practices
- Conclusion
How to enable instant file initialization
The instant file initialization feature is really helpful when you work with SQL Server, especially large databases. It is not turned on by default, but you can always quickly check that. For that, you can use the following query:
SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename like 'SQL Server (%';
Let’s run it in dbForge Studio for SQL Server. This is what we get.
In this case, “Y” means that instant file initialization is enabled.
Another way is to refer to SQL Server Configuration Manager. Starting from SQL Server 2019, Configuration Manager provides the information about instant file initialization directly on the Advanced tab of the Properties window.
To check these details, open SQL Server Configuration Manager and right-click the SQL Server instance you are running. Select Properties.
View the information on the Advanced tab. Here you can see that the instant file initialization feature is disabled.
As the feature is not active, you can turn it on yourself.
Note: Enabling instant file initialization requires granting the SQL Server startup account the SE_MANAGE_VOLUME_NAME permissions. This can be done by adding the necessary account to the Perform Volume Maintenance Tasks security policy (members of the Windows Administrator group can perform this action).
You can grant the required permissions as follows:
Open the Local Security Policy application (secpol.msc) and expand Local Policies on the left > User Rights Assignment.
Double-click Perform volume maintenance tasks on the right and select Add User or Group. Add the account that runs the SQL Server service.
Click Apply, close the Local Security Policy dialogs, and restart the SQL Server service to apply your changes.
There is one more method available in SQL Server 2019 and higher. You can turn on IFI from the Configuration Manager. As we illustrated earlier, this utility shows the information whether IFI is enabled or not. There you can change the property from “No” to “Yes” and restart SQL Server to apply the change.
IFI can be disabled in the same Configuration Manager by simply changing “Yes” to “No.”
How instant file initialization improves SQL Server performance
As we have already defined, instant file initialization allows us to create any data files instantly as it bypasses the stage of zeroing out the bits of the file system. Let us have a look at the most common scenarios where instant file initialization allows more effective SQL Server performance.
| Common Cases | With instant file initialization | Without instant file initialization |
| Database Creation | Faster creation | Slower creation |
| Database Restoration | Faster restoration | Slower restoration |
| File Growth | Instant allocation of disk space | Delayed allocation of disk space |
| Data File Expansion | Instant expansion of data files | Delayed expansion of data files |
| Transaction Log Growth | Instant allocation of log file space | Delayed allocation of log file space |
Therefore, the effectiveness of instant file initialization in SQL Server becomes most evident in scenarios involving large-scale data operations, such as database creation and restoration, index maintenance, bulk data loading, and ETL processes. It significantly reduces the time required for disk space allocation and thus ensures improved performance.
Practical test cases demonstrating the efficiency of IFI
Let us check and compare how instant file initialization works in practical scenarios. In our test case, we want to create a database of 10 GB in size. We’ll use dbForge Studio for SQL Server to create a new database as it allows doing that job in a visual mode, much faster and easier.
Now, let us check how long it took to create the databases. Under the disabled instant file initialization, the creation of a blank 10 GB database takes approximately 37 seconds.
When we enable instant file initialization, the same-size database is created within 3 seconds.
Though this discrepancy may seem a minor trifle, note that we handle a small test database in this example. When dealing with large databases (and the size of modern databases associated with complex tasks often amounts to many terabytes), the time saved through instant file initialization becomes significant.
As such, enabling instant file initialization proves beneficial in numerous scenarios. Nevertheless, it’s not enabled by default because it also involves some potential risks. Thus, next, we’ll delve into the security considerations we encounter when using instant file initialization in our work.
Security considerations and best practices
Instant file initialization might pose security risks. Here are some common concerns and recommendations on how to address them:
| Issue | Problem description | How to address the risk |
|---|---|---|
| Data Leakage | The use of instant file initialization may expose data remnants from previous operations that might be saved on the unallocated disk space. | Before storing sensitive data in a database, make sure you encrypt it safely and implement access controls to prevent unauthorized access at the database and server levels. |
| Privilege Escalation | As enabling instant file initialization requires granting some accounts the “Perform volume maintenance tasks” privilege, it is possible to get the privilege escalation problem. | Avoid involving overly privileged user accounts to run SQL Server services and make sure you limit all other permissions to those required for the service to work correctly. |
| Data Recovery Problem | Uninitialized disk space may complicate data recovery processes if the disk gets corrupted. | Make regular backups to recover data after any failures or disasters. Apply database monitoring services and high-availability solutions to oversee the state and performance of your SQL Server. |
| Compliance Requirements | Some regulatory requirements may demand zeroing out the disk space to ensure the deletion of any data previously stored there. | Explore regulatory compliance requirements and correlate them with your system. If zeroing-out is mandatory, you might need to adjust the rest of your workflows to compensate for the longer time taken by creating, expanding, and restoring databases. Or, if instant file initialization must be implemented in your scenarios, you might apply other security measures to ensure data safety without zeroing it out. |
Keep all your systems up-to-date by regularly patching and updating them to fix any known vulnerabilities. Secure your computers and databases with suitable hardware and software solutions to shield them from unauthorized access and mitigate potential risks. These recommendations seem obvious, but they are still the foundation of data security.
Finally, when it comes to enabling or disabling instant file initialization, make the decision based on a thorough examination of each case. While this feature can positively impact performance in many cases, there may be critical security-related or other concerns in your specific environment where it may be better to disable it.
Conclusion
Instant file initialization is beneficial in many work scenarios as it allows users to save time and resources when dealing with large databases. In this article, we examined both the advantages this feature brings and the potential risks it may pose. Also, we demonstrated how to enable and disable this feature, and how it impacts the SQL Server performance. However, it’s essential to note that, like any feature, instant file initialization isn’t a cure-all solution. You must assess your specific requirements and limitations before adopting it.
We illustrated our examples using dbForge Studio for SQL Server, a high-end alternative to SQL Server Management Studio (SSMS) that covers all the essential database development, management, and administration tasks. You can try out the fully functional free trial of dbForge Studio for SQL Server for 30 days to assess its advantages under real-life conditions.
