SQL Plans Cache Enable

--optimize for ad hoc workloads disable
select * from master.sys.configurations
where name='optimize for ad hoc workloads'

--optimize for ad hoc workloads enable
sp_CONFIGURE 'show advanced options',1
reconfigure
go
sp_CONFIGURE 'optimize for ad hoc workloads',1
reconfigure
go

--check execution plan

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

objtype as '物件類型',st.text, cap.size_in_bytes as '物件所耗用的位元組'    

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 '%SELECT * FROM%'