共用方式為

  • 發行項

使用計畫指南指定查詢參數化行為

當 PARAMETERIZATION 資料庫選項設定為 SIMPLE 時，SQL Server 查詢最佳化工具可能會選擇參數化查詢。這意謂著任何包含在查詢中的常值將會以參數替代。此處理序稱為簡易參數化。當 SIMPLE 參數化生效時，您無法控制哪些查詢要參數化以及哪些查詢不要參數化。但是，您可以將 PARAMETERIZATION 資料庫選項設定為 FORCED，藉以指定要參數化資料庫中的所有查詢。此處理序稱為強制參數化。

您可以透過下列方式使用計畫指南來覆寫資料庫的參數化行為：

  • 當 PARAMETERIZATION 資料庫選項設定為 SIMPLE 時，您可以指定在特定的查詢類別強制參數化。作法是在查詢的參數化格式上建立 TEMPLATE 計畫指南，並在 sp_create_plan_guide 預存程序中指定查詢提示。您可以考慮將此類的計畫指南做為只在某類別的查詢 (而不是所有的查詢) 中啟用強制參數化的方式。

  • 當 PARAMETERIZATION 資料庫選項設定為 FORCED 時，您可以指定在特定的查詢類別只嘗試簡單參數化，但不嘗試強制參數化。若要這麼做，請在強制參數化形式的查詢建立一個 TEMPLATE 計畫指南，然後在 sp_create_plan_guide 中指定 PARAMETERIZATION SIMPLE 查詢提示。

請考慮在 AdventureWorks2008R2 資料庫中的下列查詢：

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

身為資料庫管理員，您已決定不要啟用資料庫中所有查詢的強制參數化。不過，您想要對在語法上與上一個查詢相等，但僅其常值不同的所有查詢，省下其編譯成本。換句話說，您想要參數化查詢，這樣才能拒絕這類的查詢計畫。在此情況下，請完成下列步驟：

  1. 擷取查詢的參數化格式。取得此值供 sp_create_plan_guide 使用的唯一安全方法，是使用 sp_get_query_template 系統預存程序。

  2. 在查詢的參數化格式上建立計畫指南，以指定 PARAMETERIZATION FORCED 查詢提示。

    重要事項重要事項

    在參數化查詢時，SQL Server 會指派資料類型給取代常值的參數，端視常值的值與大小而定。同樣的處理方式也會發生在傳遞給 sp_get_query_template 的 @stmt 輸出參數之常值上。由於 sp_create_plan_guide 的 @params 引數中所指定的資料類型，在 SQL Server 參數化該查詢時必須符合查詢的資料類型，因此您必須建立一或多個計畫指南以涵蓋完整範圍的查詢可能參數值。如需 SQL Server 在參數化查詢後指派給參數之資料類型的詳細資訊，請參閱＜強制參數化＞。

下列指令碼可用以擷取參數化查詢，並在該查詢上建立計畫指南：

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 AS pm 
      INNER JOIN Production.ProductInventory AS 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;
EXEC sp_create_plan_guide 
    N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

相同的，在已經啟用強制參數化的資料庫中，您可以確定讓範例查詢以及其他句法相同的項目 (常數常值除外)，都根據簡單參數化規則加以參數化。作法是指定 OPTION 子句中的 PARAMETERIZATION SIMPLE，而不是 PARAMETERIZATION FORCED。

[!附註]

TEMPLATE 計畫指南可使陳述式配合僅由單一陳述式組成的批次中所提交的查詢。TEMPLATE 計畫指南無法配合多重陳述式批次中的陳述式。