SQL Server: Plan Cache and Adhoc Workloads

Applications generating many dynamic queries (such as ORM frameworks) can lead to a query plan cache bloated by single use plans. Caching something that is only used once is obviously a waste of memory that could otherwise be used to store data pages.

If you have a predominately adhoc workload, turning on ‘optimize for adhoc workloads’ can help reduce the memory footprint of single use plans (it won’t solve the problem entirely though). A system I’ve recently worked on was able to regain 9GB of memory for data pages by turning this setting on.

sp_configure 'show advanced options', 1
GO
reconfigure
GO

sp_configure 'optimize for ad hoc workloads', 1
GO
reconfigure
GO

I’ve recently been using a slightly modified version of Kimberly Tripp’s query from her post, Plan cache and optimizing for adhoc workloads to categorise a workload:

SELECT 
	CacheType = objtype,
	TotalPlans = COUNT_BIG(*),
	TotalMBs = CAST(SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS decimal(9,2)),
	AverageUseCount = AVG(usecounts),
	TotalMBs1USE = CAST(SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS DECIMAL(18, 2))) / 1024 / 1024 AS decimal(9,2)),
	TotalPlans1USE = SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END),
	[%TotalPlans] = CAST(100. * SUM(1) / (SELECT COUNT_BIG(*) FROM sys.dm_exec_cached_plans) AS decimal(9,2)),
	[%TotalMB] = CAST(100. * SUM(CAST(size_in_bytes AS DECIMAL(18,2))) / (SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2))) FROM sys.dm_exec_cached_plans) AS decimal(9,2))
FROM 
	sys.dm_exec_cached_plans
GROUP BY 
	objtype