要維護Index就一定要知道FillFactor,要設計一個效能好的INDEX也要瞭解FillFactor,這次我就來談談有關INDEX與FillFactor之間的關聯吧。
淺談INDEX與FillFactor(填滿因素)
SQL SERVER儲存資料的單位為Page(頁面),在建立Index時,SQL SERVER預設會在Page填滿資料(FillFactor為0或100),此時如果新增資料時,因為Page已滿,Index所以必須使用另一個Page儲存資料,在一連串的異動資料後,會導致資料散佈在各Page上,當SQL SEVER要讀取Index時,因為資料散佈在Page而使效能低落。為了避免因為異動資料而引起的效能低落,在建立Index時可以指定Page保留一定的空間。
當Page有保留一定空間(假設FillFactor為80)時,新增的會插入現在的Page的預留空間,如此資料就不會分散。
如果FillFactor設定太小(如30),會造成建立Index時,每一個Page都只用30%的空間,這樣同樣會有資料散佈的問題以及會浪費儲存空間。
通常在OLTP系統,因為異動較頻繁,所以要保留較多的空間,如果是OLAP因為資料異動較少所以保留空間可以少一點。如果是唯讀資料庫的就設定0或100。
另外設定FillFactor可以搭配PAD_INDEX,該選項的為FillFactor的設定也套用在Index的中間(intermediate-level)。最後提供兩段程式碼作為檢視與維護INDEX用。
程式碼:檢視DB內所有的Index的FillFactor
--檢視資料庫中所有的index
SELECT
DB_NAME() DBName,
OBJECT_NAME([object_id]) AS TableName,
name AS IndexName,
is_primary_key,
is_unique,
type_desc,
fill_factor,
has_filter,
filter_definition
FROM SYS.INDEXES
WHERE OBJECT_NAME([object_id]) IN
(SELECT Table_Name FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE)
執行結果:
程式碼:維護INDEX,並指定FillFactor為90與套用PAD_INDEX為ON
--重建INDEX
ALTER INDEX IX_Address_StateProvinceID
ON Person.Address
REBUILD
WITH (FILLFACTOR=90 ,PAD_INDEX=ON )