sp_create_plan_guide_from_handle (Transact-SQL)

適用於：SQL Server

從計劃快取中的查詢計劃建立一或多個計劃指南。 您可以使用這個預存程式來確保查詢優化器一律會針對指定的查詢使用特定的查詢計劃。 如需有關計畫指南的詳細資訊，請參閱 計畫指南。

Transact-SQL 語法慣例

語法

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

引數

[ @name = ] N'name'

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

[ @plan_handle = ] plan_handle

識別計劃快取中的批次。 @plan_handle為 varbinary（64），沒有預設值。 您可以從動態管理檢視sys.dm_exec_query_stats取得@plan_handle。

[ @statement_start_offset = ] statement_start_offset

識別語句在指定 之@plan_handle批次內的起始位置。 @statement_start_offset為 int，預設值為 NULL。

語句位移會對應至statement_start_offset動態管理檢視sys.dm_exec_query_stats中的數據行。

當指定或未指定語句位移時 NULL ，會使用指定計劃句柄的查詢計劃，針對批次中的每個語句建立計劃指南。 產生的計劃指南相當於使用 USE PLAN 查詢提示強制使用特定計劃的計劃指南。

備註

無法為所有語句類型建立計劃指南。 如果無法為批次中的語句建立計劃指南，預存程式會忽略 語句，並繼續在批次中的下一個語句。 如果在相同批次中多次發生語句，則會啟用最後一次出現的計劃，並停用語句先前的計劃。 如果批次中沒有任何語句可用於計劃指南，則會引發錯誤 10532，且語句失敗。 建議您一律從 sys.dm_exec_query_stats 動態管理檢視取得計劃句柄，以協助避免發生此錯誤。

重要

sp_create_plan_guide_from_handle 根據計劃快取中顯示的計劃，建立計劃指南。 這表示批次文字、Transact-SQL 語句和 XML 執行程式表是從計劃快取取得的逐字元（包括任何傳遞至查詢的常值）到產生的計劃指南。 這些文字字串可以包含敏感性資訊，然後儲存在資料庫的元數據中。 具有適當許可權的使用者可以使用 SQL Server Management Studio 中的目錄檢視和 [計劃指南屬性] 對話框來檢視此資訊sys.plan_guides。 為了確保不會透過計劃指南披露敏感性資訊，建議您檢閱從計劃快取建立的計劃指南。

為查詢計劃內的多個語句建立計劃指南

如同 sp_create_plan_guide， sp_create_plan_guide_from_handle 會從計劃快取中移除目標批次或模組的查詢計劃。 這樣做可確保所有使用者都開始使用新的計劃指南。 在單一查詢計劃中建立多個語句的計劃指南時，您可以藉由在明確交易中建立所有計劃指南，來延後從快取中移除計劃。 這個方法可讓計劃保留在快取中，直到交易完成，並建立每個指定語句的計劃指南。 請參閱範例 B。

權限

需要 VIEW SERVER STATE 權限。 此外，使用 所建立 sp_create_plan_guide_from_handle的每個計劃指南都需要個別許可權。 建立類型的 OBJECT 計劃指南需要 ALTER 參考對象的許可權。 建立類型的 SQL 計劃指南，或 TEMPLATE 需要 ALTER 目前資料庫的許可權。 若要判斷將建立的計劃指南類型，請執行下列查詢：

SELECT cp.plan_handle,
    sql_handle,
    st.text,
    objtype
FROM sys.dm_exec_cached_plans AS cp
INNER JOIN sys.dm_exec_query_stats AS qs
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;

在包含您建立計劃指南之語句的數據列中，檢查 objtype 結果集中的數據行。 的值 Proc 表示計劃指南的類型為 OBJECT。 其他值，例如 Ad hoc 或 Prepared 表示計劃指南的類型為 SQL。

範例

A. 從計劃快取中的查詢計劃建立計劃指南

下列範例會從計劃快取指定查詢計劃，為單 SELECT 一語句建立計劃指南。 此範例一開始會執行將建立計劃指南的簡單 SELECT 語句。 使用和 sys.dm_exec_text_query_plan 動態管理檢視來檢查sys.dm_exec_sql_text此查詢的計劃。 然後，在與查詢相關聯的計劃快取中指定查詢計劃，以針對查詢建立計劃指南。 範例中的最終語句會驗證計劃指南是否存在。

USE AdventureWorks2022;
GO

SELECT WorkOrderID,
    p.Name,
    OrderQty,
    DueDate
FROM Production.WorkOrder AS w
INNER JOIN Production.Product AS p
    ON w.ProductID = p.ProductID
WHERE p.ProductSubcategoryID > 4
ORDER BY p.Name, DueDate;
GO

-- Inspect the query plan by using dynamic management views.
SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(
    qs.plan_handle, qs.statement_start_offset,
    qs.statement_end_offset
) AS qp
WHERE TEXT LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO

-- Create a plan guide for the query by specifying the query plan in the plan cache.
DECLARE @plan_handle VARBINARY(64);
DECLARE @offset INT;

SELECT @plan_handle = plan_handle,
    @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(
    qs.plan_handle, qs.statement_start_offset,
    qs.statement_end_offset
) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle @name = N'Guide1',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO

-- Verify that the plan guide is created.
SELECT *
FROM sys.plan_guides
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO

B. 建立多語句批次的多個計劃指南

下列範例會為多語句批次內的兩個語句建立計劃指南。 計劃指南是在明確交易內建立，因此在建立第一個計劃指南之後，不會從計劃快取中移除批次的查詢計劃。 此範例會從執行多重語句批次開始。 批次的計劃會使用動態管理檢視來檢查。 會傳回批次中每個語句的數據列。 然後，藉由指定 @statement_start_offset 參數，為批次中的第一個和第三個語句建立計劃指南。 範例中的最後一個語句會驗證計劃指南是否存在。

USE AdventureWorks2022;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO

相關內容

• 資料庫引擎 預存程式 （Transact-SQL）
• sys.dm_exec_query_stats (Transact-SQL)
• 計畫指南
• sp_create_plan_guide (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)
• sys.dm_exec_text_query_plan （Transact-SQL）
• sp_control_plan_guide (Transact-SQL)