SELECT
    objtype AS 'Cached Object Type',
    COUNT(*) AS 'Number of Plans',
    SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
    AVG(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY 'Plan Cache Size (MB)' DESC