sp_create_plan_guide (Transact-SQL)

若要讓指定 @type = 'SQL' 或 @type = 'TEMPLATE' 的計畫指南順利地與查詢比對，則 batch_text 和 @parameter_name data_type [,...n ] 的值必須以和應用程式所提交之對應項目完全相同的格式來提供。這表示您提供的批次文字必須和 SQL Server 編譯器所收到的完全相同。若要擷取實際的批次和參數文字，您可以使用 SQL Server Profiler。如需詳細資訊，請參閱＜使用 SQL Server Profiler 建立及測試計畫指南＞。

當 @type = 'SQL' 而且 @module_or_batch 設定為 NULL 時，@module_or_batch 的值就會設定為 @stmt 的值。這表示，提供的 statement_text 值必須與提交給 SQL Server 的值採用完全相同的格式 (逐字元)。不會執行內部轉換來簡化這個比對作業。

當 SQL Server 使 statement_text 的值符合 batch_text 和 @parameter_name data_type [,...n ] 時，或者，如果 @type = **'**OBJECT' 符合 object_name 內之對應查詢的文字，則不考量下列字串元素：

• 字串內部的空白字元 (定位字元、空格字元、歸位字元或換行字元)。

• 註解 (-- 或 /* */)。

• 行尾的分號。

例如，SQL Server 可以使 statement_text 字串 N'SELECT * FROM T WHERE a = 10' 符合下列 batch_text：

N'SELECT *

FROM T

WHERE a=10'

不過，相同的字串不會與這個 batch_text 相符：

N'SELECT * FROM T WHERE b = 10'

SQL Server 會忽略第一項查詢內的歸位字元、換行字元和空白字元。在第二項查詢中，順序 WHERE b = 10 的解譯與 WHERE a = 10 不同。比對作業會區分大小寫以及區分腔調字 (即使資料庫定序不區分大小寫亦然)，關鍵字例外，它不區分大小寫。比對作業不區分縮寫格式的關鍵字。例如，關鍵字 EXECUTE、EXEC 和 execute 被視為相同。

如需有關計畫指南如何與查詢相符的詳細資訊，請參閱＜使用計畫指南對已部署應用程式中的查詢進行最佳化＞。

針對某個模組建立計畫指南時，就會從計畫快取中移除該模組的查詢計畫。針對某個批次建立 OBJECT 或 SQL 類型的計畫指南時，就會移除具有相同雜湊值之批次的查詢計畫。建立 TEMPLATE 類型的計畫指南時，就會從該資料庫內部的計畫快取中移除所有單一陳述式批次。

下列範例會建立與應用程式型預存程序的內容中執行的查詢相符的計畫指南，並將 OPTIMIZE FOR 提示套用至這項查詢。

以下是預存程序：

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

以下是預存程序中查詢所建立的計畫指南：

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

下列範例會建立計畫指南來與使用 sp_executesql 系統預存程序的應用程式所提交批次中的查詢比對。

批次如下：

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

若要防止這項查詢產生平行執行計畫，請建立下列計畫指南：

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @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)';

下列範例會建立與參數化為特定格式的任何查詢相符的計畫指南，並導引 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 型計畫指南中使用的詳細資訊，請參閱＜設計參數化查詢的計畫指南＞。

計畫指南可以比對從 API 伺服器資料指標常式提交的查詢。這些常式包括 sp_cursorprepare、sp_cursorprepexec 和 sp_cursoropen。使用 ADO、OLE DB 和 ODBC API 的應用程式經常會利用 API 伺服器資料指標與 SQL Server 互動。如需詳細資訊，請參閱＜API 伺服器資料指標＞。透過檢視 RPC:Starting Profiler 追蹤事件，您可以看見 SQL Server Profiler 追蹤中的 API 伺服器資料指標常式的叫用。

假設下列資料出現在您想以計畫指南調整之查詢的 RPC:Starting Profiler 追蹤事件中：

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @P1 AND <= @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

您注意到對 sp_cursorprepexec 的呼叫中 SELECT 查詢的計畫，是使用合併聯結，但您卻想要使用雜湊聯結。將利用 sp_cursorprepexec 提交的查詢參數化，包括查詢字串和參數字串。透過使用與對 sp_cursorprepexec 呼叫中完全相同的查詢和參數字串 (逐字元顯示)，您可以建立下列計畫指南來變更計畫的選擇。

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

這個應用程式對這項查詢的後續執行，將受到這份計畫指南的影響，且會利用雜湊聯結來處理查詢。

如需有關如何使用以資料指標提交之查詢的計畫指南中 USE PLAN 查詢提示的資訊，請參閱＜在含有資料指標的查詢上使用 USE PLAN 查詢提示＞。

下列範例會針對簡單的特定 SQL 陳述式建立計畫指南。直接以 @hints 參數指定查詢的 XML 執行程序表，就可以在計畫指南中提供此陳述式所需的查詢計畫。此範例會先執行 SQL 陳述式以便在計畫快取中產生計畫。基於此範例的目的，假設產生的計畫為所需的計畫，而且不需要額外調整查詢。查詢的 XML 執行程序表會透過查詢 sys.dm_exec_query_stats、sys.dm_exec_sql_text 和 sys.dm_exec_text_query_plan 動態管理檢視取得，而且會被指派給 @xml_showplan 變數。然後，系統會將 @xml_showplan 變數傳遞到 @hints 參數的 sp_create_plan_guide 陳述式中。或者，您可以使用 sp_create_plan_guide_from_handle 預存程序，從計畫快取的查詢計畫中建立計畫指南。

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints =@xml_showplan;
GO