sp_create_plan_guide_from_handle (Transact-SQL)
适用范围：
SQL Server
从计划缓存中的查询计划创建一个或多个计划指南。 可以使用此存储过程来确保查询优化器始终为指定查询使用特定的查询计划。 有关计划指南的详细信息，请参阅 Plan Guides。
语法
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_offsetsys.dm_exec_query_stats动态管理视图中的列。
指定或未指定语句偏移量时 NULL ，将使用指定计划句柄的查询计划为批处理中的每个语句创建计划指南。 生成的计划指南等效于使用 USE PLAN 查询提示强制使用特定计划的计划指南。
注解
无法为所有语句类型创建计划指南。 如果无法为批处理中的语句创建计划指南，则存储过程将忽略该语句，并继续执行批处理中的下一个语句。 如果在同一批处理中多次出现同一语句，将启用最后一处语句的计划并禁用此语句以前的计划。 如果批处理中没有语句可用于计划指南，将产生错误 10532 并且语句将失败。 建议始终从 sys.dm_exec_query_stats 动态管理视图中获取计划句柄，以帮助避免出现此错误。
重要
sp_create_plan_guide_from_handle 根据计划缓存中显示的计划创建计划指南。 这意味着将批处理文本、Transact-SQL 语句和 XML Showplan 按字符（包括传递给查询的任何文本值）从计划缓存中提取到生成的计划指南中。 这些文本字符串可以包含敏感信息，然后存储在数据库的元数据中。 具有适当权限的用户可以使用 SQL Server Management Studio 中的 sys.plan_guides 目录视图和 “计划指南属性 ”对话框来查看此信息。 为了确保不会通过计划指南披露敏感信息，建议查看从计划缓存中创建的计划指南。
为查询计划中多个语句创建计划指南
例如 sp_create_plan_guide， sp_create_plan_guide_from_handle 从计划缓存中删除目标批处理或模块的查询计划。 这样做是为了确保所有用户都开始使用新的计划指南。 当为单个查询计划中的多个语句创建计划指南时，可以通过在显式事务中创建所有计划指南来推迟从缓存中删除该计划。 使用此方法可在完成事务以及为每个指定语句创建计划指南之前将计划保留在缓存中。 请参阅示例 B。
权限
需要 VIEW SERVER STATE 权限。 此外，每个使用 <OBJECT 计划指南需要 ALTER 对引用的对象具有权限。 创建类型SQLTEMPLATE或需要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_sql_text 和 sys.dm_exec_text_query_plan 动态管理视图检查。 然后，通过指定计划缓存中与此查询相关的查询计划为此查询创建计划指南。 该示例中的最后一个语句用于验证计划指南是否已存在。
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