Analyze single plan usage

single-plan-usage.sql

SELECT
	objtype
,	COUNT_BIG(*) AS plans_count
,	SUM(CAST(size_in_bytes AS bigint) / 1024. / 1024.) AS plans_MB
,	AVG(usecounts) AS plans_count_avg
,	SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS plans_single_use_count
,	SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS bigint) / 1024. / 1024. ELSE 0 END) AS plans_single_use_MB
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY plans_single_use_MB DESC