When a space growth operation is done within SQL Server (create, restore, auto-grow) the engine places zeros across the file system and then overwrites them to handle the growth.  Depending on the size of the growth what I explained above will obviously have some sort of performance impact.  Starting with SQL Server 2012 a new setting can be configured called Instant File Initialization that allows SQL Server to run a growth operation without the need of zeroing out the storage before, this has some performance improvements especially with bigger databases, bigger auto-growth setting, and with database restores.

Enabling Instant File Initialization:

To enable Instant File Initialization follow the steps below.

1. Launch the Local Security Policy Tool in Windows by running the following from the start menu – secpol.msc
2. Expand Local Policies
3. Click and Expand User Rights Assignment
4. Find and double click Perform Volume Maintenance Tasks
5. Add any service accounts that are used by SQL Server
6. Click Apply
7. Restart SQL Server

The post Set up Instant File Initialization for SQL Server appeared first on VitaminDBA.