cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

CAS/ADS SQL Plan Cache Bloat - optimize for ad hoc workloads

gregg_kniss
Guide

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
2 REPLIES 2

ulf_thornander3
Inactive

Hi Gregg - sorry for asking but have you checked out the

https://community.dynatrace.com/community/display/DCRUM124/Planning+CAS+and+ADS+Deployment

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'.