Improving performance with instant file initialization

As a database administrator, one of your jobs is to make sure each SQL Server instance you manage is set up in such a way as to meet security requirements and is configured to optimize performance. There are lots of different aspects of securing and tuning performance for your SQL Server environment. This article discusses how instant file initialization can be configured to improve the performance of allocating disk space to a database when it is created, expanded, or restored.

What is instant file initialization?

Instant file initialization is a SQL Server setup option that keeps SQL Server from writing binary zeroes to data files when a database is first created, expanded, or restored. By avoiding the writing of binary zeroes, there is a lower performance impact when disk space is allocated for several database operations.

By default, when SQL Server creates a database, increases the size of a database, or restores a database, it needs to initialize the disk space prior to allocating the space. This initialization process writes binary zeroes (“0”) across all the bits and bytes of space being allocated. By writing binary zeroes across the disk space, the SQL Server engine makes sure that data previously stored in the unused disk space is completely overwritten before the disk space is allocated to a database.

Instant file initialization has been around since SQL Server 2005. When instant file initialization is turned on, the data files are not initialized with zeros. The transaction log always requires binary zeroes to be written when space is allocated to the log file. This is required so the database engine can maintain parity bits in each 512 byte disk sector.

Why the transaction log always needs to be initialized

Since the log file is circular, the database engine flip-flops the parity bits between 64 and 128 each time it wraps around. By changing the value of the party bits each time SQL Server wraps around, the database engine can determine which sectors are associated with the parity bit’s current value. When the log file sectors are initialized, they contain zeroes for the parity bits, which is an invalid value. The parity bit values are used during recovery to determine which sectors need to be used during the restore process, based on the current parity bits value. If SQL Server didn’t initialize the log file, then potentially, the file might have some residual data that looks like a valid parity bit value. More information about this can be found in this post by Paul Randal.

Is instant file initialization turned on?

When instant file initialization is turned on, the account running SQL Server will have been granted “SEManageVolumePermissions”. There are a couple of different options to determine if an instance has been set up to performed instant file initialization.

One method is to review the SQL Server log. When SQL Server starts up, it writes an informational message in the log file to indicate whether instant file initialization is enabled or disabled.

When instant file initialization is enabled, this informational message can be found in the log: “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.”

When instant file instant file initialization is disabled, this message will be displayed in the log: “Database Instant File Initialization: disabled. 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.”

Another method to determine if instant file initialization is turned on is to look at the instant properties under the “Advanced” tab with the SQL Server Configuration Manager tool. Figure 1 shows the “Advanced” properties for one of my instances.

Figure 1: Advanced tab configuration properties for instant MSSQLSERVER01

The “Instant File Initialization” property shows that the instance is set up for instant file initialization because the property is set to “Yes”. Note that only the SQL Server 2019 version of Configuration Manager allows you to see this property.

You can also use TSQL to determine if instant file initialization is enabled or disabled by using the “sys.dm_server_services” dynamic management view (DMV). The script in Listing 1 uses this DMV to show whether or not instant file initialization is turned on for an instance.

Listing 1: Is instant file initialization enabled?

SELECT servicename, instant_file_initialization_enabled 
FROM sys.dm_server_services
WHERE servicename like 'SQL Server (%';

When running the script in Listing 1 on my MSSQLSERVER01 instance, the information in Report 1 is displayed.

Report 1: Output when running Listing 1

Turning on instant file initialization when installing SQL ServerThe column “instant_file_initialization_enabled” is set to “Y” for the MSSQLSERVER01 service. This means the MSSQLSERVER01 instance has instant file initialization enabled. If that column were set to “N”, then instant file initialization would be disabled.

In the old days, prior to SQL Server 2016, you had to turn on instant file initialization manually. Starting with SQL Server 2016, turning on Instant File Initialization can be performed as part of the installation process.

If you install SQL Server using the installation wizard, instant file initialization can easily be turned on. Just check “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service” on the Server Configuration window of the installation wizard, as shown in Figure 2.

Figure 2: Server Configuration

You can also turn on instant file initialization when installing SQL Server from the command prompt. When running the setup.exe to install SQL Server, you just need to provide the /SQLSRVINSTANTFILEINT argument to enable instant file initialization.

Enabling instant file initialization after SQL Server is installed

There are a number of different ways to turn on instant file initialization after SQL Server has been installed. The first method to enable instant file initialization works for all versions of SQL Server that support it. Use the following steps to enable it:

1. Log on to your SQL Server with an account that is a member of the local Windows Administrator group.
2. Open the Local Security Policy application by running secpol.msc from a command prompt.
3. In the left pane that is displayed when the Local Security Policy application starts running, click the “Local Policies” item.
4. In the right pane double-click on the “User Rights Assignment”.
5. Find the “Perform volume maintenance tasks” item in the right pane, and double-click to open the settings.
6. Click the “Add User or Group” button in the “Local Security Setting” tab on the “Perform volume maintenance tasks Properties” screen
7. Add the account name running SQL Server to the dialog box, where the green arrow points to in Figure 3 and click OK.
   

Figure 3: Set up account so it can perform volume maintenance

8. Click OK to accept the properties and close the “Local Security Policy” dialog box.
9. Restart the SQL Server.

Starting with SQL Server 2019, you can use SQL Server 2019 Configuration manager to turn on instant file initialization. To do this, bring up SQL Server 2019 Configuration Manager, open the “advanced” tab, and find the “Instant File Initialization” property. Change the property from “No” to “Yes”, as shown in Figure 1. After changing the property, the SQL Server service will need to be restarted to enable Instant File Initialization. You can also disable instant file initialization by setting the property to “No” in SQL Server Configuration Manager and restarting the instance.

