Plan cache per DB

How much memory each DB is using for sql plan cache?

/* 
Description 	: How much memory each DB is using for sql plan cache?
Author		: Theo Ekelmans 
Version/Date	: 1.0 2011-03-23
*/

SELECT name = DB_NAME(t.[dbid])
	 , plan_size_MB = CONVERT(decimal(12, 2), SUM(CAST(p.size_in_bytes AS bigint)) / 1024.0 / 1024.0)
FROM sys.dm_exec_cached_plans AS p
	 CROSS APPLY sys.dm_exec_sql_text(plan_handle)AS t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY plan_size_MB DESC;

Leave a Reply

Your email address will not be published. Required fields are marked *