Whenever we create a database with a default size, SQL Server needs to allocate space from the disk. It starts “zeroing” out the new space before the database creation is complete. This is true for CREATE DATABASE, ALTER DATABASE, RESTORE and AUTOGROW and hence would delay the process.

As an example, you would notice it when trying to restore a large database. If you query the wait stats you might see wait type “PREEMPTIVE_OS_SETFILEVALIDDATA” in sys.dm_exec_requests while the growth occurs.

Having the Instant File Initialization (IFI) will skip the process of zeroing and improves the performance.

GOOD:

Turning on the option improves the performance of the above described operations. This is very helpful when we have AUTOGROWTH set to databases to proactively increase the data file sizes.

BAD:

However turning on IFI has a small security risk. That is because a file “delete” really just deallocates the space and a new allocation can reuse that space which may still have data in it. When you do not zero out the existing space there is a possibility that someone could read data that you thought had been deleted.

HOW:

To turn on the feature, we need to grant permission to the SQL Service Account or the group that has SQL Server Account “Perform volume maintenance tasks”

Open the Local Security Policy console from Control Panel\Administrative Tools.

Navigate to User Rights Assignment under Local Policies

Select the Perform Volume Maintenance Tasks policy and add the service account to the list. The Local Administrators group should already have this permission by default.

 

Finally restart the SQL Service for the permission changes to take effect.

NOTE:

  • This is not applicable for log file
  • IFI will not work if TDE is enabled

Further reading:

https://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/

– Hari Mindi


2 Comments

Hemadribabu · March 8, 2018 at 5:57 am

this is very interesting , I was searching for the reason why my auto growth settings are not responding correctly some time before, but the article posted briefs me the reason . and steps to correct

    harimindi · March 9, 2018 at 10:28 pm

    @Hemadri Glad that it helped you.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.