Instant File Initialization – possible Performance Increasement
This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!
As part of a recurrent discussion on “How to set up a stable and performing SQL Server?” I regularly stumble over the (perhaps often underestimated) possibility of “Instant File Initialization.” I didn’t know this parameter until 1-2 years ago, but now it is part of our standard for installing SQL Server.
I’ve still got a conversation with one of my DB2 colleagues in mind when they were 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 takes a long time for 100GB large data files.
MS SQL makes no difference to IBM DB2; both database systems overwrite the newly occupying disk space of the new data files or extensions of the data files in the file system first with zeroes to ensure that already deleted files overwrote old fragments. The data areas are thus adjusted and initialized in this case.
Within a restore, there are now “growing”-Events which has to be filled up with zeros cause the file was created as big as needed. So, the restore does not require a previous filling/overwriting of 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 the SQL Server Error Log
This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!
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!).
Auto Amazon Links: No products found.
