optimize for ad hoc workloads

--1.查看执行计划重用次数和占用空间 
SELECT cp.usecounts,cp.cacheobjtype,cp.objtype,st.text,qp.query_plan,cp.size_in_bytes       
FROM sys.dm_EXEC_cached_plans cp
cross apply sys.dm_EXEC_sql_text(plan_handle) st
cross apply sys.dm_EXEC_query_plan(plan_handle) qp

--2.查看是否开启即席查询
SELECT * FROM sys.configurations WHERE name='optimize for ad hoc workloads'

--3.开启
EXEC sp_configure 'show advanced options',1 
reconfigure 
EXEC sp_configure 'optimize for ad hoc workloads',1
reconfigure 
EXEC sp_configure 'show advanced options',0 
reconfigure 

--4.将只使用一次的缓存计划清除
declare @sid varbinary(64)
declare cur cursor for
SELECT  plan_handle FROM 
(
	SELECT  plan_handle  
		FROM sys.dm_EXEC_cached_plans AS cp 
			WHERE cp.cacheobjtype = N'Compiled Plan'
			AND cp.objtype IN (N'Adhoc', N'Prepared')
			AND cp.usecounts =1 
) c

open cur
fetch next FROM cur into @sid
while @@fetch_status=0
begin
	DBCC FREEPROCCACHE(@sid)
fetch next FROM cur into @sid
end
close cur
deallocate cur