If SQL Server needs to allocate space, first it fills the space it needs with zeros. Examples of when it needs to allocate space are creating/restoring a database, growing/allocating data and log files, and finally the troublesome auto growth operation. However, most of these slowdowns/long running actions can be improved by enabling instant file initialization. I say most because the one case where this is required is on log file growth. However, even when Instant file initialization has been enabled the log file will be zeroed out before the space is usable for SQL Server.
Instant file initialization skips the zero-writing step and allows SQL Server to use the allocated space immediately. The main benefit is that during restore or file growth functions it will be considerably faster and reduce downtime in case of an emergency.
As mentioned above, in many cases this a good idea, however, this is not the case for all scenarios. This is because when initializing a file on the OS level, the operating system will try to format those blocks to be zeros. This is like a hard format which is done when first inserting disks. The reason the OS does this is because it prevents people from reading data that was deleted by the user but still existed on the disk. When enabling IFI, this is basically switching the SQL Server disk allocations to lazy format, which will zero out the disk only when it needs to write to the disk. I will add that this can be a security issue but it is still a common and best practice to enable this. Make sure to discuss internally before making this choice for your organization as it could be a problem depending on what you store.
However, I will also add that most of the time the potential security concern is eliminated because most enterprises are using SAN technology to store their databases. In the article about instant file initialization, Microsoft mentions these concerns and mitigates it by stating “If the database files are hosted on a storage area network, it is also possible that the storage area network always presents new pages as pre-initialized, and having the operating system re-initialize the pages might be unnecessary overhead.” I invite you all to read the article on the MSDN located here.
Even if your business is not using a SAN, it is very common to turn Instant File Initialization on. Many shops consider the increased performance benefit to far outweigh the small security risk, but you must weigh the cost and benefits within your own environment.
This all sounds great, right? So how do you go about enabling this on your SQL machine. Well, before we touch on this, I will add, that this is only necessary if you are not running your sql server service account as local system or as a member of the administrator’s group. By default, Microsoft has the permissions granted to these groups to already have Instant file initialization on. When reviewing why, I found Microsoft documentation on the perform volume maintenance tasks policy that states “This policy setting determines which users can perform volume or disk management tasks, such as defragmenting an existing volume, creating or removing volumes, and running the Disk Cleanup tool.” Obviously, this makes sense since those are core functions of any OS.
You can doublecheck this by running the following SQL query.
|
1 2 3 4 5 |
SELECT servicename as Service_Display_Name, service_account as Service_Account, instant_file_initialization_enabled as Instant_File_Initialization_Enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%' |
If your query returns that you do not have this enabled, you can run through the following steps:
As a side note, always run changes through a change management process or on a development environment prior.
Prerequisite
- Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME.
Changing the SQL Server Service account
- On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.
- In SQL Server Configuration Manager, click SQL Server Services.
- In the details pane, right-click the name of the SQL Server instance for which you want to change the service startup account, and then click Properties.
- In the SQL Server Properties dialog box, click the Log On tab, and select a Log on as account type.
-
After selecting the new service startup account, click OK.
- A message box asks whether you want to restart the SQL Server service.
- Click Yes, and then close SQL Server Configuration Manager.
Enabling Instant File Initialization
To grant an account the Perform volume maintenance tasks permission: This option is housed in the local security policies under > local policies > users rights assignments. You will the add your user to the perform volume maintenances tasks and select apply.
Similar steps can be followed in group policy, however, depending on your structure for your group policy it can get a bit messy.
- Open Group Policy Management
- In the left pane, expand your Domain, and right click Group Policy Objects
- Create a new Policy with your domain’s naming convention.
- Once created, edit the policy by right clicking and selecting edit
- Navigate to Computer Configuration -> Polices -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignments
- Scroll down to the Perform Volume Maintenance Tasks
-
Find the Domain account you use for your SQL Server Service
- In the case of multiple SQL servers with different accounts, create a group for them prior, if you have not already done so.
- Once added, apply the policy to the appropriate OU
Testing restore times with Adventureworks
To test some of the benefits we are going to try to compare restoring the adventureworks2017 database with and without Instant file initialization. A disclaimer, with such a small database (97MB .bak file) you will likely see a more drastic result with larger databases.
To start, we will restore the Adventureworks2017 database with instant file initialization enabled, following this we created a local user account, creatively named SQLServer with it disabled (shown below)
Using the filtering feature for the SQL Server logs, we can get the speed of the restores.
As you can see the second test, we saw a 25% difference in speed for restoring a database, which can be job saving in a DR scenario.
FAQs
When should I enable instant file initialization?
Almost always, there is almost no security risk but there are tremendous benefits.
How can I tell Instant file initialization is enabled?
This can be seen by running the query below.
How to enable instant file initialization?
Well, you might not need to but a policy can be pushed locally or through a group policy.
My curious nature leads to me learn about different methodologies for accomplishing tasks more efficiently without compromising on the quality. This does tend to lead one down the rabbit hole, but it often ends in valuable experience that I enjoy sharing with anyone willing to take the time. You can find me on LinkedIn
- Preparing for SQL Server Database Corruption; initial reaction and analysis - February 6, 2019
- Considerations and concerns when setting up Database mail for SQL Server - August 31, 2018
- SQL Server Setup – Instant File Initialization (IFI) - August 31, 2018