There is a very useful new sp_configure option in SQL Server 2008 RC0 called “Optimize for Ad hoc workloads”. This is how Books Online (BOL) describes it:
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
The compiled plan stub allows the Database Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Database Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.
Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected.
This avoids having compiled ad hoc plans taking up space for the full plan in the cache, which reduces memory pressure from the cache being bloated with plans that are not likely to be reused. Very often, the biggest culprits are plans added by the SQL Server Agent as it runs jobs and collects metrics. You can see if you are having problems with this by running the DMV query below:
-- Find the ad-hoc queries that are bloating the plan cache SELECT TOP(100) [text], size_in_bytes FROM sys.dm_Exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'Adhoc' AND usecounts = 1 ORDER BY size_in_bytes DESC
If you want to take advantage of this new option (which has no downside that I can see), run the commands below:
-- Turn on advanced options EXEC sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO -- See what the current value is for 'optimize for ad hoc workloads' EXEC sp_configure -- Turn on optimize for ad hoc workloads EXEC sp_configure 'optimize for ad hoc workloads', 1 GO RECONFIGURE GO -- Finally, clear all the plans in the cache (use with caution on a Production Server) DBCC FREEPROCCACHE
