After building new SQL server one of the most important post configuration required to setup for increasing database performances is Instant File Initialization (IFI)( you can also change it on existing server). When SQL server require to perform some specific operations like;
- Create a new database.
- Add new file to an existing database.
- Increase the size of an existing files including Auto growth
- Restore a database
For example, when you perform any of the above tasks, the default setting is for sql server to reserve space using zeros ‘0’, but under IFI, the space is allocated without the initial zeros which in turn increases performance on above operations.
How to Enable Instant file Initialization (IFI)
Identify the SQL server service account
Make sure service account is enrolled in server administrator groups
In Windows search box ,type local security policy
From perform volume maintains task property, add SQL server service account to this group
Must restart the SQL server service in order to implement this change to SQL server
-By : MD Ullah