SQL Server Plan Cache Bloat from Single-Use Ad-hoc Plans

SELECT objtype,
       COUNT(*) AS plans,
       SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plans,
       CAST(SUM(CAST(size_in_bytes AS BIGINT)) / 1048576.0 AS DECIMAL(18,1)) AS total_mb,
       CAST(SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS BIGINT) ELSE 0 END) / 1048576.0 AS DECIMAL(18,1)) AS single_use_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY total_mb DESC;