MSSQL执行计划重用

--简介

在Oracle的世界里有很多存储执行计划的手段如SQL_Profile, Stored_Outline, Plan_Baseline,在SQL Server里好像只有这样一个选择

--Ref

1.http://technet.microsoft.com/zh-cn/library/cc966425(en-us).aspx   --Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 2.《SQL Server 2005 Practical Troubleshooting The Database Engine》Chapter 4    本页下方有一个使用例子是从这本书上摘录的 1. Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 20052.《SQL Server 2005 Practical Troubleshooting The Database Engine》Chapter 4   本页下方有一个使用例子是从这本书上摘录的

--自己创建执行计划

建立存储过程 代码  sp_create_plan_guide sp_create_plan_guide @name = N ' Guide1 ' , @stmt = N ' SELECT *FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country_region ' , @type = N ' OBJECT ' , @module_or_batch = N ' Sales.GetSalesOrderByCountry ' , @params = NULL , @hints = N ' OPTION (OPTIMIZE FOR (@Country_region = N '' US '' )) ' -- 其中@Country_region = N''US''查询较多，所以以下plan把us为参数的语句放到缓存Buffer中 @hints = N ' OPTION (PARAMETERIZATION FORCED) ' -- 其中PARAMETERIZATION FORCED把带参数所有语句都放到缓存Buffer中  sp_get_query_template:按照特定的语句生成template,之后生成执行计划 -- Obtain the paramaterized form of the query: DECLARE @stmt nvarchar ( max ); DECLARE @params nvarchar ( max ); EXEC sp_get_query_template N ' SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 101 GROUP BY pi.ProductID, pi.Quantity HAVING SUM(pi.Quantity) > 50 ' , @stmt OUTPUT, @params OUTPUT; -- Force parameterization of the query. (This step is only required -- if the query is not already being parameterized.) EXEC sp_create_plan_guide N ' TemplateGuide1 ' , @stmt , N ' TEMPLATE ' , NULL , @params , N ' OPTION(PARAMETERIZATION FORCED) ' ; -- Create a plan guide on the parameterized query EXEC sp_create_plan_guide N ' GeneralizedGuide1 ' , @stmt , N ' SQL ' , NULL , @params , N ' OPTION(HASH JOIN) ' ; -- 强制进行Hash Join

--从缓存(sys.dm_exec_query_stats)中的查询计划内创建执行计划

sp_create_plan_guide_from_handle USE AdventureWorks; 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  为多语句批处理创建多个计划指南 USE AdventureWorks; 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

--删除,启用或禁用执行计划

删除、启用或禁用计划指南 -- sp_control_plan_guide -- Create a procedure on which to define the plan guide. IF OBJECT_ID (N ' Sales.GetSalesOrderByCountry ' , N ' P ' ) IS NOT NULL DROP PROCEDURE Sales.GetSalesOrderByCountry; GO CREATE PROCEDURE Sales.GetSalesOrderByCountry ( @Country 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 ; END GO -- Create the plan guide. EXEC sp_create_plan_guide N ' Guide3 ' , 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 ' , N ' OBJECT ' , N ' Sales.GetSalesOrderByCountry ' , NULL , N ' OPTION (OPTIMIZE FOR (@Country = N '' US '' )) ' ; GO -- Disable the plan guide. EXEC sp_control_plan_guide N ' DISABLE ' , N ' Guide3 ' ; GO -- Enable the plan guide. EXEC sp_control_plan_guide N ' ENABLE ' , N ' Guide3 ' ; GO -- Drop the plan guide. EXEC sp_control_plan_guide N ' DROP ' , N ' Guide3 ' ; 禁用当前数据库中的所有计划指南 USE AdventureWorks; GO EXEC sp_control_plan_guide N ' DISABLE ALL ' ;

--一个例子(忘了是从哪里看到的,没修改就放到这里了)

1 制造分散数据 代码 2 开始测试 DBCC FREEPROCCACHE SET STATISTICS IO ON SET STATISTICS TIME ON EXEC sp 2 /* SQL Server 分析和编译时间:CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 分析和编译时间:CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。表 't'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。 */ EXEC sp 2 /* SQL Server 分析和编译时间:CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 分析和编译时间:CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。表 't'。扫描计数 1，逻辑读取 786890 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 SQL Server 执行时间:CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 执行时间:CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。 */ Alter proc sp @i int as select c2,c3 from t where c2 = @i Option (Optimize for ( @i = 1 )); EXEC sp_create_plan_guide @Name = N ' Guide1 ' , @stmt = N ' SELECT c2,c3 FROM t WHERE c2=@i ' , @type = N ' OBJECT ' , @module_or_batch = N ' dbo.sp ' , @params = NULL , @hints = N ' OPTION (OPTIMIZE FOR (@i = 1)) ' GO SET SHOWPLAN_XML ON ; GO select c2,c3 from t where c2 = 1 go SET SHOWPLAN_XML OFF ;--在查询配置中，直接使用该查询计划，如： EXEC sp_create_plan_guide @Name = N ' Guide1 ' , @stmt = N ' SELECT c2,c3 FROM t WHERE c2=@i ' , @type = N ' OBJECT ' , @module_or_batch = N ' dbo.sp ' , @params = NULL , @hints = N ' OPTION (USE PLANN ' 上一个脚本的XML结果 ' ) ' 采用上述的作法直接影响SQL SERVER编译执行计划的方式，但如同各种数据表提示（hint），非得不得已不要轻易使用；使用上述查询计划，适用1，但用户又偏好2，则上述配置则反而有害了；

转载于:https://www.cnblogs.com/buro79xxd/archive/2010/04/02/1703085.html