設計參數化查詢的計畫指南

您可以在已參數化的查詢上建立計畫指南。基於下列任一理由，都可將查詢參數化：

• 查詢是用 sp_executesql 來提交的。

• 資料庫已啟用強制參數化。這樣會將所有適合的查詢都參數化。

• 已在此查詢所屬的查詢類別上建立了單獨的計畫指南，指定要將那些查詢參數化。

當您在已參數化的查詢上建立計畫指南時，基本上就是在為參數化成相同形式的所有查詢建立計畫指南，唯一不同的地方，只有其常數常值。例如，在啟用強制參數化的資料庫中，下列兩個查詢會參數化成相同的形式：

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50;

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

若要在已參數化的查詢上建立計畫指南，請建立 SQL 類型的計畫指南，並且在 sp_create_plan_guide 預存程序中指定查詢的參數化形式。

例如，若要取得上述範例其中一個查詢的參數化形式，並在該查詢上建立計畫指南以強制最佳化工具使用雜湊聯結，請遵循下列步驟：

1. 執行 sp_get_query_template，取得查詢的參數化形式。

2. 如果 SQL Server 尚未使用 sp_executesql 或 PARAMETERIZATION FORCED 資料庫 SET 選項將該查詢參數化，請建立 TEMPLATE 類型的計畫指南來強制參數化。

3. 在已參數化的查詢上建立 SQL 類型的計畫指南。

下列批次檔會執行這三個步驟：

--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
    FROM Production.ProductModel pm 
    INNER JOIN Production.ProductInventory pi 
        ON pm.ProductModelID = pi.ProductID 
    WHERE pi.ProductID = 101 
    GROUP BY pi.ProductID, pi.Quantity 
    HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

現在計畫指南會套用到參數化成指定形式的所有查詢，但會包含不同的常數常值。