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.
1 CreateDBpre
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.
2 PerformVolumeMaintenanceTasks
Click Add User or Group.
3 AddUser
Enter the name of the SQL Server (database engine) service account & click Check Names.
4 SelectUser
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.
5 CreateDBpost

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: