sp_create_plan_guide_from_handle (Transact-SQL)

適用於:SQL Server

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

Transact-SQL 語法慣例

語法

sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'  
    , [ @plan_handle = ] plan_handle  
    , [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]  

引數

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

[ @plan_handle = ] plan_handle
識別計畫快取中的批次。 plan_handle 為 varbinary(64)您可以從動態管理檢視sys.dm_exec_query_stats取得 plan_handle

[ @statement_start_offset = ] { statement_start_offset |Null } ]
識別語句在指定 之plan_handle 批次內的起始位置。 statement_start_offset int ,預設值為 Null。

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

指定 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  
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。 其他值,例如 AdHocPrepared 表示計劃指南的類型為 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   
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

另請參閱

Database Engine 預存程式 (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)