Condividi tramite

SQLSweet16!, Episode 7: Install Option for Instant File Initialization

  • Articolo

Reviewed by: Dimitri Furman, Kun Cheng, Denzil Ribeiro Database Instant File Initialization helps improve performance of certain file operations. Prior to SQL Server 2016, enabling instant file operation has been cumbersome (editing the Local Security Policy to add the SQL Server service account to the Perform Volume Maintenance Tasks policy, followed by restarting SQL Server instance), therefore some administrators missed out on this performance improvement technique.

If you want to enable instant file initialization, SQL Server 2016 makes life simpler for DBAs and System Administrators by providing a simple checkbox during the install of SQL Server, as shown in Figure 1.

[caption id="attachment_4055" align="alignnone" width="1294"]Figure 1: Option to enable instant file initialization while installing SQL Server 2016 Figure 1: Option to enable instant file initialization while installing SQL Server 2016[/caption]

The checkbox “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service” is unchecked by default. To enable instant file initialization, all you need to do is check that box. No need to edit the security policies through the Local Security Policy application any more.

Notably, setup grants the privilege to the per-service SID for the SQL Server instance, e.g. to the NT SERVICE\MSSQL$SQL2016 security principal, for an instance named SQL2016. This is preferable to granting the privilege to the SQL Server engine service account, which is still sometimes done by administrators. The service account is subject to change, and if changed, SQL Server could unexpectedly lose the IFI privilege. But the per-service SID remains the same for the lifetime of the instance, which avoids this risk.

To emphasize the impact of instant file initialization, I installed a SQL Server 2014 instance on a server and restored a database (of size 190 GB). By default, this SQL Server instance doesn’t have instant file initialization enabled. And, then installed a SQL Server 2016 instance on the same server (checked the above mentioned checkbox during the install), and restored from the same database backup. The results are in Figure 2.

[caption id="attachment_4045" align="alignnone" width="1064"]Figure 2: Improved restore time with instant file initialization in SQL Server 2016. Figure 2: Improved restore time with instant file initialization in SQL Server 2016.[/caption]

How to know if instant file initialization was used while restoring your database? Use the simple techniques described here: https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/, or (another improvement in SQL Server 2016) check the server error log. If IFI is enabled, the following message is logged during server startup: “Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.”

Instant file initialization not only helps improve restore performance, but helps other operations as well, such as creating a database or adding new files to an existing database, extending a file or autogrow operations.

Comments

  • Anonymous
    August 24, 2016
    Sure, thanks, this has been a good change.