sp_create_plan_guide_from_handle (Transact-SQL)

發行項
06/18/2013

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

語法

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)。 plan_handle 可從 sys.dm_exec_query_stats 動態管理檢視取得。
[ @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 執行程序表都是從計畫快取逐字元取得 (包括傳遞至查詢的任何常值)，並放入所產生的計畫指南中。這些文字字串可能包含之後會儲存到資料庫之中繼資料中的機密資訊。擁有適當權限的使用者可以使用 sys.plan_guides 目錄檢視與 SQL Server Management Studio 中的 [計畫指南屬性] 對話方塊檢視這個資訊。為確保機密資訊不會透過計畫指南而遭到揭露，建議您檢閱從計畫快取建立的計畫指南。

sp_create_plan_guide_from_handle 會根據出現在計畫快取中的計畫建立計畫指南。也就是說，批次文字、Transact-SQL 陳述式與 XML 執行程序表都是從計畫快取逐字元取得 (包括傳遞至查詢的任何常值)，並放入所產生的計畫指南中。這些文字字串可能包含之後會儲存到資料庫之中繼資料中的機密資訊。擁有適當權限的使用者可以使用 sys.plan_guides 目錄檢視與 SQL Server Management Studio 中的 [計畫指南屬性] 對話方塊檢視這個資訊。為確保機密資訊不會透過計畫指南而遭到揭露，建議您檢閱從計畫快取建立的計畫指南。

針對查詢計畫內的多個陳述式建立計畫指南

就像 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。諸如 AdHoc 或 Prepared 的其他值則表示計畫指南的類型為 SQL。

範例

A.從工作快取的查詢計畫建立計畫指南

下列範例會從計畫快取指定查詢計畫，藉以建立單一 SELECT 陳述式的計畫指南。範例一開始會執行簡單的 SELECT 陳述式，藉以建立計畫指南。此查詢的計畫會透過 sys.dm_exec_sql_text 和 sys.dm_exec_text_query_plan 動態管理檢視加以檢查。然後會在與該查詢相關聯之計畫快取中指定查詢計畫，便可建立該查詢的計畫指南。範例中的最後一個陳述式會確認該計畫指南是否存在。

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