Loading . . .

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

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.

Renaming an MS SQL instance (MS SQL 2005 or higher) - Photo by Anya Smith Previous post Renaming an MS SQL instance (MS SQL 2005 or higher)
Next post Which SQL user has got which role and access on my SQL Server – analysis of roles & rights

SQL from Hamburg

Das bin ich ;-)

Björn Peters - MVP - Data Platform
I had to do with MS SQL databases for the first time in 2000 and looked after these database systems for around seven years. From 2007 to 2019, I was employed as a database administrator and looked after many different SQL servers from medium-sized companies and large corporations from different industries.
Although I have some certificates, I get my insights and knowledge about the SQL Server purely from day-to-day business, reading / following numerous forums/blogs.
I'm not specialized in any topic, but I focus mostly on performance analysis.
Since the end of 2016, I have been the Azure Meetup Hamburg organizer and from April 2017 to June 2018, Cloud and Data Center Management MVP, and since July 2018, Data Platform MVP.