Instant File initialization – pros and cons

It is a very interesting topic, which has brought quite some discussions at times.

Here is how it works – the Instant File Initialization(IFI) was introduced in SQL 2005 and allows us to grow large files quickly without waiting for minutes (depending on the file size and the speed of disks). Basically, when the file is created initially, it is not ‘zeroed’ out if the IFI is used, which in turn diminishes the total time of file creation.

If you wanted to enable this feature you would go to the Local Security Policy MMC (run the following command to get there: “%windir%system32secpol.msc /s”). Then go to ‘Local Policies’ and then to ‘User Rights Assignments’. From there you would find the ‘Perform volume maintenance tasks’ item and add the account under which the SQL Server Service is running.

There are some caveats, of course: if the SQL Server service is running under local system account, then you cannot revoke this right. If it is running under a domain account or as a local account which is not part of the Administrators group, then you will need to add this right.

Why is the topic debatable, though? Because, if you use the Instant File Initialization, then you will be able to grow the files easily, however when SQL Server Storage engine gets to write the data to the pages, as the pages are accessed, they need to be zeroed before the data can be written. The latter means a write delay, of course.

Also, there is a security risk, since all pages from the disc system are included in the backup and could be potentially read if the backup is lost. (If I were to ‘misplace’ a database backup, I would worry about a few other things before I think of the possibility to read the non-zeroed pages 🙂 )

By the way, a SQL Server Service restart is required to get the policy working for SQL Server.

So, what would be the solution? Well, it depends: either use the IFI or don’t use it. A good middle ground is to find the golden value of your data file growth value – a number which is not too big and not too small, so the SQL Server can grow the files and zero them out fast enough.

/Feodor Georgiev