2023年11月6日 星期一

啟用即時初始化檔案 (Instant file initialization) 的功能於 SQL Server 上

        即時初始化檔案 (Instant file initialization) 其實在 SQL Server 2005 就有提出,但隨著 SQL Server 版本的更新,此功能也不斷的更新與強化,所以此篇也特別介紹一下這個功能。

即時初始化檔案 (Instant file initialization) 主要說穿了,就是在建立資料庫 (Database) ,擴展空間或是還原資料庫等動作時,不進行磁碟初始化寫 zero 0 的動作,藉以加快初始化的動作,這樣一來在要求空間等動作時,可以加快申請的初始化動作,而且在 資料頁面(Data File) 的空間要求越大時,效果更是明顯,因為可以略過初始化填寫 zero 0 的動作。

通常有利也是有弊,我們在啟用這個功能時,也在深入的探討這個功能是否真的這麼強悍,但是否真的有適合不同的資料庫環境,我們分成下列的幾個方向進行討論。

1. 啟用此功能後是否會造成資料的錯寫或是錯亂等問題。

此功能持續觀察與使用已久,坦白說沒有遇過客戶回饋等問題,而且在相關的修正上,也沒有發現,所以這部份我覺得不是主要的問題。

2. 啟用後對效能上的影響,大約會有多少,是否有其他的負面影嚮。

此功能在啟用後,主要是進行相關動作時,不會針對 資料頁面(Data Page) 進行初始化寫 0 的動作,所以可以大大的加速擴展空間、建立資料庫、還原資料庫等動作,但是在負面影嚮的部份,我覺得主要是在安全性的部份,由於資料頁面沒有進一步的初始化,所以當有人透過此磁碟進行資料還原等動作時,是有可能可以查到之磁碟上原先存放的資料為何,這在有些管理上,可能是不允許的,也是有一定的風險存在的。

此功能在支援上,地端與雲端會有不同的支援程度,在雲端上,部份是沒有支援的,如 Azure 原本也是沒有支援的,但後來在 transaction log 的部份,是有 "部份" 支援的,但相對的 資料頁面(Data Page) 的部份,也是沒有支援的。

Database instant file initialization
Database instant file initialization - SQL Server | Microsoft Learn

另外在 AWS 上,很多人會透過 Amazon FSx for Windows File Server 來當作 SQL Disk 的部份,這部份也是沒有支援的,因為這個以前也有遇過客戶來討論過效能的問題,後來發現是不支援此功能,所以印像特別的深刻。

Using FSx for Windows File Server with Microsoft SQL Server
Using FSx for Windows File Server with Microsoft SQL Server - Amazon FSx for Windows File Server

說了這麼多,我們就來開始介紹如何進行啟用的方式。

在啟用的部份,你可以在一開始安裝時,在 Server Configuration 的部份,勾選 "Grant Perform Volme Maintenance Task privilege to SQL Server Database Engine Service" 的選項。


或是安裝完成後,從 SQL Server Configuration Manager -> SQL Server Service -> SQL Server(Instance name) -> Properties -> Advanced -> Instant File Initialization


另外在啟用時也請確認在作業系統層級,是否有權限進行套用,從 Local Security Policy -> Local Policies -> User Rights Assignment -> Perform volume maintenance tasks,請將你的 SQL Server 的啟動帳號加入此區的設定中即可。


設定完成後,你可以透過二個方式確認 SQL Server 是否有套用到 Instant file initialization 的功能。

1. 透過 SQL Server 的 Error Log 確認啟動時是否有套用此功能。

2023-11-03 18:02:37.12 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. 透過 T-SQL 指令查詢是否有啟用 Instant file initialization

select servicename, status_desc, service_account, instant_file_initialization_enabled from sys.dm_server_services



一切準備就緒就後,我們可以來透過建立資料庫的方式,來比較二者間的差異,藉以了解在啟用後之間的差異。

--啟用 Trace Flag 將建立資料庫等資訊寫入至 SQL LOG 中。
DBCC TRACEON(3004 ,3605 ,-1);
GO

CREATE DATABASE [testIFI]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'testIFI', 
  FILENAME = N'C:\tmp\testIFI.mdf' , 
  SIZE = 40GB)
 LOG ON 
( NAME = N'testIFI_log', 
  FILENAME = N'C:\tmp\testIFI_log.ldf' , 
  SIZE = 20GB)
GO


輸出比對:
2023-11-03 17:52:14.76 Server      Database Instant File Initialization: disabled.
2023-11-03 17:54:39.78 spid64      Zeroing C:\tmp\testIFI.mdf from page 0 to 5242880 (0x0 to 0xa00000000)
2023-11-03 17:54:44.13 spid64      Zeroing completed on C:\tmp\testIFI.mdf (elapsed = 4347 ms)
2023-11-03 17:55:33.57 spid64      Zeroing C:\tmp\testIFI_log.ldf from page 0 to 2621440 (0x0 to 0x500000000)
2023-11-03 17:56:25.14 spid64      Zeroing completed on C:\tmp\testIFI_log.ldf (elapsed = 51562 ms)
2023-11-03 17:56:25.23 spid64      Starting up database 'testIFI'.
2023-11-03 17:56:25.26 spid64      Parallel redo is started for database 'testIFI' with worker pool size [1].
2023-11-03 17:56:25.26 spid64      FixupLogTail(progress) zeroing 2 from 0x5000 to 0x6000.
2023-11-03 17:56:25.26 spid64      Zeroing C:\tmp\testIFI_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2023-11-03 17:56:25.27 spid64      Zeroing completed on C:\tmp\testIFI_log.ldf (elapsed = 7 ms)
2023-11-03 17:56:25.28 spid64      Parallel redo is shutdown for database 'testIFI' with worker pool size [1].

2023-11-03 18:02:37.12 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.
2023-11-03 18:04:43.74 spid63      Zeroing C:\tmp\testIFI2_log.ldf from page 0 to 2621440 (0x0 to 0x500000000)
2023-11-03 18:06:14.82 spid63      Zeroing completed on C:\tmp\testIFI2_log.ldf (elapsed = 91083 ms)
2023-11-03 18:06:14.90 spid63      Starting up database 'testIFI2'.
2023-11-03 18:06:14.94 spid63      Parallel redo is started for database 'testIFI2' with worker pool size [1].
2023-11-03 18:06:14.94 spid63      FixupLogTail(progress) zeroing 2 from 0x5000 to 0x6000.
2023-11-03 18:06:14.94 spid63      Zeroing C:\tmp\testIFI2_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2023-11-03 18:06:14.95 spid63      Zeroing completed on C:\tmp\testIFI2_log.ldf (elapsed = 8 ms)
2023-11-03 18:06:14.96 spid63      Parallel redo is shutdown for database 'testIFI2' with worker pool size [1].

從上述的結果,你就可以看到,當 Instant File Initialization 啟用時,資料頁面 (Data Page) 是完全不作初始化的動作 (zero),如同上述的說明,最後仍提醒在啟用此功能其實是真的可以大幅減少相關擴展的動作,但其相對的安全性,只能說可能要依不同的行業別在進行評估,如金融業等,當然存放資料的不同,可能也是要考慮,最後也希望此篇可以幫助到大家。

沒有留言:

張貼留言