[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