Should you enable Instant File Initialization for your SQL Server?
Yes. You should. Now let me explain.
What is 'File Initialization' ? When the database data files talk to the disk, they have to be 'initialized'. This means that any newly allocated space is overwritten with zero values (0×0). Windows does this to avoid problems by accessing old data, previously stored on the same disk. This zero File Initialization causes operations like this to take much longer:
What is 'File Initialization' ? When the database data files talk to the disk, they have to be 'initialized'. This means that any newly allocated space is overwritten with zero values (0×0). Windows does this to avoid problems by accessing old data, previously stored on the same disk. This zero File Initialization causes operations like this to take much longer:
- Create new database
- Auto-growth of any data file
- Backup or restore
Ok. So what is 'Instant File Initialization' ? This is a Windows setting that will allow SQL Server to skip the zero initialization. Instant File Initialization will claim the space to be used WITHOUT pre-filling it with zeros. The impact? This setting will make data file growth faster. Much faster.
Instant File Initialization can be enabled by granting the SQL Server service account the Perform Volume Maintenance Task privilege, as I have outlined below. This only applies to data files. The SQL Server database log files cannot be initialized like this.
Start / Run – type in ‘secpol.msc’
Left side under Security Settings, go to Local Policies, then User Rights Assignment.
Right side under Policy, double-click ‘Perform volume maintenance tasks’.
In ‘Perform volume maintenance task Properties’, choose ‘Add User or Group’.
In ‘Perform volume maintenance task Properties’, choose ‘Add User or Group’.
Add in your SQL Server Service account.
Restart your SQL Server service.
This is actually one of my defaults in any SQL Server build. I encourage you to enable it on your existing servers now, as well as any new builds in the future. Take a look at each of these references before making any changes. Let me know if I can provide anything further.