了解計畫指南

假設下列採用 @Country_region 參數的預存程序存在針對 AdventureWorks2008R2 資料庫所部署的資料庫應用程式中：

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
        Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country_region
END;

假設此預存程序已針對 @Country_region = N'AU' (澳洲) 編譯及最佳化。不過，由於來自澳洲的銷售訂單相當少，因此當查詢在多個銷售訂單上使用國家 (地區) 的參數值執行時，效能就會降低。由於大部分的銷售訂單都是來自美國，所以針對 @Country_region = N'US' 所產生的查詢計畫可能會比 @Country_region 參數的所有可能值具有更好的執行效能。

您可以修改預存程序並將 OPTIMIZE FOR 查詢提示加入查詢以處理此問題。不過，因為預存程序是在已部署的應用程式中，所以您無法直接修改應用程式的程式碼。您只能在 AdventureWorks2008R2 資料庫中建立下列計畫指南。

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'

執行在 sp_create_plan_guide 陳述式中指定的查詢時，在最佳化前會先修改查詢以包含 OPTIMIZE FOR (@Country = N''US'') 子句。

SQL 計畫指南會套用至獨立 Transact-SQL 陳述式和批次。這些陳述式通常是由應用程式使用 sp_executesql 系統預存程序進行提交。例如，請考慮下列獨立批次：

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

若要防止這項查詢產生平行執行計畫，請建立下列計畫指南並將 @hints 參數中的 MAXDOP 查詢提示設定為 1。

sp_create_plan_guide 
@name = N'Guide2', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)';

重要事項
針對 sp_create_plan guide 陳述式的 @module_or_batch 與 @params 引數所提供的值必須符合在實際查詢中所提交的對應文字。如需詳細資訊，請參閱＜sp_create_plan_guide (Transact-SQL)＞和＜使用 SQL Server Profiler 建立及測試計畫指南＞。

當 PARAMETERIZATION 資料庫選項 SET 為 FORCED 時，或是當建立 TEMPLATE 計畫指南以指定要參數化的查詢類別時，SQL 計畫指南也可在參數化為相同形式的查詢上建立 SQL 計畫指南。如需詳細資訊，請參閱＜設計參數化查詢的計畫指南＞。

TEMPLATE 計畫指南可用以覆寫特定查詢形式的參數化行為。您可以在下列其中一種情況下建立 TEMPLATE 計畫指南：

• PARAMETERIZATION 資料庫選項設定成 FORCED，但有一些要根據簡單參數化規則編譯的查詢。

• PARAMETERIZATION 資料庫選項設定成 SIMPLE (預設值)，但是要在某個查詢類別嘗試強制參數化。

如需詳細資訊，請參閱＜使用計畫指南指定查詢參數化行為＞。

下列範例會建立與參數化為特定格式的任何查詢相符的計畫指南，並導引 SQL Server 以強制執行查詢的參數化作業。下列兩項查詢在語法上相同，不同的只是兩者的常數值。

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

以下是參數化格式查詢的計畫指南：

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

在前一個範例中，@stmt 參數的值是參數化格式的查詢。取得這個值以便於 sp_create_plan_guide 中使用的唯一可靠方法，是利用 sp_get_query_template 系統預存程序。下列指令碼可以用來取得參數化查詢，之後再建立參數化查詢的計畫指南。

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

重要事項
傳送到 sp_get_query_template 之 @stmt 參數中的常數常值，可能會影響針對取代常值的參數所選擇的資料類型。這會影響計畫指南的比對作業。您可能需要建立一份以上的計畫指南，來處理不同的參數值範圍。

您也可以同時使用 TEMPLATE 計畫指南與 SQL 計畫指南。例如，您可以建立 TEMPLATE 計畫指南以確定參數化查詢類別。接著就可以在該查詢的參數化形式上建立 SQL 計畫指南。