Compiled Plans vs Plan Stubs

A query that compares the size and count of compiled plans versus plan stubs in the cache.

Published Oct 2, 2007

How much of the plan cache is real compiled plans versus stubs? This query groups dm_exec_cached_plans by cache object type and reports the total size in megabytes and count for each - a quick way to see whether your cache is bloated with stub entries.

SELECT
	cacheobjtype as [Plan Type],
	Cast(Sum(Cast(size_in_bytes as float)) / 1024.0 / 1024.0 as Decimal(16, 2)) as [Size (MB)],
	Count(0) as [Count]
FROM
	sys.dm_exec_cached_plans
WHERE
	cacheobjtype like 'Compiled Plan%'
GROUP BY
	cacheobjtype