SQL Server 2016 – Perform Volume Maintenance

One of the server settings that I always enable when configuring a new instance of SQL is database instant file initialisation

In case you don’t know what this is, it is a setting that can be granted to the service account that the SQL database engine runs under that prevents SQL Server from “zeroing out” new space when a data file is created or expanded.

This improves the performance of CREATE/ALTER DATABASE statements, RESTORE statements and AUTOGROWTH operations. A full detailed article to how and why can be found here.

It’s cool to see that you can now enable this when installing SQL Server: –

SQL2016 Perform Volume Maintenance

I can’t think of any downside to having this enabled (off the top of my head, there’s probably one or two) and it’s good to see that Microsoft know that most people enable it so adding it as an option in the installer is great imho.

One thought on “SQL Server 2016 – Perform Volume Maintenance

  1. The only downside that I’ve heard of is that it opens a very tiny security hole. This is because it allocates the space for the new database file without overwriting the old data that was stored on those same blocks on disk. If someone was somehow able to get their hands on a decrypted copy of the data file (when not using TDE) then they could potentially crack it open with a hex editor and see the data inside.

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 )

Connecting to %s