[SQL SERVER][Performance]別忘記開啟optimize for ad hoc workloads

2011-06-29

如果你是使用SQL2008以上的版本，強烈建議開啟該選項，

當你看完 optimize for ad hoc workloads 說明後，我想你就會知道我為什麼會這麼說了。

optimize for ad hoc workloads:

(擷取BOL)

相信大家都知道，SQL Server會對每個批次建立相關執行計畫後儲存在計畫快取區，

而計畫快取區目的主要是重用執行計畫，

optimize for ad hoc workloads 主要就是讓計畫快取區都是存放最常使用的執行計畫(最常執行的查詢)，

所以第一次只會先建立小型編譯計畫(complied plan stub)而非完整執行計畫並儲存在計畫快取區中，

以協助減輕記憶體的壓力，下面就來測試看看。

optimize for ad hoc workloads disable

select * from master.sys.configurations

where name='optimize for ad hoc workloads'

--Query

select * from dbo.TB_HR_DEPARTMENT

go

--check execution plan

SELECT cap.usecounts as '使用次數',cap.cacheobjtype as '快取類型',

objtype as '物件類型',st.text

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.TB_HR_DEPARTMENT%'

第一次執行該 ad hoc query 的完整執行計畫以存入計畫快取區中。

optimize for ad hoc workloads enable

sp_CONFIGURE 'show advanced options',1

reconfigure

go

sp_CONFIGURE 'optimize for ad hoc workloads',1

reconfigure

go

select * from master.sys.configurations

where name='optimize for ad hoc workloads'

go

第一次查詢

第二次查詢

第三次查詢

當開啟了optimize for ad hoc workloads 後，第一次所建立的快取類型為 Compiled Plan Stub，

第二次執行才建立完整執行計畫在計畫快取中，第三執行就可以看到重用了該執行計畫。

參考

RiCo技術農場