通过

sp_create_plan_guide_from_handle (Transact-SQL)

  • 项目

从计划缓存中的查询计划创建一个或多个计划指南。 可以使用此存储过程来确保查询优化器始终为指定查询使用特定的查询计划。 有关计划指南的详细信息，请参阅计划指南。

主题链接图标 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)。 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 显示计划（包括传递给查询的任何文字值）逐个字符地导入生成的计划指南中。 这些文本字符串可能包含敏感信息，这些信息将以数据库的元数据形式存储。 拥有适当权限的用户可通过在 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。 其他诸如 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

请参阅

参考

数据库引擎存储过程 (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)

概念

计划指南