sp_create_plan_guide （Transact-SQL）

適用於：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric 中的 SQL 資料庫

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

Transact-SQL 語法慣例

語法

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

引數

[ @name = ] N'name'

計劃指南的名稱。 @name為 sysname，不含預設值，最大長度為 124 個字元。 計劃指南名稱的範圍設定為目前的資料庫。 @name必須符合標識符的規則，且無法從數位元號 （#） 開始。

[ @stmt = ] N'stmt'

要為其建立計劃指南的 Transact-SQL 語句。 @stmt為 nvarchar（max），預設值為 NULL。 當 SQL Server 查詢優化器辨識符合@stmt的查詢時，@name就會生效。 若要成功建立計劃指南，@stmt必須出現在@type、@module_or_batch和@params參數所指定的內容中。

必須提供@stmt，讓查詢優化器能夠比對查詢優化器與@module_or_batch和@params所識別之批次或模組內的對應語句。 如需詳細資訊，請參閱備註一節。 @stmt的大小僅受限於伺服器的可用記憶體。

[ @type = ] { N'OBJECT' |N'SQL' |N'TEMPLATE' }

@stmt出現的實體類型。 這會指定要比對@stmt到@name的內容。 @type為 nvarchar（60），而且可以是下列其中一個值：

• OBJECT

  指出@stmt出現在目前資料庫中 Transact-SQL 預存程式、純量函數、多重語句數據表值函式或 Transact-SQL DML 觸發程式的內容中。

• SQL

  指出@stmt出現在可透過任何機制提交至 SQL Server 的獨立語句或批次內容中。 Common Language Runtime （CLR） 對象或擴充預存程式EXECUTE N'<sql_string>'所提交的 Transact-SQL 語句會在伺服器上以批次的形式處理，因此，應該識別為 的@typeSQL。 如果SQL已指定，則無法在 @hintsPARAMETERIZATION { FORCED | SIMPLE }指定查詢提示。

• TEMPLATE

  指出計劃指南適用於任何參數化為 @stmt 中所指示表單的查詢。如果TEMPLATE已指定，則只能在 PARAMETERIZATION { FORCED | SIMPLE } @hints 參數中指定查詢提示。 如需計劃指南的詳細資訊 TEMPLATE ，請參閱 使用計劃指南指定查詢參數化行為。

[ @module_or_batch = ] { N' [ schema_name。 ] object_name' |N'batch_text' }

指定@stmt出現的物件名稱，或@stmt出現的批次文字。 @module_or_batch為 nvarchar（max），預設值為 NULL。 批次文字不能包含 USE <database> 語句。

若要讓計劃指南符合從應用程式提交的批次， @module_or_batch 必須以與提交至 SQL Server 相同的格式字元字元。 不會執行內部轉換來簡化這個比對作業。 如需詳細資訊，請參閱備註一節。

[ <schema_name>. ] <object_name>指定包含@stmt的 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 ]'

指定內嵌於@stmt的所有參數定義。 @params 為 nvarchar（max），預設值為 NULL。 @params只有在下列任一選項成立時才適用：

• @type 為 SQL 或 TEMPLATE。 如果 TEMPLATE為 ， 則@params 不得為 NULL。

• @stmt會使用 sp_executesql 來提交，並指定@params參數的值，或 SQL Server 在參數化後於內部提交語句。 從資料庫 API 提交參數化查詢（包括 ODBC、OLE DB 和 ADO.NET）會顯示為對 SQL Server 的呼叫 sp_executesql 或 API 伺服器數據指標例程;因此，它們也可以由 SQL 或 TEMPLATE 計劃指南比對。

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

[ @hints = ] { N'OPTION （ query_hint [ ， ...n ] ]' |N'XML_showplan' }

@hints為 nvarchar（max），預設值為 NULL。

• OPTION ( <query_hint> [ , ...n ] )

  指定要OPTION附加至符合@stmt之查詢的子句。 @hints在語法上必須與 語句中的 OPTION 子句相同SELECT，而且可以包含任何有效的查詢提示序列。

