sp_create_plan_guide (Transact-SQL)

發行項
08/21/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體

建立計劃指南，以將查詢提示或實際查詢計劃與資料庫中的查詢產生關聯。如需有關計畫指南的詳細資訊，請參閱計畫指南。

語法

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 ] )'   
                 | N'XML_showplan'  
                 | NULL }

引數

[ @name = ]N'plan_guide_name '
這是計劃指南的名稱。計劃指南名稱的範圍設定為目前的資料庫。 plan_guide_name 必須符合識別碼的規則，而且不能以數位記號（#）開頭。 plan_guide_name 的最大長度 為 124 個字元。

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

必須提供statement_text ，讓查詢最佳化工具能夠將它與@module_or_batch和@params所識別之批次或模組內提供的對應語句比對。如需詳細資訊，請參閱一節。 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）物件或擴充預存程式所提交的 Transact-SQL 語句，或使用 EXEC N'sql_string '，會在伺服器上以批次方式處理，因此，應該識別為 'SQL' @type = 。如果指定 SQL，查詢提示 PARAMETERIZATION { FORCED |無法在 @hints 參數中指定 SIMPLE }。

範本
指出計劃指南適用于任何參數化至statement_text 中所指示表單的 查詢。如果指定 TEMPLATE，則只有 PARAMETERIZATION { FORCED |您可以在 @hints 參數中指定 SIMPLE } 查詢提示。如需範本計劃指南的詳細資訊，請參閱使用計劃指南指定查詢參數化行為。

[@module_or_batch =]{ N'[ schema_name 。 ] object_name ' |N'batch_text ' |Null }
指定statement_text出現的物件 名稱，或statement_text出現的批次文字。 批次文字不能包含 USE 資料庫 語句。

若要讓計劃指南符合從應用程式提交的批次， batch_tex 不能以與提交至 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只有在下列任一項為 true 時才適用：

@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 計劃指南進行比對。

@parameter_name data_type必須以與在參數化之後于內部使用 sp_executesql 或提交至 SQL Server 完全相同的格式來提供。如需詳細資訊，請參閱＜備註＞一節。如果批次不包含參數，則必須指定 Null。 @params的大小只受限於可用的伺服器記憶體。

[@hints = ]{ N'OPTION （ query_hint [ ， ...n ] ]' |N'XML_showplan ' |Null }
N'OPTION （ query_hint [ ， ...n ]
指定要附加至符合@stmt之查詢的 OPTION 子句。@hints在語法上必須與 SELECT 語句中的 OPTION 子句相同，而且可以包含任何有效的查詢提示序列。

N'XML_showplan '
這是要套用為提示之 XML 格式的查詢計劃。

建議將 XML Showplan 指派給變數;否則，您必須在 Showplan 中逸出任何單引號，方法是在它們前面加上另一個單引號。請參閱範例 E.

NULL
表示查詢的 OPTION 子句中指定的任何現有提示都不會套用至查詢。如需詳細資訊，請參閱 OPTION 子句（Transact-SQL）。

備註

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

您可以針對相同的查詢和批次或模組，建立一個以上的 OBJECT 或 SQL 計畫指南。但是，在任何指定的時間內，只能啟用一個計畫指南。

無法針對參考預存程式、函式或 DML 觸發程式的@module_or_batch值建立 OBJECT 類型的計劃指南，該觸發程式會指定 WITH ENCRYPTION 子句或暫時性。

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

注意

並非每個 Microsoft SQL Server 版本都可使用計劃指南。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2016 版本支援的功能。在任何版本中都可以看到計畫指南。您也可以將包含計畫指南的資料庫附加到任何版本中。當您將資料庫還原或附加至 SQL Server 的升級版本時，計畫指南仍維持不變。在執行伺服器升級之後，您應該確認每個資料庫中計劃指南的可取性。

計劃指南比對需求

針對指定@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 類型的計劃指南，需要 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_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''))';

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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2022.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 常數常值值可能會影響為取代常值的參數選擇的資料類型。這會影響計畫指南的比對作業。您可能需要建立一份以上的計畫指南，來處理不同的參數值範圍。

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

計劃指南可以比對從 API 伺服器資料指標常式提交的查詢。這些常式包括sp_cursorprepare、sp_cursorprepexec和sp_cursoropen。使用 ADO、OLE DB 和 ODBC API 的應用程式經常使用 API 伺服器資料指標與 SQL Server 互動。您可以檢視 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 AS h INNER JOIN 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)';

應用程式後續執行此查詢將受到此計劃指南的影響，而雜湊聯結將用來處理查詢。

E. 從快取計畫取得 XML 執行程式表，以建立計劃指南

下列範例會針對簡單的特定 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 變數傳遞到 sp_create_plan_guide 參數的 @hints 陳述式中。或者，您可以使用 sp_create_plan_guide_from_handle 預存程序，從計畫快取的查詢計劃中建立計劃指南。

USE AdventureWorks2022;  
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

另請參閱

計畫指南
 sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
Database Engine 預存程式（Transact-SQL）
系統預存程序 (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template （Transact-SQL）