Improve SQL Server performance with instant file initialization

Today I would like to talk about a special topic in SQL Server--Instant file initialization (Instant initialization). For your instance of SQL Server, if you enable Instant file initialization, you will get a huge performance boost under certain circumstances. Instant file initialization defines how the SQL Server engine deals with the Windows operating system when new space is allocated in the data file.

cause of the problem

In the SQL Server default configuration, when you allocate new space in the data file, SQL Server calls the internal WIN32 API function and fills in 0 to initialize the newly allocated NTFS cluster. This means that each byte of the newly allocated space is rewritten with a value of 0 (0x0). This behavior prevents access to the raw data problem, which is the data that is physically stored in the same ntf cluster. 0 initialization occurs in the following SQL Server operations:

• Create a new database
• Automatic database growth
• Database backup Restore

When you create a 10GB database file, SQL Server 1th needs to write the 10GB data block with a value of 0. This will take a long time. Let's look at the CREATE DATABASE statement below:

1 --Create A new ten GB database2 CREATE DATABASETestdatabase on PRIMARY3 ( 4NAME=N'Testdatabase',5FILENAME=N' D: \sql\data\testdatabase.mdf' , 6SIZE=10240000KB,7FileGrowth=1024KB8 )9 LOG  on Ten ( OneNAME=N'Testdatabase_log',  AFILENAME=N'D:\SQL\Log\TestDatabase_log.ldf' , -SIZE=1024KB, -FileGrowth= Ten% the ) - GO

As you can see from the code, here I create a 10GB database file. In my SQL Server default configuration, this statement took nearly 49 seconds because SQL Server writes 10GB of 0 to storage through the WIN32 API function. Suppose you have a damaged database (for example also 10GB), what happens if you want to restore the backup? What do people usually do in the 1th step? is to delete the corrupted database. This means that your database file is gone, and during the restore file operation, the SQL Server 1th step needs to rebuild the file.

1. SQL Server 1th steps to create a 10GB "empty" database, in the ntf file system, the data file will be filled with 0 initialization.
2. When the last 1-step backup is restored, SQL Server writes the 10GB data to the data file again.

You will find that you have written nearly 20GB of data to your storage! If you restore your backup on an existing file, SQL Server skips step 1th and writes 10GB of data directly to your storage-you get a 100% performance boost!

Instant File Initialization

If you do not want SQL Server to perform the 0 initialization of your data file, you can reconfigure SQL Server. If you authorize a service account, under the corresponding SQL Server that is running- perform volume maintenance tasks (Performance Volume maintenance Task) privileges, after you restart SQL Server, the SQL Server skips the 0 initialization of the data file. I have said this is only valid for data files-log files in SQL Server or total 0 initialization! This is rice has a way to drop !!! If the log file is not filled with 0 initialization, the failover process does not know where to end when the journal file is wrapped. A failed restore stops at the place where it finds the next log record that needs to handle the head 0 value.

You can grant the service account for SQL Server the ability to perform volume maintenance tasks (Performance Volume maintenance Task) privileges through secpol.msc.

after a reboot, SQL Server can now skip the 0 initialization of the data file. When I execute the CREATE DATABASEagain, it only costs nearly 210ms--that's a huge difference! What about side effects? You can get the original content stored in the allocated NTFS cluster through the DBCC PAGE command:

1 --Enable DBCC trace flag 36042 DBCCTRACEON (3604)3 GO4 5 --Dump out a page somewhere in the data file6 --A Hex Dump is working here7 DBCCPAGE (Testdatabase,1, +,2)8 GO

You saw me dump a page in my data file. In that case, SQL Server will now return some of your junk data-data previously stored in the newly allocated NTFS cluster-that has no data associated with SQL Server.

By granting this permission to SQL Server, you basically open a security vulnerability where the user (with the correct permissions) can obtain old data that was previously stored in the file system. So you have to carefully consider whether you want to grant this privilege to SQL Server.

If you want to know if your SQL Server is running with this permission, you can enable the 3004 and 3605 trace Flags. With these enabled trace flags, SQL Server in the error log will report that the file was filled with 0 initialization. Next, when you create a new database, you do not grant this privilege to SQL Server, and from the error log you can see that both the data and log files are 0 initialized:

If SQL Server has the perform Volume maintenance task (Performance Volume maintenance Task) privilege, from the error log you can see that only the log file is filled with 0 initialization:

Windows kernel

What happens inside the Windows operating system when you run the service account under SQL Server and you grant it the Perform volume maintenance task (Performance Volume maintenance Task) privilege? When this privilege is enabled (internally, it calls the so-called se_manage_volume_name function through the WIN32 API), SQL Server can invoke Setfilevaliddata 's WIN32 API function. As you can see from the online documentation, the process that calls that function has se_manage_volume_name permissions. When that function is called by SQL Server, the function itself sets the file so-called high Watermark-in its NTFS cluster, the file expands directly without rewriting the original content! As described in the online documentation:

"The setfilevaliddata function allows you to avoid filling data and zeros when writing nonsequentially to a file . The function makes the data in the file valid without writing to the file. As a result, although some performance gain may is realized, existing data on disk from previously existing files can Inad Vertently become available to unintended readers. "

...

"If setfilevaliddata is used on a file, the potential performance gain was obtained by not filling the allocated C Lusters for the file with zeros. Therefore, reading from the file would return whatever the allocated clusters contain, potentially content from other users . This was not necessarily a security issue at this point, because the caller needs to the se_manage_volume_name PR Ivilege for Setfilevaliddata to succeed, and all data is on disk can is read by such users. "

As I've said, for your instance of SQL Server, whether you enable this permission is primarily related to security.

Summary

Should you enable instant file initialization for your instance of SQL Server? This depends on the specific situation ... When you are a SQL Server and system administrator, it is a good idea to grant this permission because you can always access the file system as a system administrator. But when you have a dedicated system administrator and a SQL Server administrator, this is not possible because the system administrator does not trust you and you do not get this permission for your instance of SQL Server. In that case, SQL Server always fills in 0 initialization data and log files ...

Thanks for your attention!

Improve SQL Server performance with instant file initialization