Instant File Initialization for SQL Server 2005/2008

August 24, 2010

Introduction

When we create a database or add a file to an existing database, SQL Server occupies the disk space and zeros out the data pages to overwrite any data already existing on it from the previously deleted files. This process might take several minutes to several hours depending on the file size or disk speed. There are certain scenarios, as discussed below, in which this behavior might be problematic for you:

• In a data warehouse scenario often the database and schema objects are created and fresh data is pulled from sources to recover from a disaster. Because of the humongous data warehouse database size, the downtime would be increased by hours required in the process of zeroing out the data pages for your database.
• If you have enabled and set a larger value for autogrowth for database files, during this expansion the SQL Server will write zero to the extended portion of the files and may take a longer time before it becomes responsive. For that reason your query or transaction will fail/time out with query time-out exception.
• If you are adding a file-group or files to an existing database, the ALTER DATABASE command may again take several minutes to several hours depending on the file size and hence increased deployment time.
• If you are restoring a database or a file-group, it may take longer and increase the downtown for the system during recovery process.

Starting with SQL Server 2005, we can take advantage of the Database Instant File Initialization feature provided by Windows XP Professional, Windows Server 2003, Windows Server 2008 and later versions. If you enable database instant file initialization, the OS allocates the required disk space, as and when required, immediately before actually zero-ing out the content from the allocated space; it means the content of the allocated disk area remains there until SQL writes some data to it later on. This feature works for data files (mdf or ndf) only and is not applicable for log files (ldf).

How to use it...

To take advantage of this great feature, you need to give SE_MANAGE_VOLUME_NAME privilege to the SQL Server service account (an account under which your SQL Server service is running). To assign this privilege you need to add SQL Server service account to Perform Volume Maintenance Tasks security policy by going to START -> Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Assignment (you can directly run secpol.msc from RUN) and then add the SQL Server service account to “Perform volume maintenance tasks” security policy as depicted in the image below.

Figure 1 - Enabling Instant Database File Initialization

Notice the “Administrators” group is already a member of this security policy by default. So if SQL Server service account belongs to “Administrators” group, this feature is already enabled for it and you don’t need to do anything to take advantage of this feature.

Once you have added the SQL Server security account to this security policy you need to then execute gpupdate /force from the command prompt and restart the SQL Service for this change to take effect.

I have created a small demo to show you the time difference between not enabling this feature and enabling this feature. In the figure 2, you can see a database (500GB initial data file and 10 GB initial Log file) creation takes 21 minutes and 10 seconds.

Figure 2 - Database creation without using Instant Initialization feature

In the figure 3, you can see the same database (500GB initial data file and 10 GB initial Log file); the script just takes 29 seconds only.

Figure 3 - Database creation with Instant Initialization feature

Point worth noting down...

• Instant file initialization works for data files only, not for log files and hence if your log file is huge it may take longer. You can learn more about why the transaction log is not supported here.
• The above CREATE DATABASE statement has been simplified for better understanding, though in production you would want to have your log file on a separate disk from data files and further you would want to split your data file into multiple files, keeping them on separate drives with separate spindles for better Input / Output performance.

Consideration to take...

If the data on the previously deleted file (remember the same space might now be used by new database files) were confidential, this option may pose a security threat as those data were not overwritten in the beginning and can be read from the drive by unauthorized users, if the database is not attached, even though they don’t have SE_MANAGE_VOLUME_NAME privilege.

This security threat is reduced to some extent if the database is attached as it uses Discretionary Access Control List (DACL) on the file. However, an intruder, at SQL Server level, can still use DBCC PAGE command to see the content.

Conclusion

Instant File Initialization is great feature to consider and proves to be a lifesaver, especially during recovery after a disaster or deployment of new database / database files, by minimizing the downtime during database creation, database modification, database restoration and the modification, which result in auto growth of the database. In this case the OS only allocates the required disk space immediately before actually zero-ing out the content from the allocated space. The point to note here though, this feature works for data files (mdf or ndf) only and is not applicable for log files (ldf). Use of this feature requires consideration of the points discussed above.

References

MSDN: Database File Initialization

MSDN: Physical Database Storage Design

» See All Articles by Columnist Arshad Ali