sp_create_plan_guide (Transact-SQL)

發行項
12/15/2008

更新： 2007 年 9 月 15 日

建立將查詢提示關聯於資料庫中查詢的計劃指南。如需有關計劃指南的詳細資訊，請參閱＜使用計劃指南對已部署應用程式中的查詢進行最佳化＞。

語法

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' | NULL }

引數

[ @name= ] N'plan_guide_name'
指定用來識別計劃指南的名稱。計劃指南名稱的範圍會達到目前的資料庫。plan_guide_name 必須符合識別碼的規則，且開頭不能是數字符號 (#)。

[ @stmt= ] N'statement_text'
這是建立計劃指南所針對的 Transact-SQL 陳述式。當 SQL Server 查詢最佳化工具辨識出符合 statement_text 的查詢時，plan_guide_name 就會生效。若要成功建立計劃指南，statement_text 必須出現在 @type、@module_or_batch 及 @params 參數所指定的內容中。

statement_text 的提供方式，必須允許 SQL Server 使其符合 @module_or_batch 和 @params 所識別之批次或模組內提供的對應陳述式。在 SQL Server 嘗試這項符合作業之前 (空白、註解及關鍵字的大小寫不重要)，statement_text 會先轉換為標準內部格式。如需詳細資訊，請參閱「備註」一節。statement_text 的大小僅受到伺服器的可用記憶體所限制。

[ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
這是 statement_text 所在實體的類型。這指定 statement_text 要與 plan_guide_name 相符的內容。
- OBJECT
  指出 statement_text 會出現在目前資料庫中 Transact-SQL 預存程序、純量函數、多重陳述式資料表值函數或 Transact-SQL DML 觸發程序的內容中。
- SQL
  指出 statement_text 出現在可以透過任何機制提交給 SQL Server 之獨立陳述式或批次的內容中。由 Common Language Runtime (CLR) 物件或擴充預存程序提交或利用 EXEC N'sql_string' 來提交的 Transact-SQL 陳述式，在伺服器上會被當作批次來處理，因此，應被識別為 @type ='SQL'。如果指定了 SQL，就不能在 @hints 參數中指定 PARAMETERIZATION { FORCED | SIMPLE } 查詢提示。
- TEMPLATE
  指出計劃指南適用於任何查詢，這些查詢會參數化為 statement_text 中表示的格式。如果指定了 TEMPLATE，在 @hints 參數中只能指定 PARAMETERIZATION { FORCED | SIMPLE } 查詢提示。如需有關 TEMPLATE 計劃指南的詳細資訊，請參閱＜使用計劃指南指定查詢參數化行為＞。

[ @module_or_batch = ] { N'[ schema_name**.] object_name'** | N'batch_text' | NULL }
指定 statement_text 所在物件的名稱，或 statement_text 所在的批次文字。批次文字不能包含 USE database 陳述式。

若要計劃指南與從應用程式提交的批次相符，batch_text 的提供格式必須與其提交給 SQL Server 的格式逐字元相同。不會執行內部轉換來促成這個相符項。如需詳細資訊，請參閱「備註」一節。

[schema_name.]object_name 指定包含 statement_text 的 Transact-SQL 預存程序、純量函數、多重陳述式資料表值函數，或 Transact-SQL DML 觸發程序的名稱。如果未指定 schema_name ，則 schema_name 會使用目前使用者的結構描述。如果指定 NULL 且 @type='SQL'，則 @module_or_batch 值會設成 @stmt 值。如果 @type='TEMPLATE'，則 @module_or_batch 必須是 NULL。

[ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
指定內嵌於 statement_text 中所有參數的定義。只有在下列兩者之一為真時，@params 才適用：
- @type='SQL' 或 'TEMPLATE'.如果是 'TEMPLATE'，@params 不可以是 NULL。
- statement_text 是利用 sp_executesql 提交且指定了 @params 參數的值，或者在將它參數化之後，SQL Server 內部會提交陳述式。來自資料庫 API (包括 ODBC、OLE DB 及 ADO.NET) 參數化查詢的提交，SQL Server 視同對 sp_executesql 或對 API 伺服器資料指標常式的呼叫；因此，也可以由 SQL 或 TEMPLATE 計劃指南進行比對。如需有關參數化和計劃指南的詳細資訊，請參閱＜SQL Server 如何比對計劃指南與查詢＞。
提供 @parameter_name data_type 的格式必須與利用 sp_executesql 提交給 SQL Server，或參數化之後內部提交的格式完全相同。如需詳細資訊，請參閱「備註」一節。如果批次不包含參數，就必須指定 NULL。@params 的大小僅受到可用的伺服器記憶體所限制。

[@hints = ] { **N'**OPTION (query_hint [ ,...n ] )' | NULL }
指定 OPTION 子句來附加至與 @stmt 相符的查詢。@hints 語法上必須與 SELECT 陳述式中的 OPTION 子句相同，且可包含任何有效順序的查詢提示。NULL 表示沒有 OPTION 子句。如需詳細資訊，請參閱＜OPTION 子句 (Transact-SQL)＞。

備註

sp_create_plan_guide 的引數必須依照顯示順序提供。當您提供 sp_create_plan_guide 的參數值時，必須明確指定所有的參數名稱，或是完全不指定。例如，如果指定了 @name =，您也必須指定 @stmt =、@type = 等等。同樣地，如果省略 @name =，而只提供參數值，您也必須省略其餘參數名稱，只提供它們的值。引數名稱僅供描述用途，以協助您了解語法。SQL Server 不會驗證指定的參數名稱是否與使用該名稱之位置中的參數名稱相符。

對特定 @module_or_batch 和 @stmt 組合，只能建立一份計劃指南。

如果 @module_or_batch 值參考的預存程序、函數或 DML 觸發程序指定了 WITH ENCRYPTION 子句或是暫時的，您就不能為這個值建立 OBJECT 類型的計劃指南。

試圖卸除或修改計劃指南所參考的函數、預存程序或 DML 觸發程序，不論是已啟用或已停用，都會造成錯誤。嘗試卸除定義了觸發程序且被計劃指南參考的資料表也會造成錯誤。

附註：
雖然在任何版本都可以看見計劃指南，但計劃指南僅適用於 SQL Server Standard、Developer、Evaluation 與 Enterprise 版本。您也可以附加資料庫，以便在任何版本中包含計劃指南。當您將資料庫還原或附加至 SQL Server 2008 的升級版本時，計劃指南仍維持不變。您應該在執行伺服器升級後確認每個資料庫中計劃指南的符合度。

雖然在任何版本都可以看見計劃指南，但計劃指南僅適用於 SQL Server Standard、Developer、Evaluation 與 Enterprise 版本。您也可以附加資料庫，以便在任何版本中包含計劃指南。當您將資料庫還原或附加至 SQL Server 2008 的升級版本時，計劃指南仍維持不變。您應該在執行伺服器升級後確認每個資料庫中計劃指南的符合度。

計劃指南的比對作業需求

若要指定 @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 類型的計劃指南，會從該資料庫內的計劃快取移除所有單一陳述式的批次。

權限

若要建立類型為 OBJECT (藉由指定 @type='OBJECT') 的計劃指南，需要參考物件的 ALTER 權限。若要建立類型為 SQL 或 TEMPLATE 的計劃指南，需要目前資料庫的 ALTER 權限。

範例

A. 為預存程序中的查詢建立類型為 OBJECT 的計劃指南

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

以下是預存程序：

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';

B. 為獨立的查詢建立類型為 SQL 的計劃指南

下列範例會建立計劃指南來與使用 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)';

C. 為參數化格式的查詢建立類型為 TEMPLATE 的計劃指南

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

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

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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 型計劃指南中使用的詳細資訊，請參閱＜設計參數化查詢的計劃指南＞。

D. 建立藉由使用 API 資料指標要求提交查詢的計劃指南

計劃指南可以比對從 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 AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @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 BETWEEN @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 查詢提示＞。

請參閱

參考

sp_control_plan_guide (Transact-SQL)
sys.plan_guides
Database Engine 預存程序 (Transact-SQL)
系統預存程序 (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本	歷程記錄
2005 年 12 月 5 日	新增內容：新增的 PARAMETERIZATION { FORCED \| SIMPLE } 查詢提示不能指定用於 SQL 計劃指南。在「備註」一節中釐清語法順序與一致性的方針。變更的內容：釐清 OBJECT 計劃指南不能參考加密或暫存物件。
2007 年 9 月 15 日	更新的內容：釐清當 @type = ‘SQL’ 且 @module_or_batch 設定為 NULL 時，對於 statement_text 的計劃指南比對作業需求。新增有關建立計劃指南對計劃快取之影響的資訊。

2005 年 12 月 5 日

新增內容：

新增的 PARAMETERIZATION { FORCED | SIMPLE } 查詢提示不能指定用於 SQL 計劃指南。
在「備註」一節中釐清語法順序與一致性的方針。

變更的內容：

釐清 OBJECT 計劃指南不能參考加密或暫存物件。

2007 年 9 月 15 日

更新的內容：

釐清當 @type = ‘SQL’ 且 @module_or_batch 設定為 NULL 時，對於 statement_text 的計劃指南比對作業需求。
新增有關建立計劃指南對計劃快取之影響的資訊。

共用方式為