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 Peters

Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.

Amazon.com Empfehlungen

Damit ich auch meine Kosten für den Blog ein wenig senken kann, verwende ich auf diese Seite das Amazon.com Affiliate Programm, so bekomme ich - falls ihr ein Produkt über meinen Link kauft, eine kleine Provision (ohne zusätzliche Kosten für euch!).

SQL QuickStart Guide: The Simplified Beginner's Guide to Managing, Analyzing, and Manipulating Data With SQL (QuickStart Guides™ - Technology)

(1538)

10% Off $27.99 $25.19 (as of 3. October 2023 18:55 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

SQL: Learn SQL (using MySQL) in One Day and Learn It Well. SQL for Beginners with Hands-on Project. (Learn Coding Fast with Hands-On Project Book 5)

(956)

$2.99 (as of 3. October 2023 18:55 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Learn SQL Server Administration in a Month of Lunches: Covers Microsoft SQL Server 2005-2014

(61)

53% Off $34.99 $16.39 (as of 3. October 2023 18:55 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Azure SQL Revealed: A Guide to the Cloud for SQL Server Professionals

(52)

46% Off $37.99 $20.14 (as of 3. October 2023 18:55 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Bayonetta Origins: Cereza and the Lost Demon

(554)

33% Off $59.99 $39.89 (as of 3. October 2023 18:55 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)