共用方式為


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

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

  • 查詢是用 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)';

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