您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 計劃指南實戰：不修改應用程式碼優化參數化查詢計畫

編輯：關於SqlServer

痛點：無法修改源碼的查詢調優

第三方套件、加密存儲過程、廠商鎖定代碼等場景下，DBA 無法直接修改 SQL 文本加入 Hint。SQL Server 2005 引入計劃指南，允許在資料庫層面為特定查詢綁定優化提示，實現「零代碼變更」調優。

三種類型對照

類型	匹配範圍	典型場景
OBJECT	預存程序、函數、觸發器內的查詢	可識別模組名稱的內部查詢
SQL	獨立批次/動態 SQL（`sp_executesql`）	應用程式內嵌參數化查詢、ORM 產出 SQL
TEMPLATE	參數化形式的獨立查詢	強制參數化資料庫下的模板化查詢

核心系統程序：`sp_create_plan_guide`

EXEC sp_create_plan_guide 
    @name = N'GuideName',           -- 指南名稱
    @stmt = N'SELECT ...',          -- 要匹配的完整 SQL 文本（必須逐字一致）
    @type = N'SQL',                 -- OBJECT / SQL / TEMPLATE
    @module_or_batch = NULL,        -- OBJECT 類型時指定模組名，其餘 NULL
    @params = N'@Param1 int, ...',  -- SQL/TEMPLATE 類型：參數定義字串
    @hints = N'OPTION (RECOMPILE)'; -- 要附加的 Hint 子句

實戰案例：參數嗅探導致計畫固定不佳

場景重現

USE AdventureWorks;
GO

-- 第一次執行：ProductID=870（高選擇性，4688 行），優化器選擇 Table Scan
EXEC sp_executesql 
    @stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
    @params = N'@ProductID int', @ProductID = 870;

-- 第二次執行：ProductID=897（極低選擇性，2 行），卻復用 Table Scan 計畫
EXEC sp_executesql 
    @stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
    @params = N'@ProductID int', @ProductID = 897;

結果：第二次執行本應用 Index Seek，卻因計畫快取復用導致全表掃描，效能差異可達數十倍。

建立 SQL 類型計劃指南強制重編譯

EXEC sp_create_plan_guide
    @name = N'Fix_SalesOrderDetail_ParameterSniffing',
    @stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@ProductID int',
    @hints = N'OPTION (RECOMPILE)';
GO

驗證效果

重新執行上述兩次查詢，執行計畫顯示：ProductID=870 仍用 Table Scan，ProductID=897 改為 Index Seek。
Profiler/Extended Events 可見 SP:Recompile 事件，確認每次執行前重新編譯。
查詢 sys.plan_guides 確認指南存在。

管理與維護

檢視現有指南

SELECT name, type, module_or_batch, params, hints, is_disabled, create_date, modify_date
FROM sys.plan_guides;

控制指南狀態：`sp_control_plan_guide`

-- 停用單一指南
EXEC sp_control_plan_guide N'DISABLE', N'Fix_SalesOrderDetail_ParameterSniffing';

-- 啟用單一指南
EXEC sp_control_plan_guide N'ENABLE', N'Fix_SalesOrderDetail_ParameterSniffing';

-- 刪除單一指南
EXEC sp_control_plan_guide N'DROP', N'Fix_SalesOrderDetail_ParameterSniffing';

-- 批次操作
EXEC sp_control_plan_guide N'DISABLE ALL', NULL;
EXEC sp_control_plan_guide N'DROP ALL', NULL;

最佳實務與風險控管

精確匹配：@stmt 必須與應用提交的 SQL 逐字一致（含空白、換行、大小寫），建議從 Profiler/Query Store 擷取確切文本。
文件化：每個指南必須記錄「建立原因、匹配 SQL 來源、Hint 內容、預期效果、負責人、審查週期」。
定期審查：統計資訊更新、索引變更、版本升級可能導致指南失效或反效果，建議季度審查。
測試先行：在非生產環境驗證執行計畫變化與效能指標（Duration, CPU, Reads），再推上線。
避免濫用：優先考慮索引優化、統計資訊維護、查詢重寫，計劃指南作為最後手段。
版本相容：SQL Server 2005~2022 皆支援，Azure SQL Database 部分支援（受限於受控執行個體）。