[SQL] 小型索引

維護索引時發現，有些索引不論怎麼 ALTER INDEX REBUILD，avg_fragmentation_in_percent 完全沒有變化或是效果不彰（仍大於 MS 建議的 30 %）。

在 重新組織和重建索引 中這段文字點出問題所在

一般來說，小型索引的片段經常是無法控制的。小型索引的頁面會儲存在混合範圍上，混合範圍最多可由八個物件所共用，所以當重新組織或重建索引之後，小型索引中的片段可能不會減少。

利用 sys.dm_db_index_physical_stats DMV 判斷重整的兩個指標為

1. avg_fragmentation_in_percent - 邏輯片段的百分比 (索引中失序的頁面) - 越低越好
2. avg_fragment_size_in_pages - 在索引中一個片段的頁面平均數目 - 越高越好

而 avg_fragmentation_in_percent 在 5% 和 30% 之間，使用 ALTER INDEX REORGANIZE，而大於 30% 使用 ALTER INDEX REBUILD 來重整索引。

但對於小型索引來講，sys.dm_db_index_physical_stats 的 Page_Count（總索引頁數）也是一個重要指標，Page_Count 太小的索引，即使 avg_fragmentation_in_percent 很高，重整時也不會有作用或效果不彰的情況。

• 以下是測試結果（索引填充因子皆為預設 0 、表格中的 fragmentation 代表 avg_fragmentation_in_percent ）：

	重整前		重整後
資料筆數	fragmentation	Page_Count	fragmentation	Page_Count	重整 效果
305311	95.35	13937	0.0137	7270	Good
26033	92.98	699	0.6172	162	Good
11528	48.087	915	0	706	Good
3225	84.426	122	33.333	21	效果差
340	81.818	11	85.714	7	更糟糕
801	83.333	6	80	5	沒效果

＊ 測試資料庫約為 2G ，只有少數幾個 Table 資料量較龐大。

小型索引這個名詞很模糊，到底怎樣才算小，在這篇文章中 SQL Server: Simple Method to Resolve All Indexes Fragmentation 重整時 Page_Count 小於 8 會忽略，這篇 SQL Server Index Maintenance Performance Tuning for Large Tables 則是預設 128 以下不動作，以測試資料來看， Page_Count 在 500 以上， ALTER INDEX REBUILD 效果較明顯。

• 20130818 補充

閱讀 Where do the Books Online index fragmentation thresholds come from? ，該大師建議 page_count 小於 1000 時，就不要理會該索引破碎程度。

• 20211206 補充

在 SQL Server 2019 維護計畫中發現，重建索引的頁面計數 (Page Count) 預設是 1,000

參考資料
• 重新組織和重建索引
• sys.dm_db_index_physical_stats
• 讓 SQL Server 告訴你有哪些索引應該被重建或重組
• SQL Server: Simple Method to Resolve All Indexes Fragmentation
• SQL Server: Small Tables’ Clustered Indexes Fragmentation
• Why index REBUILD does not reduce index fragmentatation?
• Defragmenting Indexes in SQL Server 2005 and 2008 - 推薦閱讀
• Inside sys.dm_db_index_physical_stats
• Do you need to index very small table?
• SQL Server Index Maintenance Performance Tuning for Large Tables
• Where do the Books Online index fragmentation thresholds come from?