SQL Server 2008 升級前保留查詢計畫：Plan Guide 實戰指南

為何需要升級前保留計畫？

升級至 SQL Server 2008 通常能提升效能，但對經深度調優的關鍵任務查詢，新版優化器可能選擇較差計畫。Plan Guide（計畫指南） 可在升級前捕獲並固化優良計畫，升級後作為後備強制使用。

操作步驟

1. 收集計畫：使用 sp_create_plan_guide 搭配 USE PLAN 提示，將關鍵查詢的 XML 計畫存入 Plan Guide。
2. 驗證適用性：確認 Plan Guide 能正確匹配目標查詢。
3. 升級資料庫：Plan Guide 隨資料庫升級保留。
4. 升級後評估：建議預設停用 Plan Guide，因統計資料更新可能帶來更好計畫。
5. 效能退化時啟用：若發現特定查詢效能倒退，啟用對應 Plan Guide 強制使用舊計畫。

計畫收集來源

• SET SHOWPLAN_XML ON / SET STATISTICS XML ON
• 查詢 sys.dm_exec_query_plan DMV
• SQL Server Profiler: Showplan XML 事件類別

範例：透過 DMV 擷取計畫並建立 Plan Guide

-- 目標查詢
SELECT City, StateProvinceID, PostalCode 
FROM Person.Address 
ORDER BY PostalCode DESC;

-- 擷取 XML 計畫
USE AdventureWorks;
GO
SELECT query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';

-- 建立 Plan Guide (假設 @xml_plan 為上述查詢結果)
EXEC sp_create_plan_guide 
    @name = N'Guide_Address_PostalCode',
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (USE PLAN @xml_plan)';

最佳實務

• 僅對經嚴格測試、確認穩定的關鍵查詢建立 Plan Guide。
• 升級後密切監控，優先讓優化器自行選擇新計畫。
• 定期審視 Plan Guide 必要性，避免長期綁定舊計畫阻礙效能提升。