SQL Server->> enable Instant file initialization (open files are initialized in a timely manner)

When we install SQL Server, we see an option below that says "Grant Perform Volume maintenance Task privilege ..." When you specify the account for each SQL Server service to run the startup account. To be precise, this thing is not a thing of SQL Server, "Perform Volume maintenance Task" permission belongs to Windows Security Policy category, This is a change to the local Windows security policy through a UI interface.

What's the "Perform Volume maintenance Task" thing? It represents the "Instant File initialization" feature of the Windows operating system. This means that under the Windows operating system, the file is created with the "zeroing out" task, that is, the space allocated to a file by the file system is written to clear the original data. Open the file in a timely manner after initialization in Windows we delete a file actually does not erase the data on the disk, but to save the next time this part of the space is re-use when the time to write. The "Instant File initialization" means not performing the "zeroing out" task.

Advantages:

Then back to SQL Server to talk about this thing, it is the role of SQL Server every time the file is created or the space growth occurs when the old data is not clear, thereby improving performance. This can affect the CREATE database, ALTER database, restore, and autogrowth scenarios.

Disadvantages/Risks:

Security risk is the drawback of initiating file initialization in a timely manner.

Limitations:

1) log file does not apply. As for why the log file does not apply, in fact, it is not difficult to understand. Data file space allocation behind the Gam,sgam, PFS, and internal tables to support data space metadata validation, nature will not make mistakes, it should not be "own data" as their own, even if this part of the data remains in the file. However, unlike log, the log file does not have this layer of logic to support it.

2) can not be a sparse file, such as SNAPSHOT,DBCC CHECKDB command run when the resulting file (this file is automatically generated);

3) Transparent Data encryption database option cannot be opened;

4) Tracking flag (trace flag) 1806 cannot be enabled. The function of this tag is to turn off the timely file initialization feature. The two repel each other.

If you don't check that option when you install it, how do you turn it back on?

Turn on the security policy modification on the line. That is, the SQL Server service account has permissions to initialize the files of SQL Server that are automatically enabled. Adding permissions requires restarting the SQL Server service, and removing permissions requires restarting the server.

Reference documents:

SQL Server Premier Field Engineer Blog--how and why to Enable Instant File initialization

SQL Server->> enable Instant file initialization (open files are initialized in a timely manner)