碎片化是經典話題，傳統觀點是「碎片一定要修」。但修碎片本身有成本，修完資料一變動又得重來，而現代 SSD 上「page 跳來跳去」的代價已大幅降低。核心張力不是「碎片好不好」，而是這些碎片真的傷到你的查詢了嗎、傷多少——再決定要不要動、怎麼動。
🧠 Intuition
TIP
一句話分辨：page split 讓 page 的邏輯順序（雙向鏈結串列）仍然正確，但實體順序錯位——原本相鄰的資料散到不同 extent。範圍掃描本該是順序 I/O，碎片一多就退化成隨機 I/O、read-ahead 失效。
- 兩種碎片：external fragmentation 是 page 在不同 extent 間跳來跳去；internal fragmentation 是 page 內部有空洞、沒填滿。前者傷順序掃描，後者則不一定壞——空隙能讓後續 INSERT/UPDATE 不必再 split。
- page split 是成因：page 滿了、又有 INSERT 或把某列撐大的 UPDATE 擠進中間，SQL Server 就把一半留在原 page、另一半搬到新 allocate 的 page，新 page 位置不見得相鄰。這對應到 B-Tree 的節點分裂。
- 尾端插入不 split：INSERT 在索引尾端只會 allocate 新 page、不觸發 split。這正是 sequential key（如
IDENTITY）比 random key（如 random GUID）對 OLTP 友善的核心原因。 - 量測優先：不要無腦排程 nightly REBUILD。先用 DMV 量出哪幾條索引真的碎、且真的影響查詢，再決定策略——對絕大多數索引「永遠不修」也沒影響。
⚖️ Tradeoffs
Internal vs External — 不是所有碎片都是敵人
- External fragmentation（page 跨 extent 錯位）傷的是大範圍 sequential scan：順序 I/O 變 random I/O、read-ahead 失效。
- Internal fragmentation（page 沒填滿）不一定壞：留下的空間讓後續 INSERT/UPDATE 不必再 split，反而對寫入有利。
- 所以「碎片率高」不能一概而論——要看是哪一種、以及你的負載是掃描重還是寫入重。
Fill Factor — 空間換 split 成本
- 建索引時指定
FILLFACTOR（如 80）讓每個 leaf page 只填 80%、留 20% 給未來插入，降低 page split 機率。 - 太小 → page 數變多 → 大範圍 scan 要讀更多 page、I/O 增加。
- 太大（接近 100）→ 沒有緩衝空間 → split 機率高。
- 對 sequential key 幾乎不需要 fill factor（插入都在尾端）；對 random key 才有意義。
Heap 的 forwarding pointer — 修碎片代價更高
- heap 表也會碎片化，但修整時若用
ALTER TABLE ... REBUILD，所有 nonclustered index 必須一起重建——因為它們透過 RID（實體位置）指向資料，而資料剛被搬家。 - 修 heap 碎片的代價因此遠比修 clustered index 高。
- 這也是 heap 不宜作為主流 OLTP 設計的原因之一。
偵測 — sys.dm_db_index_physical_stats 的三種模式
- 最重要的 DMV，關鍵欄位：
avg_fragmentation_in_percent（邏輯碎片百分比）、avg_page_space_used_in_percent（頁密度）、fragment_count、page_count。 - 三種掃描模式：Limited（預設、最快最粗）、Sampled（抽 1% page、速度與準確度平衡）、Detailed（掃所有 page、最準最重）。
- 注意：不指定
OBJECT_ID又設 Detailed，會對整個資料庫所有索引做完整掃描，可能讓 production 陷入維護級 I/O 衝擊；另外索引不到 10,000 page 時，即使選 Sampled 也會自動升級為 Detailed。
REBUILD vs REORGANIZE — 徹底重建 vs 就地整理
- REBUILD：drop + create 整個索引，重建統計資訊（含 fullscan），成本高；預設上 schema-mod 鎖，可加 ONLINE。適合高度碎片。
- REORGANIZE：只在 leaf level 就地整理 page，online、可中斷恢復、不更新統計，成本低。適合中度碎片。
- 經典 Microsoft 粗略規則：0–5% 別動、5–30% REORGANIZE、>30% REBUILD——但這只是指引，重點仍是查詢效能是否實際受影響。
碎片什麼時候才真的有害 — 掃描 vs 點查、HDD vs SSD
- 小範圍查詢/點查對碎片幾乎無感：只讀單頁時，誰跟誰實體相鄰根本不重要。
- 大範圍 scan 受害最深：碎片把 sequential I/O 打成 random I/O、read-ahead 也失效，這才是實測中修整前後差距最大的地方。
- 硬體也改變結論：現代 SSD/NVMe 上「page 跳來跳去」的隨機存取代價已大幅降低，讓「一定要修碎片」的老規則更站不住腳。
🔑 Takeaways
- 碎片分兩種：external（跨 extent 錯位，傷順序掃描）與 internal（頁內空洞，對寫入反而有利）——別把兩者混為一談。
- page split 是成因：中段 INSERT 或撐大列的 UPDATE 會分裂 page；尾端插入不 split，這是 sequential key 勝過 random key 的關鍵。
- 偵測用
sys.dm_db_index_physical_stats（看avg_fragmentation_in_percent與頁密度），修整用 REBUILD（重、徹底）或 REORGANIZE（輕、就地），並用 fill factor 做空間換 split 的預防。 - 碎片只在大範圍 scan 上真的有害，對點查幾乎無感，且 SSD 進一步稀釋其代價——以查詢實測為準，別依賴老舊規則無腦排程 REBUILD。
No notes yet — jot your takeaways or Q&A here.