Check For Plan Cache Pollution

-- Adapted from demo scripts by Kimberly L. Tripp, SQLskills – https://www.sqlskills.com/blogs/kimberly/

CREATE PROCEDURE [dbo].[CheckForPlanCachePollution]
AS 
SELECT [Cache Type] = [cp].[objtype] 
    , [Total Plans] = COUNT_BIG (*) 
    , [Total MBs]
        = SUM (CAST ([cp].[size_in_bytes] 
            AS DECIMAL (18, 2))) / 1024.0 / 1024.0 
    , [Avg Use Count] 
        = AVG ([cp].[usecounts]) 
    , [Total MBs - USE Count 1]
        = SUM (CAST ((CASE WHEN [cp].[usecounts] = 1 
        THEN [cp].[size_in_bytes] ELSE 0 END) 
            AS DECIMAL (18, 2))) / 1024.0 / 1024.0
    , [Total Plans - USE Count 1]
        = SUM (CASE WHEN [cp].[usecounts] = 1 
                THEN 1 ELSE 0 END) 
    , [Percent Wasted]
        = (SUM (CAST ((CASE WHEN [cp].[usecounts] = 1 
            THEN [cp].[size_in_bytes] ELSE 0 END) 
            AS DECIMAL (18, 2))) 
         / SUM ([cp].[size_in_bytes])) * 100
FROM [sys].[dm_exec_cached_plans] AS [cp]
GROUP BY [cp].[objtype]
ORDER BY [Total MBs - USE Count 1] DESC;