[sqlserver脚本]查看指定SQL语句生成了哪些执行计划

参考SQL技术内幕写了一段脚本,可以通过这段脚本查看执行指定SQL语句后,系统生成了哪些执行计划.使用时注意以下几点:

1. 修改use MyTest,换成自己的数据库名字.
2. 将 exec sp_page_test TestTable','*','id',20,100,0,'1=1'替换成要测试的SQL语句
3. 该脚本只支持Sql Server 2005及以上版本

set nocount on;
use MyTest; --这里使用你自己的数据库
dbcc freeproccache;--清空过程缓存
go
--用tempdb..optstats表来捕获所执行SQL语句生成的执行计划信息
if(OBJECT_ID('tempdb..optstats') is not null)
    drop table tempdb..optstats;
go
--这条语句用于创建临时表 tempdb..optstats
select *,0 as run
into tempdb..optstats
from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
go
--该语句的计划将被保存到过程缓存
--这样当下一次执行时不会产生任何优化器事件.
--后面的GO用于确保下次执行这段脚本时
--可以重用insert的计划
go
insert into tempdb..optstats
select *,1 as run
from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
go
--原因同上,只是用"2"替换"1"
--这样我们将得到不同的计划.
go
insert into tempdb..optstats
select *,2 as run
from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
go
--清空临时表
truncate table tempdb..optstats
go
--存储"运行前的"信息,
--保存到临时表,run列的值为"1"
go
insert into tempdb..optstats
select *,1 as run
from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
go
--在这里执行你自己的语句或批处理
--/*** 下面是一个示例
go
exec sp_page_test 'TestTable','*','id',20,100,0,'1=1'
go
--***/
go
--存储"运行后的"信息,
--保存到临时表,RUN列的值为"2"
go
insert into tempdb..optstats
select *,2 as run
from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
go
--从临时表中提取出run1和run2之间
--执行所测试SQL语句后生成的执行计划
select * from tempdb..optstats where bucketid in(
 select tt.bucketid from ( 
    select bucketid,COUNT(*) as cc  from tempdb..optstats  group by bucketid ) tt where  tt.cc=1
)
go 
--删除临时表
Drop table tempdb..optstats;
go
set nocount off

转载于:https://www.cnblogs.com/harry-wang/p/5959416.html