萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 效能調校實戰 (一):索引維護與碎片整理提升查詢速度

為何索引維護至關重要?

大多數資料表需要索引加速查詢。無索引時 SQL Server 必須掃描整張表 (Table Scan)。索引分為 叢集索引 (重排資料物理順序) 與 非叢集索引 (維護資料指標)。隨著資料異動,索引會產生碎片與分頁,降低效能。

索引內部架構

  • 資料以 8KB 頁面儲存。叢集索引將資料依鍵值排序,葉層即為資料頁,形成雙向鏈結串列。
  • 非叢集索引葉層儲存鍵值 + 叢集索引鍵 (或 RID),不直接指向資料頁。
  • 分頁:插入導致頁面滿載時,約一半資料移至新頁,鏈結指標可能指向非連續磁區,形成邏輯碎片與範圍碎片,導致磁碟搜尋增加。

偵測碎片:DBCC SHOWCONTIG

使用 DBCC SHOWCONTIG 分析指定資料表/索引碎片程度。範例以 Northwind 資料庫 Orders 表為例:

USE Northwind
DECLARE @table_id INT = OBJECT_ID('Orders')
DBCC SHOWCONTIG(@table_id)

關鍵指標解讀

指標含義健康閾值
Scan Density最佳範圍數 vs 實際範圍數越接近 100% 越好
Logical Scan Fragmentation邏輯順序與物理順序不一致的頁面百分比< 10% (理想 0%)
Extent Scan Fragmentation範圍切換次數過多< 20%
Avg. Page Density頁面填滿度視填滿因子而定,通常 80-95%

範例結果中 Scan Density 60%、Extent Scan Fragmentation 40% 顯示碎片嚴重,建議重建索引。

重建與重組索引

  • 重建:DBCC DBREINDEX 或 ALTER INDEX ... REBUILD。離線操作 (Enterprise 版可 ONLINE),完全重建索引樹,更新統計資料,耗時較久。
  • 重組:DBCC INDEXDEFRAG 或 ALTER INDEX ... REORGANIZE。線上操作,僅整理葉層頁面順序,較輕量但無法調整填滿因子。

維護策略建議

  1. 建立定期維護計畫 (維護計畫精靈 / SQL Server Agent 作業),每週執行索引重組,每月執行索引重建。
  2. 根據碎片報表動態決定:Logical Fragmentation > 30% → REBUILD;10-30% → REORGANIZE。
  3. 設定適當 填滿因子 (預設 0 = 100%),預留插入空間,減少分頁頻率。
  4. 同步更新統計資料 (UPDATE STATISTICS 或 sp_updatestats),確保查詢最佳化器擁有最新基數估計。

下集將探討查詢計畫分析、統計資料維護、參數嗅探等進階調校主題。

copyright © 萬盛學電腦網 all rights reserved