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 2005
2.《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 PLAN
N'上一个脚本的XML结果')'

采用上述的作法直接影响SQL SERVER编译执行计划的方式，但如同各种数据表提示（hint），非得不得已不要轻易使用；使用上述查询计划，适用1，但用户又偏好2，则上述配置则反而有害了；

posted @ 2010-04-02 14:05 xxd 阅读(1360) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MSSQL执行计划重用

公告