Improve Database Allocations through Instant File Initialization

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 Randal’s
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 isn’t
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.

»


See All Articles by Columnist
Gregory A. Larsen