Instant File Initialization for SQL Server 2005/2008
August 24, 2010
This article discusses Database Instant File Initialization, which instructs the OS to allocate the required disk space immediately before actually zero-ing out the content from the allocated space. This means the content of the allocated disk area remains there until SQL overwrites it, improving the performance and reducing the downtime. Read on to learn more...
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:
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.
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 dont 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.
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.
Point worth noting down...
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 dont 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.
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.