多檔案的FileGroup之資料配置

本次參加PASS Taiwan Summit 2022時和朋友討論到多檔案的File Group是如何分散存放資料,其分散的最小單位為何呢?

首先我們建立一測試資料庫TestDB然後其Primary的FileGroup有4個檔案,如下圖所示。

 

在TestDB中建立一資料表tb1,內含兩的欄位,其中name這一個欄位給char(8000),這樣一來一筆資料等於會佔用掉一個Page的空間,方便我們等一下的LAB。

 

下面這段語法會幫我們列出tb1在各個Database File有幾筆資料。由於目前還沒資料寫入,所以沒有任何資料列出

 

下圖寫入第一筆資料。

 

我們在下圖查詢中可以看見TestDB4.ndf這個檔案中有了1筆資料寫入。

 

接下來一次寫入7筆資料,由於一筆資料相當於一個Page空間,我們已經寫入8筆資料,照SQL機制來看就相當寫入了64KB,一個Extents的空間了。

 

接下來再次執行上面語法,這次可以看見多了一筆TestDB3.ndf的資訊,原有的TestDB4.ndf已有8筆資料,第9筆資料寫入到TestDB3.ndf中。此時應該有所疑問,我只寫入8筆資料,為何會有9筆紀錄在兩個檔案中呢?
其實多出來的那筆是叢集索引的中繼層,並非我們剛剛寫入的任何一筆。(如下圖紅色框選處)
從下圖中我們可以發現當8筆資料(相當於8個Page的空間)被寫入TestDB4.ndf後下一筆就改寫入TestDB3.ndf了,因此可以約略看出多檔案狀態下應是以Extents為最小單位輪流寫入各個Data File內

 

再次寫入7筆資料。

 

下圖中TestDB3.ndf檔案也寫了8筆資料了,理論上也佔了一個Extents了,那下一筆寫入應該會改分配到另一個檔案去了。

 

我們再寫入一筆資料。

 

果然! 剛剛最新的一筆被分配到TestDB2.ndf去了。

 

再寫入8筆資料後TestDB.mdf也有分配到資料了

 

最後再寫入8筆資料可以發現又輪回到TestDB4.ndf了,其他檔案都只有8筆,TestDB4.ndf則有9筆

經由這簡易實驗,可以知道當一個DB有多個檔案的狀態下SQL會以Extents為單位的輪流分配到各個檔案中,以達到分散資料的目的。
重要:當檔案大小不一致時,SQL會根據個檔案剩餘空間比例來分配資料。例如A檔有10MB,而B檔有20MB的請況下就會給A檔寫入1個Extents後給B檔寫入2個Extents(1:2方式)來分配空間。

我是ROCK

rockchang@mails.fju.edu.tw