資料庫立即檔案初始化Database Instant File Initialization

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

在此文章中,您將了解檔案立即初始化,以及如何加以啟用來加速 SQL Server 資料庫檔案的成長。In this article, you learn about instant file initialization, and how to enable it to speed up growth for your SQL Server database files.

根據預設,系統會將資料和記錄檔初始化,以覆寫磁碟上先前刪除之檔案中所遺留的任何現有資料。By default, data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. 當您執行下列作業時,會先將資料檔與記錄檔清空 (填入 0),以初始化檔案:Data and log files are first initialized by zeroing the files (filling with zeros) when you perform the following operations:

  • 建立資料庫。Create a database.
  • 將資料或記錄檔新增至現有的資料庫。Add data or log files, to an existing database.
  • 增加現有檔案的大小 (包括自動成長作業)。Increase the size of an existing file (including autogrow operations).
  • 還原資料庫或檔案群組。Restore a database or filegroup.

SQL ServerSQL Server 中,檔案立即初始化 (IFI) 可讓您更快速地執行先前提到的檔案作業,因為其會回收已使用的磁碟空間,而不需以零填滿該空間。In SQL ServerSQL Server, instant file initialization (IFI) allows for faster execution of the previously mentioned file operations, since it reclaims used disk space without filling that space with zeros. 而是在新資料寫入檔案時將磁碟內容覆寫為新資料。Instead, disk content is overwritten as new data is written to the files. 記錄檔無法立即初始化。Log files cannot be initialized instantaneously.

啟用檔案立即初始化Enable instant file initialization

只有在 SQL ServerSQL Server 服務啟動帳戶被授與 SE_MANAGE_VOLUME_NAME 時,才能使用檔案立即初始化。Instant file initialization is only available if the SQL ServerSQL Server service startup account has been granted SE_MANAGE_VOLUME_NAME. Windows Administrator 群組的成員擁有此權限,並可將此權限授與其他使用者 (方法是將他們新增到「執行磁碟區維護工作」 安全性原則。Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

重要

使用透明資料加密 (TDE) 等某些功能時,會防止檔案立即初始化。Some feature usage, such as Transparent Data Encryption (TDE), can prevent Instant File Initialization.

注意

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此權限可在安裝期間進行安裝時授與給服務帳戶。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), this permission can be granted to the service account at install time, during setup.

如果使用命令提示字元安裝,請新增 /SQLSVCINSTANTFILEINIT 引數,或在安裝精靈中核取 [對 SQL Server Database Engine 服務授與「執行磁碟區維護工作」權限] 方塊。If using the command prompt install, add the /SQLSVCINSTANTFILEINIT argument, or check the box Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service in the installation wizard.

授與帳戶「 Perform volume maintenance tasks 」權限:To grant an account the Perform volume maintenance tasks permission:

  1. 在要建立資料檔案的電腦上,開啟 [本機安全性原則] 應用程式 (secpol.msc)。On the computer where the data file will be created, open the Local Security Policy application (secpol.msc).

  2. 在左窗格中,展開 [本機原則] ,然後按一下 [使用者權限指派] 。In the left pane, expand Local Policies, and then click User Rights Assignment.

  3. 在右窗格中,按兩下 [執行磁碟區維護工作]。In the right pane, double-click Perform volume maintenance tasks.

  4. 按一下 [新增使用者或群組] 並新增執行 SQL Server 服務的帳戶。Click Add User or Group and add the account that runs the SQL Server service.

  5. 按一下 [套用] ,然後關閉所有 [本機安全性原則] 對話方塊。Click Apply, and then close all Local Security Policy dialog boxes.

  6. 重新啟動 SQL Server 服務。Restart the SQL Server service.

  7. 請在啟動時檢查 SQL ServerSQL Server 錯誤記錄檔。Check the SQL ServerSQL Server error log at startup.

    適用範圍: SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP4、SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 與 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 和更新版本開始)。Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP4, SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later).

    1. 如果 SQL ServerSQL Server 服務啟動帳戶獲授與 SE_MANAGE_VOLUME_NAME,會記錄如下資訊訊息:If the SQL ServerSQL Server service startup account is granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged:

      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.

    2. 如果 SQL ServerSQL Server 服務啟動帳戶獲授與 SE_MANAGE_VOLUME_NAME,會記錄如下資訊訊息:If the SQL ServerSQL Server service startup account has not been granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged:

      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.

    注意

    您也可以使用 sys.dm_server_services DMV 中的資料行 instant_file_initialization_enabled 來識別是否已啟用檔案立即初始化。You can also use the column instant_file_initialization_enabled in the sys.dm_server_services DMV to identify if instant file initialization is enabled.

安全性考量Security considerations

我們建議您啟用檔案立即初始化,因為這麼做的好處可能超過安全性風險。We recommend enabling instant file initialization as the benefits can outweigh the security risk.

