資料庫立即檔案初始化

適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

在本文中,您將瞭解立即檔案初始化 (IFI),以及如何讓它加速 SQL Server 資料庫檔案的成長。

根據預設,系統會初始化數據和記錄檔,以覆寫先前已刪除檔案磁碟上剩餘的任何現有數據。 當您執行下列作業時,數據與記錄檔會先以零填入檔案(填滿零)來初始化:

  • 建立資料庫。
  • 將數據或記錄檔新增至現有的資料庫。
  • 增加現有檔案的大小(包括自動成長作業)。
  • 還原資料庫或檔案群組。

在 SQL Server 中,立即檔案初始化可讓您更快速地執行先前提及的檔案作業,因為它會回收已使用的磁碟空間,而不需以零填滿該空間。 相反地,當新數據寫入檔案時,會覆寫磁碟內容。

在 Azure SQL 資料庫 和 Azure SQL 受控執行個體 立即檔案初始化僅適用於事務歷史記錄檔。

立即檔案初始化和事務歷史記錄

適用於:SQL Server 2022 (16.x) 和更新版本,以及 Azure SQL 資料庫 和 Azure SQL 受控執行個體。

在過去,事務歷史記錄檔無法立即初始化。 不過,從 SQL Server 2022 (16.x) (所有版本) 和 Azure SQL 資料庫 和 Azure SQL 受控執行個體 開始,立即檔案初始化可以從事務歷史記錄自動成長事件中獲益,最多 64 MB。 新資料庫的預設自動成長大小增量為 64 MB。 大於 64 MB 的事務歷史記錄檔自動成長事件無法受益於立即檔案初始化。

由於事務歷史記錄檔的擴充本質以及事務歷史記錄是以序列方式寫入,所以允許在啟用透明數據加密 (TDE) 的資料庫上,立即初始化檔案。

  • 立即檔案初始化可用於一般用途和 azure SQL 資料庫 層 業務關鍵 層,Azure SQL 受控執行個體 只有利於事務歷史記錄檔的成長。
  • Azure SQL 資料庫 和 Azure SQL 受控執行個體 中無法設定立即檔案初始化。

啟用立即檔案初始化

只有在授與 SE_MANAGE_VOLUME_NAMESQL Server 服務啟動帳戶時,才可使用數據檔的立即檔案初始化。 Windows 管理員 istrator 群組的成員具有此許可權,並可將其新增至 [執行磁碟區維護工作] 安全策略,將其授與其他使用者。 事務 SE_MANAGE_VOLUME_NAME 歷史記錄中最多 64 MB 的立即檔案初始化不需要許可權,這是隨著 SQL Server 2022 (16.x) 版本而引進的。

重要

某些功能使用方式,例如 透明數據加密 (TDE),可以防止立即檔案初始化。 從 SQL Server 2022 (16.x) 和 Azure SQL 資料庫 和 Azure SQL 受控執行個體 開始,事務歷史記錄上允許 IFI。 如需詳細資訊,請參閱 立即檔案初始化和事務歷史記錄

注意

從 SQL Server 2016 (13.x)開始,此許可權可以在安裝期間授與服務帳戶。

如果使用命令提示字元安裝,請新增 /SQLSVCINSTANTFILEINIT 自變數,或在安裝精靈核取 [將執行磁碟區維護工作許可權授與 SQL Server 資料庫引擎 服務] 方塊。

若要授與帳戶 Perform volume maintenance tasks 許可權:

  1. 在將建立數據文件的計算機上,開啟本機 安全策略 應用程式 (secpol.msc)。

  2. 在左窗格中,展開 [本機原則],然後選取 [ 用戶權力指派]。

  3. 在右窗格中,按兩下 [ 執行磁碟區維護工作]。

  4. 選取 [新增使用者或群組 ],然後新增執行 SQL Server 服務的帳戶。

  5. 選取 [ 套用],然後關閉 [所有 本機安全策略 ] 對話框。

  6. 重新啟動 SQL Server 服務。

  7. 在啟動時檢查 SQL Server 錯誤記錄檔。

    適用於: SQL Server(從 SQL Server 2012 (11.x) SP4、SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x) 和更新版本開始)。

    1. 如果授與 SE_MANAGE_VOLUME_NAMESQL Server 服務啟動帳戶,則會記錄類似下列範例的資訊訊息:

      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. 如果未授SE_MANAGE_VOLUME_NAME與 SQL Server 服務啟動帳戶,則會記錄類似下列範例的資訊訊息:

      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.

    注意

    在 SQL Server 中,使用 sys.dm_server_services動態管理檢視中的 instant_file_initialization_enabled,識別實例是否已啟用立即檔案初始化。

安全性考量

建議您啟用立即檔案初始化,因為優點可能超過安全性風險。

當您使用立即檔案初始化時,只會覆寫已刪除的磁碟內容,因為新數據會寫入檔案。 基於這個理由,在數據檔的特定區域上寫入其他數據之前,未經授權的主體可能會存取已刪除的內容。

當資料庫檔案附加至 SQL Server 實例時,這項資訊洩漏風險會由檔案上的任意訪問控制清單 (DACL) 降低。 此 DACL 只允許對 SQL Server 服務帳戶和本機系統管理員進行檔案存取。 不過,卸離檔案時,用戶或服務可以存取沒有 SE_MANAGE_VOLUME_NAME的 。

下列情況有類似的考慮:

  • 資料庫已備份。 如果備份檔未受到適當的 DACL 保護,已刪除的內容就可供未經授權的使用者或服務使用。

  • 檔案會使用 IFI 來成長。 SQL Server 系統管理員可能會存取原始頁面內容,並查看先前刪除的內容。

  • 資料庫檔案裝載在儲存局域網路上。 儲存局域網路可能一律會將新頁面呈現為預先初始化,而且讓操作系統重新初始化頁面可能是不必要的額外負荷。

如果遺失已刪除內容的可能性是值得關注的,您應該採取下列其中一個或兩個動作:

  • 請務必確定任何中斷鏈接的數據檔和備份檔都有嚴格的 DACL。

  • 停用 SQL Server 實例的立即檔案初始化。 若要這樣做, SE_MANAGE_VOLUME_NAME 請撤銷 SQL Server 服務啟動帳戶。

    注意

    停用會增加數據檔的配置時間,而且只會影響在撤銷用戶權力之後建立或增加大小的檔案。

SE_MANAGE_VOLUME_NAME用戶權力

您可以在 SE_MANAGE_VOLUME_NAME Windows 管理員 工具本機安全策略小程式中指派用戶許可權。 在 [本機原則] 下,選取 [用戶權力指派],並修改 [執行磁碟區維護工作] 屬性。

效能考量

資料庫檔案初始化程式會將零寫入至初始化下檔案的新區域。 此程式的持續時間取決於初始化的檔案部分大小,以及記憶體系統的回應時間和容量。 如果初始化需要很長的時間,您可能會在 SQL Server 錯誤記錄檔和應用程式記錄檔中看到下列記錄的訊息。

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.

資料庫和/或事務歷史記錄檔的長時間自動成長可能會導致查詢效能問題。 這是因為在檔案成長作業期間需要自動成長檔案的作業,例如鎖定或閂鎖等資源。 您可能會在配置頁面的閂鎖上看到長時間等候。 需要長時間自動成長的作業會顯示的 PREEMPTIVE_OS_WRITEFILEGATHER等候類型。