SQL Server 索引重建實戰：從碎片偵測到效能提升的完整流程

一、問題偵測：找出效能瓶頸表

1. 捕捉高耗時查詢

利用 sys.dm_exec_query_stats 列出平均耗時、總耗時、CPU、邏輯讀最高的語句：

SELECT 
    creation_time AS '語句編譯時間',
    last_execution_time AS '上次執行時間',
    total_physical_reads AS '物理讀取總次數',
    total_logical_reads/execution_count AS '每次邏輯讀次數',
    total_logical_reads AS '邏輯讀取總次數',
    total_logical_writes AS '邏輯寫入總次數',
    execution_count AS '執行次數',
    total_worker_time/1000 AS '所用CPU總時間ms',
    total_elapsed_time/1000 AS '總花費時間ms',
    (total_elapsed_time / execution_count)/1000 AS '平均時間ms',
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
         ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS '執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE ... NOT LIKE '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

2. 結合業務鎖定核心表

列出各表資料量，結合業務知識找出「查詢量大 + 資料量大」的核心表，再查看磁碟使用情況。

3. 檢測索引碎片度

DBCC SHOWCONTIG ('表名')

關鍵指標：

• 掃描密度：最佳 100%，案例中僅 33.52% → 需掃描 3 倍頁面
• 邏輯碎片 / 區碎片：超 30% 即需優化，案例中嚴重超標

二、重建索引：一鍵修復碎片

USE [資料庫名];
ALTER INDEX ALL ON [表名稱] REBUILD;

重建後對比：

• 掃描密度：33.52% → 96.9%
• 邏輯碎片：大量 → 近乎為零

三、效能驗證：平均查詢時間降近半

重跑步驟 1 的查詢，發現大多數語句平均耗時下降接近 50%。前台實測回應明顯加快。

四、維護建議

• 定期排程重建/重組索引（碎片 5~30% 用 REORGANIZE，>30% 用 REBUILD）
• 結合 sys.dm_db_index_physical_stats 自動化監控
• 避開業務高峰期，或使用 ONLINE=ON（企業版）減少封鎖