--简介
在Oracle的世界里有很多存储执行计划的手段如SQL_Profile, Stored_Outline, Plan_Baseline,在SQL Server里好像只有这样一个选择
--Ref
- 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 开始测试
采用上述的作法直接影响SQL SERVER编译执行计划的方式，但如同各种数据表提示（hint），非得不得已不要轻易使用；使用上述查询计划，适用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结果')'