sqlserver plan guide 固定执行计划

利用两个存储过程，一个是sp_create_plan_guide_from_handle，一个是sp_create_plan_guide。前者利用sql_handle，更加方便；后者需要填入完整sql语句，在语句复杂时比较麻烦。

 

一、 sp_create_plan_guide_from_handle

语法如下：

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

利用sql_hanlde，从计划缓存中的查询计划创建一个或多个计划指南

-- -- 1. find the sql_handle
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'%sql语句%';  
GO  

-- 2. 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 sql_handle=0x02000000634C1C0B421GY78D01204FB24BADBAEE2E6A024C0000000000000000000000000000000000000000; 
  
EXECUTE sp_create_plan_guide_from_handle   
    @name =  N'Guide0510',  
    @plan_handle = @plan_handle,  
    @statement_start_offset = @offset;  
GO  

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-from-handle-transact-sql?view=sql-server-2017

 

二、 sp_create_plan_guide

语法如下：

sp_create_plan_guide [ @name = ] N'plan_guide_name'  
    , [ @stmt = ] N'statement_text'  
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  
    , [ @module_or_batch = ]  
      {   
                    N'[ schema_name. ] object_name'  
        | N'batch_text'  
        | NULL  
      }  
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'   
                 | N'XML_showplan'  
                 | NULL }

• @stmt为存储过程中的sql语句
• @module_or_batch为存储过程名
• @params为参数
• @hints为要加的hint，可以指定连接类型例如 @hints = N'OPTION(HASH JOIN)'; 
• @type有3类，代表计划指南的3类用途：
• OBJECT：为存储过程中的查询创建计划指南
• TEMPLATE：使SQL Server强制执行查询参数化
• SQL：分为两种，为独立查询创建计划指南 和 从缓存中获取XML格式执行计划来创建计划指南

 

1. OBJECT：为存储过程中的查询创建计划指南

-- 示例存储过程
IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL  
    DROP PROCEDURE Sales.GetSalesOrderByCountry;  
GO  
CREATE PROCEDURE Sales.GetSalesOrderByCountry   
    (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h   
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID  
    INNER JOIN Sales.SalesTerritory AS t   
        ON c.TerritoryID = t.TerritoryID  
    WHERE t.CountryRegionCode = @Country_region;  
END  
GO  

EXEC sp_create_plan_guide   
    @name =  N'Guide1',  
    @stmt = N'SELECT *  
              FROM Sales.SalesOrderHeader AS h   
              INNER JOIN Sales.Customer AS c   
                 ON h.CustomerID = c.CustomerID  
              INNER JOIN Sales.SalesTerritory AS t   
                 ON c.TerritoryID = t.TerritoryID  
              WHERE t.CountryRegionCode = @Country_region',  
    @type = N'OBJECT',  
    @module_or_batch = N'Sales.GetSalesOrderByCountry',  
    @params = NULL,  
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';  

 

2. TEMPLATE：使SQL Server强制执行查询参数化

下列两个查询在语法上是等价的，差别只是它们的常量文字值

SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45640;  

下面是参数化格式的查询的计划指南：

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
                  ON h.SalesOrderID = d.SalesOrderID  
              WHERE h.SalesOrderID = @0',  
    @type = N'TEMPLATE',  
    @module_or_batch = NULL,  
    @params = N'@0 int',  
    @hints = N'OPTION(PARAMETERIZATION FORCED)';  

其中@stmt 参数的值是参数化格式的查询，可以通过以下sql直接获取创建

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
          ON h.SalesOrderID = d.SalesOrderID  
      WHERE h.SalesOrderID = 45639;',  
    @stmt OUTPUT,   
    @params OUTPUT  
EXEC sp_create_plan_guide N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';  

 

3. SQL：为独立查询创建计划指南

EXEC sp_create_plan_guide   
    @name = N'Guide1',   
    @stmt = N'SELECT TOP 1 *   
              FROM Sales.SalesOrderHeader   
              ORDER BY OrderDate DESC',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (MAXDOP 1)';

 

4. SQL：从缓存中获取XML格式执行计划来创建计划指南

在创建计划指南时，直接在@hints参数中为查询指定XML格式计划

-- 用下面的sql找到sql的sql_handle

SELECT sql_handle,text 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp 
WHERE st.text LIKE N'%SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%'

-- create plan guide
DECLARE @xml_showplan nvarchar(max);  
SET @xml_showplan = (SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE sql_handle=xxx);  
  
EXEC sp_create_plan_guide   
    @name = N'Guide1_from_XML_showplan',   
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints =@xml_showplan;  
GO  

 

三、plan guide维护

1. 检查guide是否创建

SELECT * FROM sys.plan_guides;  

2. 检查创建是否成功（若有值则不成功，message为报错信息）

SELECT plan_guide_id,name,msgnum, severity, state, message  
FROM sys.plan_guides  
CROSS APPLY fn_validate_plan_guide(plan_guide_id); 
GO  

3. 验证绑定是否成功

找到sql的真实的执行计划 -> 右键显示xml格式 -> ctrl+f -> 搜索gui关键字 -> 如果成功就会找到plan_guide相关的设置

4. 删除plan guide

EXEC sp_control_plan_guide N'DROP', N'Guide0520';

5. 扩展事件

可以通过扩展事件查看plan_guide是否成功，事件类为Plan Guide Successful和Plan Guide Unsuccessful 

 

参考

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-transact-sql?view=sql-server-2017

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-functions/sys-fn-validate-plan-guide-transact-sql?view=sql-server-2017

https://docs.microsoft.com/zh-cn/sql/relational-databases/event-classes/plan-guide-successful-event-class?view=sql-server-2017