In fact I was writing a checklist on the topic SQL Server performance tuning with focus on running the Dynamics AX 2012 databases.
One tip deals with “Instant File Initialization”. Even though there are some articles in the internet about this feature, I have not really found detailed “official” information of Microsoft.
The questions are: What is Instant File Initialization? How can Instant File Initialization be started? What are its limits? Are the risks?
1. Instant File Initialization: theory and practice
Standard behavior of SQL Server when
a. Creating a new Database
b. Adding a file to a database
c. File growth (manual and auto-growth)
d. Restore of database (if it includes database-creation or file growth)
is that the new allocated space is overwritten by zeros before it can be used by SQL Server.
I have made some tests with a 32bit Developer Edition of SQL Server 2008 R2 on my Intel Core i3 (2x2,13 GHz; 3.2 GB of usable RAM; 2,5 inch notebook-hard disc) Laptop. Results for 64bit systems are comparable. Of course other hardware affects the results in detail.
The results:
Without enabled Instant File Initialization the creation of a new database with a size of 10 GB took about 4:30 minutes. Raising the database size to 20GB took another 4:30 minutes.
Blocking a productive system for such a period of time can cause some trouble with users and your boss.
Those are scenarios where Instant File Initialization can help you.
After setting up Instant File Initialization SQL does not overwrite newly allocated space with zeros any longer: Creation of a database with the size of 10GB just took round about 5 seconds. Raising the database size to 20GB took somewhat between 5 and 10 seconds. On a fast server-system with SAN-storage this time span should be even shorter.
2. Setting up Instant File Initialization:
Instant File Initialization is not mainly a SQL Server feature. It is an operating system privilege which was established by Microsoft with Windows Server 2003 and Windows XP. Therefore it is also available with Windows Vista, Windows Server 2008 and Windows 7.
To set up Instant File Initialization you have to grant the service account which is running SQL Server the
SE_MANAGE_VOLUME_NAME-privilege. To get this privilege add the SQL Server service account to
Perform Volume Maintenance Tasks security policy. Quite simple, isn’t it?
Please note: Just the Professional versions of Windows Vista and Windows Server grand direct access to the group policies. Home Premium versions have not installed the needed tools to edit the polies directly. In these versions you have to edit registry directly.
3. What tasks do not profit of Instant File Initialization?
Instant File Initialization does not work with log files. When allocating new space for log files SQL Server is zeroing out the needed space even though Instant File Initialization is enabled.
According to Paul S. Randal log-files needed to be zeroed out because this is the only way to determine the end of the log-file in case of a crash.
For more information read his blog-entry.
4. Are there risks?
According to some Microsoft blog-entries there seems to be a slight risk. If you are running two or more instances of SQL Server on a computer there could be the risk, that space which was formally allocated to one instance, will be allocated to another instance. With enabled Instant File Initialization this space is not overwritten with zeros. Therefore there is the risk that users of this other instance can read old data of the instance which had formally allocated the space.
I have spoken to a Microsoft Consultant about this risk. He said, that the risk is mainly of a theoretical nature, because the data extremely hard to read and most time not of a specific order.
5. Conclusion:
Instant File Initialization can speed up creation of big, new databases or raising the size of an existing database in a significantly.
Log-flies are not affected by Instant File Initialization, because allocated space of log files needs to be zeroed out by design.
There is a slight security risk when you run more than one instance of SQL Server but when you can rule out this risc for your data Instant File Initialization is a great feature which can speed up SQL Server significantly.