• <XML_showplan>'

  要套用為提示之 XML 格式的查詢計劃。

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

• NULL

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

備註

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

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

無法針對OBJECT值建立型別計劃指南，該觸發程式會指定WITH ENCRYPTION子句或暫時性。

試圖卸除或修改計畫指南所參考的函數、預存程序或 DML 觸發程序，不論是已啟用或已停用，都會造成錯誤。 嘗試卸除計劃指南所參考的觸發程序數據表，也會導致錯誤。

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

計劃指南比對需求

針對指定 的計劃指南，SQL和TEMPLATE 必須以與應用程式所提交之對應專案完全相同的格式提供。 這表示您必須提供批次文字，就像 SQL Server 編譯程式收到一樣。 若要擷取實際的批次和參數文字，您可以使用 SQL Server Profiler。 如需詳細資訊，請參閱 使用 SQL Server Profiler 建立及測試計劃指南。

當 @type 是 且 @module_or_batch 設定為 時，@module_or_batch 的值SQL 的值。NULL這表示@stmt的值必須以與提交至 SQL Server 完全相同的格式、字元代表字元。 不會執行內部轉換來簡化這個比對作業。

當 SQL Server 比對 @stmt 的值與 @module_or_batch 和 @params [， ...n ]，如果 @type 為 OBJECT，則不會考慮下列字串元素至內<object_name>對應查詢的文字：

• 字串內的空白元（製表元、空格、歸位字元或換行字元）
• 註注 （-- 或 /* */）
• 尾端分號

例如，SQL Server 可以將@stmt字串與下列N'SELECT * FROM T WHERE a = 10'相符：

 N'SELECT *
 FROM T
 WHERE a = 10'

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

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

以下是在預存程式中查詢上建立的計劃指南：

EXECUTE 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;

若要防止在此查詢上產生平行執行計劃，請建立下列計劃指南：

EXECUTE 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;

以下是查詢參數化形式的計劃指南：

EXECUTE 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 AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE 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;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
    @stmt, N'TEMPLATE', NULL,
    @params, N'OPTION(PARAMETERIZATION FORCED)';

重要

傳送到 @stmt 之 sp_get_query_template 參數中的常數常值，可能會影響針對取代常值的參數所選擇的資料類型。 這會影響計畫指南的比對作業。 您可能必須建立多個計劃指南來處理不同的參數值範圍。

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

計劃指南可以比對從 API 伺服器數據指標例程提交的查詢。 這些例程包括 sp_cursorprepare、 sp_cursorprepexec與 sp_cursoropen。 使用 ADO、OLE DB 和 ODBC API 的應用程式經常使用 API 伺服器資料指標與 SQL Server 互動。 您可以檢視分析工具追蹤事件，查看 SQL Server Profiler 追蹤中 API 伺服器數據指標例程的 RPC:Starting 叫用。

假設下列資料出現在您想要使用計劃指南微調的查詢分析工具追蹤事件中 RPC:Starting ：

DECLARE @p1 AS INT;
SET @p1 = -1;

DECLARE @p2 AS INT;
SET @p2 = 0;

DECLARE @p5 AS INT;
SET @p5 = 4104;

DECLARE @p6 AS INT;
SET @p6 = 8193;

DECLARE @p7 AS INT;
SET @p7 = 0;

EXECUTE 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;

您注意到呼叫 SELECT 中的查詢計劃sp_cursorprepexec是使用合併聯結，但您想要使用哈希聯結。 使用 sp_cursorprepexec 提交的查詢會參數化，包括查詢字串和參數位符串。 您可以建立下列計劃指南，藉由使用查詢和參數位符串，在呼叫 sp_cursorprepexec時，使用完全相同的查詢和參數位符串來變更計劃的選擇。

EXECUTE 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 AS 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;%'
);

EXECUTE 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）
• 資料庫引擎 預存程式 （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）