Improve Database Allocations through Instant File Initialization
December 30, 2010
As a Database Administrator, you need to consider setting up your environment to do instant file initialization. This article explains what instant file initialization is, why it is important, and shows you how to set it up. Additionally, we demonstrate how not setting it up can adversely affect your database performance.
As a Database Administrator, you need to set up your SQL Server environment as optimally as possible. You need to make sure you configure your disk drives with the proper raid level and partition alignment to optimize your disk level I/O. Additionally you need to consider setting up your environment to do instant file initialization. In this article, I will explain what instant file initialization is, why it is important, and show you how to set it up. Additionally I will demonstrate how not setting it up can adversely affect your database performance.
What is Instant File Initialization
When a SQL Server database is created, or expanded SQL Server wants to initialize the file. The file is initialized by filling it with zeroes 0, that is unless you have turned on instant file initialization. If you have turned on instant file initialization the process of writing zeroes to the data pages is by-passed. Skipping this file initialization process can save a lot of time, especially for those extremely large databases files.
Not only does SQL Server require file initialization when you create or expand a database it also requires it when you restore a database. The first step of the restore process is to allocate the required space for the database and initialize it with zeroes. If you have instant file initialization turned on, restoring a database is faster because file initialization is not performed when a database backup is restored.
Keep in mind, Instant file initialization is only available in Window XP SP2 and Windows Server 2003 and above. Therefore, if you are on old versions of the Windows OS you might not be able to implement instant file initialization.
Determining Whether or Not you have Instant File Initialization Turned On
If you already have an installation of SQL Server, it does not mean you cannot turn on instant file initialization. You can turn it on and off at any time. Prior to going through the steps, to turn on instant file initialization, you should first check to verify whether you already have it enabled. There are a number of ways to find out if instant file initialization is turned on. I originally learned about these methods from reading from Paul Randals blog at www.sqlskills.com.
The first method uses the whoami.exe with the /priv option. The priv option displays the user security permissions. If the account running SQL Server has been granted the SeManagVolumePrivileges privilege then instant file initialization is turned on. Here is a script that you can run from the query window within SQL Server Management Studio to determine if instant file initialization is turned on.
USE master GO -- Turn on advanced options EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; -- Create temp table to hold current configuration of xp_cmdshell CREATE TABLE #CurrentConfig (name VARCHAR(256), minimum INT, maximum INT, config_value INT, run_value INT); -- Get current configuration value of xp_cmdshell INSERT INTO #CurrentConfig EXEC sp_configure 'xp_cmdshell'; -- Get config_value for xp_cmdshell DECLARE @config_value INT; SELECT @config_value = config_value FROM #CurrentConfig; -- Turn on xp_cmdshell if not already enabled IF @config_value = 0 EXEC sp_configure 'xp_cmdshell', 1; -- force xp_cmdshell to be enabled RECONFIGURE WITH OVERRIDE; -- create table to hold xp_cmdshell output CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000)); -- run whoami command via xp_cmdshell INSERT INTO #xp_cmdshell_output EXEC ('xp_cmdshell ''whoami /priv'''); -- check to see if instant file initialization is on and then print -- appropriate error message IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE '%SeManageVolumePrivilege%Enabled%') PRINT 'Instant Initialization enabled' ELSE PRINT 'Instant Initialization disabled'; -- Reconfigure xp_cmdshell as it originally was EXEC sp_configure 'xp_cmdshell', @config_value; RECONFIGURE WITH OVERRIDE; -- turn off advanced option EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE; -- cleanup DROP TABLE #xp_cmdshell_output; DROP TABLE #CurrentConfig;
If you look at this code, you will see this method uses xp_cmdshell to execute the whoami exe. The output of the whoami exe is stored in a table. Then that table is searched to see if the SeManageVolumePrivilege is Enabled. Depending on whether or not this privilege is enabled or not then the message Instant Initialization enabled or Instant Initialization disabled is displayed.
In some environments, it may be inappropriate to turn on xp_cmdshell, even if it is just for a few moments, as this script does. Therefore, there is an alternative method for determining if instant file initialization is turned on. The alternative method is to enable trace flag 3004 and 3605. You can enable these trace flags by running the following T-SQL script:
DBCC TRACEON(3004, 3605, -1); GO
Once these trace flags are enabled, create a DUMMY database, and then review the ERRORLOG file. In the ERRORLOG file, you should see some messages like this:
2010-11-21 15:40:19.83 spid55 Zeroing C:\DATA\DUMMY.mdf from page 0 to 256 (0x0 to 0x200000) 2010-11-21 15:40:19.86 spid55 Zeroing completed on C:\DATA\DUMMY.mdf 2010-11-21 15:40:19.88 spid55 Zeroing C:\LOG\DUMMY_log.ldf from page 0 to 128 (0x0 to 0x100000) 2010-11-21 15:40:19.89 spid55 Zeroing completed on C:\LOG\DUMMY_log.ldf
Here you can see the message Zeroing related to placing zeroes (0) in the mdf and ldf files. If you see these messages against the data files then you know that instant file initialization is not turned on. Once you have verified the status of your instant file initialization settings using the trace flag method, make sure you turn off the trace flags by running this script:
DBCC TRACEOFF(3004, 3605, -1); GO
If you do not turn off these trace flags you will get a lot of additional messages posted to the ERRORLOG file.
Now that you know how to determine whether your system has instant file initialization enabled, or disabled, let's discuss what it takes to enable instant file initialization.
Configuring Your Machine for Instant File Initialization
Remember we used the whoami exe to determine what priviledges the SQL Server services account has in Windows. If the SQL Server Service account had SeManageVolumePrivilege enabled that meant instant file initialization was turned on. Therefore, we just to need to enable this security policy for the account that SQL Service runs under. By enabling this policy, we will allow SQL Server to by-pass the long zeroing process when it creates new databases, restores databases, or increases the size of a database data file.
This policy can be enabled by adding the SQL Server Service account to the Perform Volume Maintenance Tasks security policy. By default, the Administrators group has this permission. To change the policy, first click on the Start button, followed by clicking on, or hovering over the Administrative Tools menu item, and then clicking on the Local Security Policies item as shown in the following screen shot:
When you do this, the following screen will be displayed:
Here you will click on Local Policies and then in the right pane double click on User Rights Assignment. In the right pane, all the user rights will be displayed. Scroll down in the list until you find the Perform Volume Maintenance Tasks item as in the screen shot below:
As you can see, only the Administrators group has this right on my machine. Double clicking on this item will display a screen like below where you can add the SQL Server Service account:
Click on the Add User or Group button, and then add your
SQL Server Service account.
Once you have added your SQL Service account you will need to restart SQL Server in order to complete the process of turning on Instant File Initialization.
Once you have restarted SQL Server you can verify that you have Instant File Initialization now turned on by using the trace flag method above and creating a new DUMMY2 database. Now that Instant File Initialization is turned on, you will only see these messages in the ERRORLOG file:
2010-11-21 17:27:41.45 spid52 Zeroing C:\LOG\DUMMY2_log.ldf from page 0 to 128 (0x0 to 0x100000) 2010-11-21 17:27:41.48 spid52 Zeroing completed on C:\LOG\DUMMY2_log.ldf
Note that now you only see that the log file is being zeroed out. The transaction log is zeroed out, even with instant file initialization enabled.
Also worth mentioning is instant file initialization isnt available for transparent data encrypted databases and there are also some security considerations related to allocated space assigned to a database that is not zeroed out. For more information regarding the security issues associated with instant file initialization refer to this MSDN article on "Database File Initialization".
How not having Instant File Initialization Can Impact Performance
We know that there is a performance impact associated with zeroing out the data file prior to allocating a database. However, how much of an impact is this really. There are a couple of simple methods to measure the effects of this.
The first method is using the trace flags method identified above. With instant file initialization turned off; turn on trace flag 3004 and 3605. Then create a large database, and note how long it takes to be created. Then after it is created, review the ERROR log and see the amount of time it took to zero out your DATA file on the new database. Here is my ERRORLOG output when I created a 5,000 MB DATA file for database DUMMY3:
2010-11-22 05:34:50.69 spid53 Zeroing C:\DATA\DUMMY3.mdf from page 0 to 640000 (0x0 to 0x138800000) 2010-11-22 05:41:03.19 spid53 Zeroing completed on C:\DATA\DUMMY3.mdf
As you can see, it took a little over 6 minutes to initialize the DATA file. Keep in mind I am running this on a VM, so your machine might see a very different slow down factor when you create a database of the same size.
Another method where you notice it takes time to do initialization is when restoring a large database. Have you ever noticed the progress percentage stays at zero for a long time before it starts progressing toward 100%. This is caused by instant file initialization. You can measure this as well by having instant file initialization disabled and having those two trace flags turned on, and then restoring a database. After the restore has completed, review the ERRORLOG to determine how long it took to zero out your data file. Here is the output when I restored the AdventureWorks database:
2010-11-22 05:57:57.48 spid53 Zeroing C:\DATA\AdventureWorks.mdf from page 1 to 21760 (0x2000 to 0xaa00000) 2010-11-22 05:57:57.48 spid53 Zeroing C:\LOG\AdventureWorks_2.ldf from page 1 to 2304 (0x2000 to 0x1200000) 2010-11-22 05:57:58.99 spid53 Zeroing completed on C:\LOG\AdventureWorks_2.ldf 2010-11-22 05:58:03.31 spid53 Zeroing completed on C:\DATA\AdventureWorks.mdf 2010-11-22 05:58:03.31 spid53 Zeroing C:\Temp\AdventureWorks_1.ndf from page 1 to 640 (0x2000 to 0x500000) 2010-11-22 05:58:03.57 spid53 Zeroing completed on C:\Temp\AdventureWorks_1.ndf
Here you can see to initialize the two DATA files as part of the RESTORE operation took a few seconds. However, if my database were many GB this zeroing process would have taken a considerable amount of time.
Now turn on instant file initialization and run the same tests again. You should see a substantial decrease in the time it takes to initialize a large database and/or restore a database. The restore time to initialize large database could be substantial especially when you are trying to get your large databases back up in running after a disaster of some sorts.
Optimizing Database Allocation with Instant File Initialization
Every time your database data file grows, it will take a little time to allocate the new blocks to your databases. While this growing process takes place all activity to the growing database is delayed. With instant file initialization turned on adding those new data pages are optimized by not waiting for SQL server to write binary zeroes across the newly allocated pages. If you want to make sure that every time your database data file grows it is done as quickly as possibly then you should turn on instant file initialization.