一次 SQL Server 效能調優實戰：從高 CPU 到索引優化的完整過程

背景與發現

• 生產伺服器 CPU 持續飆高，IO 尚有餘裕。
• 使用 **SQL Server 內建效能報表** → 「依總 CPU 時間排序的查詢」定位熱點語句。
• 報表無法直接複製語句、不明確標示所屬資料庫，匯出 Excel 後才能分析。

問題語句 (整理後)

SELECT *
FROM network_listen
WHERE node_code IN (
    SELECT DISTINCT node_code
    FROM view_Log_Network_circsByUnit
    WHERE status = '1'
)
OR node_code = (
    SELECT TOP 1 nodeCode
    FROM TransmissionUnit_LocalInfo
)
AND node_code <> (
    SELECT parentNodeCode
    FROM TransmissionUnit_RouterInfo
    WHERE nodeCode = (
        SELECT TOP 1 nodeCode
        FROM TransmissionUnit_LocalInfo
    )
);

執行計畫分析

• 計畫樹龐大，核心耗時在 view_Log_Network_circsByUnit 視圖展開後的兩個 叢集索引掃描 (Clustered Index Scan)。
• 視圖定義揭露：對 Log_Network_circs (48 萬筆) 做 GROUP BY node_code 取 MAX(end_time) 再 LEFT JOIN 回原表 —— **缺乏支援索引**。

視圖定義

CREATE VIEW [dbo].[view_Log_Network_circsByUnit] AS
SELECT B.*
FROM (
    SELECT node_code, MAX(end_time) AS end_time
    FROM Log_Network_circs
    GROUP BY node_code
) A
LEFT OUTER JOIN dbo.Log_Network_circs B
    ON A.node_code = B.node_code
   AND A.end_time = B.end_time;

資料表結構

CREATE TABLE [dbo].[Log_Network_circs](
    [log_id] [varchar](30) NOT NULL PRIMARY KEY CLUSTERED,
    [node_code] [varchar](100) NULL,
    [node_name] [varchar](100) NULL,
    [server_name] [varchar](100) NULL,
    [start_time] [datetime] NULL,
    [end_time] [datetime] NULL,
    [status] [varchar](30) NULL
) ON [PRIMARY];

優化策略：建立覆蓋索引

視圖核心存取路徑：GROUP BY node_code + MAX(end_time) + JOIN ON node_code, end_time。
建立複合非叢集索引：

CREATE NONCLUSTERED INDEX [IX_Log_Network_circs_node_end]
ON [dbo].[Log_Network_circs] (node_code ASC, end_time ASC)
WITH (SORT_IN_TEMPDB = ON, ONLINE = ON); -- Enterprise 版可 ONLINE

資料庫引擎調優顧問 (DTA) 同步建議：建立統計資料 _dta_stat_... (end_time, node_code) 與相同欄位順序的非叢集索引。

優化前後對比 (SET STATISTICS IO/TIME ON)

其他資料表 (TransmissionUnit_*, network_listen) 邏輯讀維持個位數，無變化。

執行計畫變化

• 前：視圖展開 → 兩次叢集索引掃描 (全表掃描 48 萬列) → Hash Match / Merge Join。
• 後：視圖展開 → 索引搜尋 (Index Seek) + 鍵值查詢 或直接覆蓋 → Nested Loops，行數估算精準。

關鍵心得

1. 報表只是起點：內建報表定位熱點，但需自行擷取語句至非生產環境完整分析。
2. 視圖隱藏複雜度：查詢計畫會展開視圖，實際調優點往往在視圖底層資料表。
3. 索引設計遵循查詢模式：GROUP BY a, MAX(b) + JOIN ON a, b → INDEX (a, b) 即可覆蓋排序、分組、聯結。
4. 驗證指標：STATISTICS IO 邏輯讀與 STATISTICS TIME CPU/經過時間是最客觀的改善證據。
5. 不可改動應用 SQL 時：資料庫層面 (索引、統計資料、檢視表重寫) 仍可大幅提升效能。

結語

此案例以單一複合索引解決高 CPU 問題，證明「找對存取路徑、建立正確索引」往往比重寫 SQL 更高效。建議建立定期 索引維護 (重組/重建) + 統計資料更新 排程，防止統計過期導致計畫退化。