Instant File Initialization – possible Performance Increasement
Björn Peters
15. May 2015
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
AUTHOR
Björn Peters
Björn works as a Senior Consultant with a focus on the Microsoft Data Platform, SQL Server, PowerShell, and Azure SQL. He helps customers with operations, migrations, high availability, performance analysis, and troubleshooting.
On SQL from Hamburg, he writes about lessons learned from real-world projects – practical, technical, and with the goal of making complex problems easier to understand. Beyond SQL Server, he enjoys science fiction, baking, and cycling.