Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 30, 2010

Improve Database Allocations through Instant File Initialization

By Gregory A. Larsen

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 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 
-- Turn on advanced options
EXEC sp_configure 'show advanced options', 1;
-- 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
-- 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' 
PRINT 'Instant Initialization disabled'; 
-- Reconfigure xp_cmdshell as it originally was
EXEC sp_configure 'xp_cmdshell', @config_value; 
-- turn off advanced option
EXEC sp_configure 'show advanced options', 0 
-- 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);

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);

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:

This policy can be enabled by adding the SQL Server Service
account to the “Perform Volume Maintenance Tasks” security policy

When you do this, the following screen will be displayed:

Local Security Policy: Security Settings

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:

Local Policies: User Rights Assignment

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:

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM