日前支援開發一個跟卡證相關系統，系統目前上線運作快一年。日前檢視一下該系統相關資料表是否有索引破碎問題需要重建。其中發現一張資料表是用來存放卡號等相關資料，其破碎率高達99% ( Clustered Index ) 。我檢視該資料表叢集索引是採流水碼編號排序，理論上應該不至於破碎如此嚴重啊 !
經檢視該資料表欄位後發現竟是長度可變資料欄位(varchar 或 nvarchar)導致資料表破碎嚴重問題，下面我就模擬一下情境並重現問題。
首先我先建立一張存放卡片資料的資料表，其中cName欄位是nvarchar而CardNum欄位是存放卡號，型態是varchar(16)。資料表建立好後，我先塞入10000筆預設資料，注意 : CardNum欄位我先塞入一碼的0
塞完10000筆資料後我們用DBCC SHOWCONTIG來檢視一下該資料表Clustered Index破碎率，如下圖紅色圈選處掃描片段僅3.33%
在我的卡證系統中會先針對每一個申請人產出一筆卡片資料，但是並無卡號 ( 預設帶入0 )，待製卡廠商製完卡後會回傳每一個人的卡號，此時再Update回去。 下圖我模擬廠商已完成卡片製作，此時我要將卡號Update進去資料表。
更新完資料後，我們再來看看該資料表Clustered Index破碎率，如下圖紅色圈選處掃描片段居然高達98.33%
※既然是Page空間不足，是否可以透過fillfactor設定來增加Free的空間進而減少破碎率呢?
下圖我先Truncate掉剛剛的資料表。
我將該資料表Clustered Index加了FillFactor參數，值為70 ( 如下圖紅色圈選處 )。
我們一樣塞入10000筆資料，看看Page是否會只使用70%而保留30%的空間呢。
答案是否定的，由下圖紅色圈選處可以看見平均頁面密度仍高達98.81%。
為什麼我們設定了FillFactor但在塞入10000筆資料後，密度沒有根據設定保留30%的空間呢?
下圖是MSDN對於FillFactor的說明，其中紅色圈選是重點。該參數只作用在建立或重建索引時，一旦我們建立完索引後Page頁面密度就底定了。後面新增的Page其密度並不會參照FillFactor值，依然是會填滿整張Page。一旦新增的Page密度一樣是滿的，那我再Update CardNum欄位由1碼改成16碼，勢必還是會發生Page Split的情況，因此FillFactor對我的情境並不適用。
※如果一開始就可以確認欄位長度，並改用Char的資料型態，是否狀況就不一樣呢 ?
我將CardNum欄位由Varchar(16)改為Cahr(16)。
接下來我一樣塞入10000筆資料 ( 截圖省略 )，然後我們看一下邏輯掃描片段為2.17%
最後一樣我再Update CardNum欄位由1碼改成16碼，改完以後再看一次邏輯掃描片段變化，如下圖所示邏輯掃描片段依然是2.17%
PASS的朋友提供一個方法，就是建立一個計算欄位，然後把空間先吃下來。TSQL如下
CREATE TABLE varchartest(
c1 INT IDENTITY PRIMARY KEY NOT NULL,
c2 VARCHAR(20) NOT NULL,
c3 AS REPLICATE('*',20-LEN(c2)) PERSISTED
)
參考資料來源 : 指定索引的填滿因素
我是ROCK
rockchang@mails.fju.edu.tw
