Instant File Initialization – possible Performance Increasement

As part of a recurrent discussion on “How to set up a stabil and performing SQL Server?” I stumble on a regular basis about the (perhaps often underestimated) possibility of “Instant File Initialization”. I didn’t know this parameter until 1-2 years, but now it is part of our standard, how we install SQL Server.

I’ve got still a conversation with one of my DB2 colleagues in mind when their were just planning/testing a DB2 server migration…
He wanted to create a new database and had to wait until the respective data files were created… that needs a long time for a 100GB large datafiles.

MS SQL makes here no difference to IBM DB2, both database systems overwrite the newly occupying disk space the new data files or extensions of the data files in the file system first with zeroes, so as to ensure that old fragments were actually overwritten by already deleted files.
The data areas are thus adjusted, initialized in this case.

Within a restore there are now “growing”-Events which has to be filled up with zeros, cause the file were created as big as needed. So the restore does not require a previous filling / overwrite the data area.

How to check if Instant File Initialization is activated:

  • Activate Trace Flags 3004 (DBCC TRACEON(3004,-1);)
  • Activate Trace Flags 3605 (DBCC TRACEON(3605,-1);)
  • Create Test-database
  • have a look into SQL Server Error Log
Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

Leave a Reply

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