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重新檢視該索引頁使用數量。

原文出處：https://www.cnblogs.com/MrHSR/p/9366475.html