Which files are getting zeroed out?

When instant file initialization is disabled, both the data files and the transaction log files will be zeroed out when a database is created, restored, or file space is added. However, if instant file initialization is enabled then, only the transaction log is zeroed out during these operations.

You can see additional messages in the error log to identify which files are zeroed out by turning on trace flags 3004 and 3605. Trace flag 3004 is undocumented and tells SQL Server to show information about backups and file creation in the error log file. Whereas trace flag 3605 is documented, it tells the database engine to write the trace messages to the error log file. When these two trace flags are turned on, you can review the error log to see which files are being zeroed out when a new database is created, or additional space is allocated to a database. Additionally, messages about which files are zeroed out are even shown when a database is restored. Keep in mind that these trace flags should only be used for testing and not left on continually in production.

In Listing 2 shows the TSQL code that turns on trace flags 3004 and 3605.

Listing 2: Turning on trace flags 3004, and 3605

DBCC TRACEON(3004 ,3605 ,-1);
GO

When these two trace flags are enabled and instant file initialization is disabled, the messages in Report 2 are displayed when creating a new database named TestIFI.

Report 2: Informational messages about zeroing out database files

Zeroing C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI.mdf from page 0 to 1024 (0x0 to 0x800000) Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI.mdf (elapsed = 8 ms) Zeroing C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf from page 0 to 1024 (0x0 to 0x800000) Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf (elapsed = 7 ms) Starting up database ‘TestIFI’. Parallel redo is started for database ‘TestIFI’ with worker pool size [6]. FixupLogTail(progress) zeroing 2 from 0x5000 to 0x6000. Zeroing C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf from page 3 to 249 (0x6000 to 0x1f2000) Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf (elapsed = 1 ms)

Report 2 shows that the pages in the data (mdf) and log (ldf) files were zeroed out.

If instant file initialization is enabled and these trace flags are turned on, only the transaction log file will get zeroed out, as shown in Report 3.

Report 3: Informational messages about zeroing out database file

Zeroing C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf from page 0 to 1024 (0x0 to 0x800000) Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf (elapsed = 3 ms) Starting up database ‘TestIFI’. Parallel redo is started for database ‘TestIFI’ with worker pool size [6]. FixupLogTail(progress) zeroing 2 from 0x5000 to 0x6000. Zeroing C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf from page 3 to 249 (0x6000 to 0x1f2000) Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestIFI_log.ldf (elapsed = 1 ms)

Additionally, timestamps associated with these messages, which I didn’t include in the report, can show the performance impact of instant file initialization.

Improving performance with instant file initialization

The reason to turn on instant file initialization is to improve performance when disk space is allocated to a database. This writing of binary zeroes requires I/O and CPU to perform this initialization. This means that each time SQL Server allocates disk space to a database, there is a performance impact. The performance impact is minimal when small amounts of space are added, but when large amounts of space need to be zeroed out, the performance impact can be significant and can be noticed by those using the database.

To measure the performance impact, I will perform two tests. My test will create a database, first with instant file initialization enabled and then a second time with instant file initialization disabled. For this test, I will create a database named “PerformanceIFI” using the TSQL code in Listing 3.

Listing 3: Code used for performance testing

CREATE DATABASE [PerformanceIFI]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'PerformanceIFI', 
  FILENAME = N'C:\temp\PerformanceIFI.mdf' , 
  SIZE = 40GB)
 LOG ON 
( NAME = N'PerformanceIFI_log', 
  FILENAME = N'C:\temp\PerformanceIFI_log.ldf' , 
  SIZE = 10GB)
GO

When running the code in Listing 3 with instant file initialization disabled, the messages in Report 4 were written to the error log file.

Report 4: Timing for zeroing out mdf and ldf when instant file initialization is disabled

When running the code in Listing 3 a second time with instant file initialization enabled, the messages in Report 5 were found in the error log file.Report 4 shows that it took 51,192 ms to zero out the mdf file and 15,090 ms to zero out the ldf file.

Report 5: Timing for zeroing out only ldf file when instant file initialization is disabled

By turning on the trace flags and running the two tests, it’s easy to determine the performance impact of zeroing out the data file when creating a database. The larger the data file, the more significant the performance impact.Report 5 shows no zeroing out messages for the mdf file, but the log file still got zeroed out.

Security Issue

There is a minor security issue when instant file initialization is enabled. Because data files are not zeroed out, the old information that was on the unused disk space is available for DBAs or another administrator who has access to see. Meaning, someone could peek in and look at this uninitialized data that was added to a database. If anyone did browse these uninitialized bytes of database data pages, then they might be able to access sensitive data they have not been authorized to see. If there are any concerns over DBAs or anyone else seeing old disk data in the uninitialized data pages, then instant file initialization should not be enabled.

Instant file initialization

Instant file initialization is a great way to improve performance when disk space is allocated to a database. With the changes to the installation process and the configuration management tool, the SQL Server team at Microsoft has made it simple to enable instant file initialization. If there is a need to optimize performance of disk allocations, and there are no concerns over the security issues associated with instant file initialization, then instant file initialization should be enabled. Are your instances configured to enable instant file initialization or not?

If you liked this article, you might also like Managing SQL Server transaction log size.

The post Improving performance with instant file initialization appeared first on Simple Talk.

from Simple Talk https://ift.tt/3rCiXez
via