--获取批处理信息的脚本(优化器在处理批处理时所发生的优化器事件)
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
--清空过程缓存
GO
--使用tempdb..Optstats表来捕获执行几次
IF ( OBJECT_ID('dbo.tempdb..Optstats') IS NOT NULL )
DROP TABLE tempdb..Optstats;
GO
--创建临时表tempdb..Optstats
SELECT 0 Run ,
*
INTO tempdb..Optstats
FROM sys.dm_exec_query_optimizer_info;
GO
--该语句的计划将被保存到过程缓存
--这样下一次执行时不会产生任何优化器事件
--后面的Go用于确保下次执行这段脚本时可以重用Insert计划
GO
INSERT INTO tempdb..Optstats
SELECT 2 Run ,
*
FROM sys.dm_exec_query_optimizer_info;
GO
--清空临时列表
TRUNCATE TABLE tempdb..Optstats;
GO
--存储运行前的信息
GO
INSERT INTO tempdb..Optstats
SELECT 1 Run ,
*
FROM sys.dm_exec_query_optimizer_info;
GO
--执行批处理语句
SELECT C.CustomerID ,
COUNT(O.OrderID) NumOrders
FROM dbo.Customers C
LEFT JOIN dbo.Orders O ON O.CustomerID = C.CustomerID
WHERE C.City = 'London'
GROUP BY C.CustomerID
HAVING COUNT(O.OrderID) > 5
ORDER BY NumOrders;
GO
--保存到临时表
INSERT INTO tempdb..Optstats
SELECT 2 Run ,
*
FROM sys.dm_exec_query_optimizer_info;
GO
/*从临时表中取出Run1和Run2之间Occurrence或Value值发生改变的所有事件
然后显示执行批处理或查询之前(Run1Occurrence和Run1Value)和之后所有这些事件(Run2Occurrence和Run2Value)的Occurrence和Value*/
WITH X ( Run, Counter, Occurrence, Value )
AS ( SELECT *
FROM tempdb..Optstats
WHERE Run = 1
),
Y ( Run, Counter, Occurrence, Value )
AS ( SELECT *
FROM tempdb..Optstats
WHERE Run = 2
)
SELECT X.Counter ,
Y.Occurrence - X.Occurrence Occurrence ,
CASE Y.Occurrence - X.Occurrence
WHEN 0 THEN Y.Value * Y.Occurrence - X.Value * X.Occurrence
ELSE ( Y.Value * Y.Occurrence - X.Value * X.Occurrence )
/ ( Y.Occurrence - X.Occurrence )
END Value
FROM X
JOIN Y ON X.Counter = Y.Counter
AND ( X.Occurrence <> Y.Occurrence
OR X.Value <> Y.Value
);
GO
--删除临时表
DROP TABLE tempdb..Optstats;
GO
