共用方式為

sp_create_plan_guide (Transact-SQL)

  • 發行項

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

適用於：SQL Server (SQL Server 2008 至目前版本)。

主題連結圖示 Transact-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) 物件或擴充預存程序來提交，或是利用 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 所在的批次文字。 批次文字不能包含 USEdatabase 陳述式。

    若要計畫指南與從應用程式提交的批次相符，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 計畫指南進行比對。

    提供 @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 ] )
      指定 OPTION 子句來附加至與 @stmt 相符的查詢。 @hints 語法上必須與 SELECT 陳述式中的 OPTION 子句相同，且可包含任何有效順序的查詢提示。

    • N'XML_showplan'
      要套用為提示之 XML 格式的查詢計畫。

      建議將 XML 執行程序表指派給變數；否則，您必須在單引號前加上另一個單引號，以免除執行程序表中的所有單引號。 請參閱範例 E。

    • NULL
      表示在查詢之 OPTION 子句中指定的任何現有提示沒有套用到查詢中。 如需詳細資訊，請參閱＜OPTION 子句 (Transact-SQL)＞。

備註

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

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

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

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

注意

並非每個 Microsoft SQL Server 版本都可使用計畫指南。如需 SQL Server 版本所支援功能的清單，請參閱＜SQL Server 2014 版本支援的功能＞。在任何版本中都可以看到計畫指南。您也可以將包含計畫指南的資料庫附加到任何版本中。當您將資料庫還原或附加至 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 AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.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 AdventureWorks2012.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2012.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 AdventureWorks2012.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2012.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 追蹤事件，您可以看見 SQL Server Profiler 追蹤中的 API 伺服器資料指標常式的叫用。

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

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 變數傳遞到 @hints 參數的 sp_create_plan_guide 陳述式中。 或者，您可以使用 sp_create_plan_guide_from_handle 預存程序，從計畫快取的查詢計畫中建立計畫指南。

USE AdventureWorks2012;
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)

概念

計畫指南