In SQL Server 2008, there is a new server configuration option introduced, called ‘optimize for ad hoc workloads’. This option can improve the efficiency of the plan cache for single use ad-hoc queries (or called dynamic SQL). When this option is on (set to 1, and 0 by default), SQL Server only stores a small compiled plan stub in the plan cache instead of the full compiled plan. When the same query executs next time, the database engine knows that this ad hoc query has compiled before based on the compiled plan stub in the plan cache, and then removes the compiled plan stub from the plan cache and stores the full compiled plan in the plan cache this time so that the plan can be reused in the following executions. For more informaiton, refer to BOL: http://msdn.microsoft.com/en-us/library/cc645587.aspx.
Here is a script to check the ‘optimize for ad hoc workloads’ option, and update it to 1 if required.
-- Check the total server memory currently used by buffer pool only SELECT cntr_value / 1024 AS [Total Server Memory (MB)] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Memory Manager' AND counter_name = 'Total Server Memory (KB)' -- Cached plan usage statistics (from kimberly) SELECT objtype AS [CacheType] , COUNT_BIG(*)AS [Total Plans] , SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs] , AVG(usecounts) AS [AVG USE COUNT] , SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE COUNT 1] , SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE COUNT 1] FROM sys.dm_exec_cached_plans GROUP BY objtype ORDER BY [Total MBs - USE COUNT 1] DESC GO -- Check the size of the total cached plans SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs] FROM sys.dm_exec_cached_plans -- Check the size of total cached plans that were used only once SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs of Single-user Plans] FROM sys.dm_exec_cached_plans WHERE usecounts = 1 -- You can clean the whole plan cache since 'optimize for ad hoc workloads' -- only works for following queries /* DBCC FREEPROCCACHE; GO */ -- Check the optimize for ad hoc workloads option, should be 1 SELECT * FROM sys.configurations WHERE name = 'optimize for ad hoc workloads'; GO -- Update optimize for ad hoc workloads to 1 if it is 0 EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'optimize for ad hoc workloads', 1; GO RECONFIGURE; GO /* References: http://msdn.microsoft.com/en-us/library/cc645587.aspx http://msdn.microsoft.com/en-us/library/ms187404.aspx http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx */