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
END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
END) AS [Total Plans – USE Count 1]
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
Solved! Go to Solution.
Hi Gregg - sorry for asking but have you checked out the
Running all SQL DB's on a single machine is a bold move 🙂
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'.