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…
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
