Is there a consensus or recommendation for enabling "optimize for ad hoc workloads" in SQL Server? While investigating our SQL performance we noticed a lot of single use queries. We have CAS, ADS, CSS, RUM Console, and App Mon databases all in one instance on one server. Upon further investigation we found that the plan cache was growing at a high rate day after day. Upon 36hrs of uptime of the SQL server the cache size for single use plans was 4.1GB and 18hrs later it was 20% more at 4.9GB. I'm curious as to what it will be days/weeks from now.
We used the below query from: http://www.sqlskills.com/blogs/kimberly/plan-cache...
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
Answer by Robert G. ·
From the CAS and ADS point of view enabling "optimize for ad hoc workloads" is a good way to reduce the size of the proc cache. There seem to be no risk in doing it and, as you mentioned, the majority of CAS and ADS queries are 'ad hocs'.
Answer by Ulf T. ·
Hi Gregg - sorry for asking but have you checked out the
Running all SQL DB's on a single machine is a bold move :-)