You might ask yourself: What is Instant File Initialization and why does it matter?
What Instant File Initialization is:
Instant File Initialization allow SQL Server to allocate storage (space from disks) very very quickly. As you probably know, when you delete files they are not physically deleted from the disk – they are only marked as deleted in the file system allocation structures. This is why various undelete programs can recover deleted files. So imagine a process requiring disk space. The disk area given to the process from the file system and operating system could potentially contains some secret information from deleted files. This is why the file system/OS insist to zero out the storage before the process can actually see it. (I’m not OS / file system expert so anyone is free to correct me here.) That is, unless the one allocating the storage has some special privileges.
When does the zeroing out of disk space occur?
Whenever SQL Server need disk space, such as:
- Create database
- Add file to database
- Restore (if the restore process includes database creation)
- File growth (both manual and auto-grow)
- Backup [edit 2011-08-26: Not sure how this got here, AFAIK shouldn’t be here]
Can we avoid the slow zeroing out space?
Yes, but only if you are on SQL Server 2005 or higher and for some operations: creation and allocation of data database files (mdf and ndf). The SQL Server service account need to have appropriate permissions from the OS. To be more precise, it need to have a privilege called SE_MANAGE_VOLUME_NAME. This is by default granted to Administrators. Do you run your SQL Server as an account being member of Administrators? I hope not. Did you grant this permission to the service account?
How do I grant this privilege?
This is easy. Add the SQL Server service account to the “Perform Volume Maintenance Tasks” security policy.
Does it matter?
You be the judge. Just to give you an idea, I created a database with a data file of various size (I had the log file at 1MB for all tests in order for it to influence the least). I timed it both with and without Instant File Initialization. I ran it on my desktop machine which has a RAID0 of two 7200RPM disks:
| Size | without IFI | with IFI | 1GB | 10.3 s | 0.3 s | 10GB | 128 s | 1.3 s | 50GB | 663 s | 4.5 s |
The difference is roughly a factor of 100!
When does it hurt?
Basically every time disk storage is allocated. But let us focus of the occasions where you can do anything about it, i.e., when you can have Instant File Initialization. Such occasions include:
- Whenever a database is created. Space need to be allocated for the data file(s).
- Whenever a data file is manually expanded in size.
- Whenever you have auto-grow for a data file. Note that potentially some poor user will now sit and wait for the auto-grow to complete.
- When you start SQL Server. Why? Tempdb need to be re-created.
- When you perform restore, if the destination database not already exists with matching database file structure.
How can I tell if I have Instant File Initialization?
I find it easiest to just create a database and time it, using some reasonable size for your data file, like 5GB. Actually, run two test: One with 5GB data file and really small log file. And then another with very small data file and 5GB log file. Remember that we never have Instant File Initialization for log files. For instance, run below and you will see (adjust the file path for the database files). You need to adapt your code for file path name, possibly database name and the datetime handling if you are lower then SQL Server 2008:
DECLARE @t time(3) = SYSDATETIME()
CREATE DATABASE IFI_test_ld
ON PRIMARY
(NAME = N'IFI_test', FILENAME = N'C:\IFI_test\IFI_test_ld.mdf', SIZE = 5GB, FILEGROWTH = 100MB)
LOG ON
(NAME = N'IFI_test_log', FILENAME = N'C:\IFI_test\IFI_test_ld.ldf', SIZE = 1MB, FILEGROWTH = 10MB)
SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time(3))) AS LargeDataFile
SET @t = SYSDATETIME()
CREATE DATABASE IFI_test_ll
ON PRIMARY
(NAME = N'IFI_test', FILENAME = N'C:\IFI_test\IFI_test_ll.mdf', SIZE = 3MB, FILEGROWTH = 100MB)
LOG ON
(NAME = N'IFI_test_log', FILENAME = N'C:\IFI_test\IFI_test_ll.ldf', SIZE = 5GB, FILEGROWTH = 10MB)
SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time(3))) AS LargeLogFile
Are numbers for above two about the same? If yes, then you don’t have Instant File Initialization. If the one with large data file is much quicker, then you do have Instant File Initialization. And now you also know approx how long it takes to allocate 1 GB with of data and log file for your SQL Server.
John Samson blogged about an alternative way to check, involving trace flags.
I’m curious: Did you have Instant File Initialization?