Allow Instant Initialization

Aim: Grant the relevant permissions to the SQL Server service account to allow for Instant Initialization of database files.

Instant Initialization allows space to be allocated to database (.mdf) files without first filling that space with zeros. Therefore, creation of databases (especially large ones) is significantly faster, as is the manual or auto-growth of database files. Service accounts which are admins will automatically use Instant Initialization, but non-admin SQL Server service accounts must be added to the Perform Volume Maintenance Tasks security policy if Instant Initialization is required.

To set a non-admin SQL Server service account, see Set SQL Server Service Accounts (note: since the previous blog post, I have moved to Windows Server 2012 R2 & SQL Server 2012).
To get an indication of how long it takes to create a database without Instant Initialization, create a database with a large(ish) database file. On my machine, it took 6.25 minutes.

To allow Instant Initialization, launch Local Security Policy. Open Local Policies, User Rights Assignment & right-click on Perform Volume Maintenance Tasks to view its Properties.

Click Add User or Group.

Enter the name of the SQL Server (database engine) service account & click Check Names.

Click OK on each of the Properties windows to apply the change. Via SQL Server Configuration Manager, restart the SQL Server service.

To confirm that Instant Initialization is now used by SQL server, test how long it takes to create another database of the same size as the first. This time it took 2 seconds.

For more on Instant Initialization, see https://msdn.microsoft.com/en-us/library/ms175935.aspx.