使用檔案立即初始化時,只有在將新資料寫入檔案時,才會覆寫已刪除的磁碟內容。When using instant file initialization, the deleted disk content is overwritten only as new data is written to the files. 基於這個理由,直到其他資料寫入資料檔特定區域之前,未經授權的主體可能得以存取刪除的內容。For this reason, the deleted content might be accessed by an unauthorized principal, until some other data writes on that specific area of the data file.

當資料庫檔案附加到 SQL ServerSQL Server 的執行個體之際,檔案上的判別存取控制清單 (DACL) 可降低此一資訊洩漏風險。While the database file is attached to the instance of SQL ServerSQL Server, this information disclosure risk is reduced by the discretionary access control list (DACL) on the file. 此 DACL 只允許 SQL ServerSQL Server 服務帳戶和本機系統管理員存取檔案。This DACL allows file access only to the SQL ServerSQL Server service account and the local administrator. 但是,當檔案卸離後,沒有 SE_MANAGE_VOLUME_NAME 的使用者或服務便能存取該檔案。However, when the file is detached, it may be accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME.

在下列情況中,有類似的考量因素:Similar considerations exist when:

  • 資料庫已備份。The database is backed up. 如果備份檔案未使用適當的 DACL 保護,未經授權的使用者或服務便可存取刪除的內容。If the backup file is not protected with an appropriate DACL, the deleted content can become available to an unauthorized user or service.

  • 使用 IFI 讓檔案成長A file is grown using IFI. SQL Server 系統管理員可能會存取原始頁面內容,並查看先前刪除的內容。A SQL Server administrator could potentially access the raw page contents and see the previously deleted content.

  • 資料庫檔案裝載於存放區域網路The database files are hosted on a storage area network. 存放區域網路也可能會一律以預先初始化方式顯示新頁面,因此讓作業系統重新初始化頁面可能是不必要的額外負荷。It is also possible that the storage area network always presents new pages as pre-initialized, and having the operating system re-initialize the pages might be unnecessary overhead.

如果洩漏刪除的內容可能是一項隱憂,您應該採取下列其中一或兩個項動作:If the potential for disclosing deleted content is a concern, you should take one or both of the following actions:

  • 務必確保所有卸離的資料檔和備份檔都具有限制的 DACL。Always make sure that any detached data files and backup files have restrictive DACLs.

  • 針對 SQL ServerSQL Server 的執行個體停用檔案立即初始化。Disable instant file initialization for the instance of SQL ServerSQL Server. 若要這樣做,請從 SQL ServerSQL Server 服務啟動帳戶撤銷 SE_MANAGE_VOLUME_NAMETo do so, revoke SE_MANAGE_VOLUME_NAME from the SQL ServerSQL Server service startup account.

    注意

    停用將增加資料檔案的配置時間,並只會影響使用者權限撤銷後建立或大小增加的檔案。Disabling will increase allocation times for data files, and only affects files that are created or increased in size after the user right is revoked.

SE_MANAGE_VOLUME_NAME 使用者權限SE_MANAGE_VOLUME_NAME user right

您可在 [Windows 系統管理工具]、[本機安全性原則] 小程式中,指派 SE_MANAGE_VOLUME_NAME 使用者權限。The SE_MANAGE_VOLUME_NAME user privilege can be assigned in Windows Administrative Tools, Local Security Policy applet. 在 [本機原則] 下,選取 [使用者權限指派] 並修改 [執行磁碟區維護工作] 屬性。Under Local Policies select User Right Assignment and modify the Perform volume maintenance tasks property.

效能考量Performance considerations

資料庫檔案初始化程序會將零寫入至檔案中要初始化的新區域。The Database File initialization process writes zeros to the new regions of the file under initialization. 此程序其時間長短取決於所初始化的檔案部分大小,以及儲存系統的回應時間和容量。The duration of this process depends on size of file portion that is initialized and on the response time and capacity of the storage system. 如果初始化需要很長的時間,則可能會在 SQL Server 錯誤記錄檔和應用程式記錄檔中看到記錄了下列訊息。If the initialization takes a long time, you may see the following messages recorded in the SQL Server Errorlog and the Application Log.

Msg 5144
Autogrow of file '%.*ls' in database '%.*ls' was cancelled by user or timed out after %d milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Msg 5145
Autogrow of file '%.*ls' in database '%.*ls' took %d milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

資料庫和/或交易記錄檔的長時間自動成長可能會導致查詢效能問題。A long autogrow of a database and/or transaction log file may cause query performance problems. 這是因為需要檔案自動成長的作業會在檔案成長作業期間保留鎖定或閂鎖等資源。This is because an operation that requires the autogrowth of a file will hold on to resources such as locks or latches during the duration of the file grow operation. 您可能會看到配置頁面的閂鎖等候時間很長。You may see long waits on latches for allocation pages. 需要長時間自動成長的作業會顯示 PREEMPTIVE_OS_WRITEFILEGATHER 其等候類型。The operation that requires the long autogrow will show a wait type of PREEMPTIVE_OS_WRITEFILEGATHER.

另請參閱See Also

CREATE DATABASE (SQL Server Transact-SQL)CREATE DATABASE (SQL Server Transact-SQL)