原文: sql server 索引闡述系列七 索引填充因子與碎片
一.概述
索引填充因子作用：提供填充因子選項是為了優化索引資料存儲和性能。 當建立或重新生成索引時，填充因子的值可确定每個葉級頁上要填充資料的空間百分比，以便在每一頁上保留一些剩餘存儲空間作為以後擴充索引的可用空間，例如：指定填充因子的值為 80 表示每個葉級頁上将有 20% 的空間保留為空，以便随着向基礎表中添加資料而為擴充索引提供空間。
填充因子的值是 1 到 100 之間的百分比，伺服器範圍的預設值為 0，這表示将完全填充葉級頁。
1.1 頁拆分現象
根據資料的查詢和修改的比例，正确選擇填充因子值，可提供足夠的空間，以便随着向基礎表中添加資料而擴充索引，進而降低頁拆分的可能性。如果向已滿的索引頁添加新行(新行位置根據鍵排序規則，可以是頁中任意行位置)， 資料庫引擎将把大約一半的行移到新頁中，以便為該新行騰出空間。 這種重組稱為頁拆分。頁拆分可為新記錄騰出空間，但是執行頁拆分可能需要花費一定的時間，此操作會消耗大量資源。 此外，它還可能造成碎片，進而導緻 I/O 操作增加。 如果經常發生頁拆分(可能過sys.dm_db_index_physical_stats 來檢視頁拆分情況)，可通過使用新的或現有的填充因子值來重新生成索引，進而重新分發資料。
填充值設定過低： 優點是 插入或修改時降低頁的拆分次數。缺點是 會使索引需要更多的存儲空間，并且會降低讀取性能。
填充值設定過高： 優點是 如果每一個索引頁資料都全部填滿，此時select效率最高。缺點是 插入或修改時需要移動後面所有頁，效率低。
二. 碎片與填充因子案例
下面分析在生産環境下，對長時間一個表的ix_1索引進行分析。
-- 有一個PUB_Search_ResultVersions2表長期有增删改操作， 在很長一段時間運作後，檢視碎片如下
dbcc SHOWCONTIG (PUB_Search_ResultVersions2,'ix_1')
通過上圖可以了解到平均頁密度是29.74%，也就是内部碎片太多，現三個頁的資料存儲量才是正常一個頁的存儲量。掃描的頁數是703頁,涉及到了192個區。下面重新維護索引
--重建索引
ALTER INDEX ix_1 ON dbo.PUB_Search_ResultVersions2 REBUILD 
通過上圖可以看到，掃描頁數隻有了248頁(原來是703頁) 用了36區(原來是192個區)，現等于一頁的實際資料是之前三頁的總量， 查詢将會減少了大量的I/O掃描。
如果頻繁的增删改，最好設定填充因子，預設是0,也就是100%, 如果有新索引鍵排序後，擠入到一個已填滿8060位元組的頁中時，就會發生頁拆分，産生碎片,這裡我使用圖形界面來設定填充因子為85%(最好通過t-sql來設定，做運維自動維護)，再重建下索引使設定生效。
下圖可以看出平均頁密度是85%，填充因子設定生效。可以在通過sys.dm_db_index_physical_stats重新檢視該索引頁使用數量。