使用計劃指南指定查詢引數化行為

當 PARAMETERIZATION 資料庫選項設定為 SIMPLE 時，SQL Server 查詢優化器可以選擇引數化查詢。這意味著查詢中包含的任何文字值都用引數來替換。此過程稱為簡單引數化。SIMPLE 引數化生效後，將無法控制引數化哪些查詢，不引數化哪些查詢。不過，您可以通過將 PARAMETERIZATION 資料庫選項設定為 FORCED 來指定引數化資料庫中的所有查詢。此過程稱為強制引數化。

可以通過下列方式使用計劃指南來覆蓋資料庫的引數化行為：

當 PARAMETERIZATION 資料庫選項設定為 SIMPLE 時，您可以指定對某一類查詢嘗試執行強制引數化。可以通過在查詢的引數化表單上建立 TEMPLATE 計劃指南並在 sp_create_plan_guide 儲存過程中指定 PARAMETERIZATION FORCED 查詢提示來完成此操作。您可以將此種計劃指南看作只對某一類查詢（而不是所有查詢）啟用強制引數化的方法。

當 PARAMETERIZATION 資料庫選項設定為 FORCED 時，您可以指定對某一類查詢僅嘗試執行簡單引數化而非強制引數化。通過在查詢的強制引數化表單上建立 TEMPLATE 計劃指南，並在 sp_create_plan_guide 中指定 PARAMETERIZATION SIMPLE 查詢提示，可以執行此操作。

請考慮 AdventureWorks 資料庫的以下查詢：

 複製程式碼
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;作為資料庫管理員，您已確定不想對資料庫中的所有查詢啟用強制引數化。不過，您確實想避免所有語法上與前一個查詢相同而只是常量文字值不同的查詢的編譯開銷。換句話說，您想引數化該查詢，使此種查詢的查詢計劃可以再次使用。在此情況下，請完成下列步驟：

檢索查詢的引數化表單。獲取此值以用於 sp_create_plan_guide 的唯一安全方法是使用 sp_get_query_template 系統儲存過程。

請指定 PARAMETERIZATION FORCED 查詢提示以對查詢的引數化表單建立計劃指南。
重要提示：
作為引數化查詢的一部分，SQL Server 根據文字的值和大小，將資料型別分配給替換文字值的引數。傳遞給 sp_get_query_template 的 @stmt 輸出引數的常量文字值也發生同樣的過程。由於在 SQL Server 引數化查詢時，sp_create_plan_guide 的 @params 引數中指定的資料型別必須與此查詢中的資料型別匹配，因此您可能必須要建立多個計劃指南以涵蓋全部可能的查詢引數值。有關引數化查詢後 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 計劃指南進行匹配是不合格的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-503351/，如需轉載，請註明出處，否則將追究法律責任。