EXEC sp_CONFIGURE 'optimize for ad hoc workloads', 1
RECONFIGURE

;WITH cacheobjects
AS
(
SELECT 
  cacheobjtype,
  objtype, 
  CAST(SUM(ISNULL(size_in_bytes,0))/1024./1024. AS DECIMAL(20,2)) AS TotalMB, 
  AVG(ISNULL(size_in_bytes,0))/1024. AS AvgKB, 
  COUNT(*) AS [Count], 
  SUM(usecounts) AS usecounts, 
  AVG(CAST(usecounts AS BIGINT)) AvgUseCount, 
  SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) UsedOnceCount,
  CAST(SUM(CASE WHEN usecounts = 1 THEN ISNULL(size_in_bytes,0)/1024./1024. ELSE 0. END) AS DECIMAL(20,2)) AS UsedOnceMB
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
)
SELECT 
  cacheobjtype, 
  objtype, 
  TotalMB, 
  AvgKB, 
  [Count], 
  AvgUseCount, 
  UsedOnceCount,
  CAST(1.* UsedOnceCount/[Count] AS DECIMAL(20,2)) AS [UsedOnceCount%],
  UsedOnceMB,
  CAST(1.* UsedOnceMB/TotalMB AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
UNION ALL
SELECT 'Total', 'Total', 
  SUM(TotalMB), 
  CAST(SUM(TotalMB)/SUM(CAST([Count] AS BIGINT))*1024. AS DECIMAL(20,2)) AS AvgKB, 
  SUM(Count), 
  SUM(CAST(usecounts AS BIGINT))/SUM(CAST([Count] AS BIGINT)) AS AvgUseCount, 
  SUM(UsedOnceCount), 
  CAST(1.* SUM(UsedOnceCount)/SUM([Count]) AS DECIMAL(20,2)) AS [UsedOnceCount%],
  SUM(UsedOnceMB),
  CAST(1.* SUM(UsedOnceMB)/SUM(TotalMB) AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
ORDER BY TotalMB DESC

;WITH cacheobjects
AS
(
SELECT 
  cacheobjtype,
  objtype, 
  CAST(cast(SUM(ISNULL(cast(size_in_bytes as bigint),0)) as bigint)/1024./1024. AS DECIMAL(20,2)) AS TotalMB, 
  AVG(ISNULL(cast(size_in_bytes as bigint),0))/1024. AS AvgKB, 
  COUNT(*) AS [Count], 
  SUM(usecounts) AS usecounts, 
  AVG(CAST(usecounts AS BIGINT)) AvgUseCount, 
  SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) UsedOnceCount,
  CAST(SUM(CASE WHEN usecounts = 1 THEN ISNULL(size_in_bytes,0)/1024./1024. ELSE 0. END) AS DECIMAL(20,2)) AS UsedOnceMB
  
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
)
SELECT 
  cacheobjtype, 
  objtype, 
  TotalMB, 
  AvgKB, 
  [Count], 
  AvgUseCount, 
  UsedOnceCount,
  CAST(1.* UsedOnceCount/[Count] AS DECIMAL(20,2)) AS [UsedOnceCount%],
  UsedOnceMB,
  CAST(1.* UsedOnceMB/TotalMB AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
UNION ALL
SELECT 'Total', 'Total', 
  SUM(TotalMB), 
  CAST(SUM(TotalMB)/SUM(CAST([Count] AS BIGINT))*1024. AS DECIMAL(20,2)) AS AvgKB, 
  SUM(Count), 
  SUM(CAST(usecounts AS BIGINT))/SUM(CAST([Count] AS BIGINT)) AS AvgUseCount, 
  SUM(UsedOnceCount), 
  CAST(1.* SUM(UsedOnceCount)/SUM([Count]) AS DECIMAL(20,2)) AS [UsedOnceCount%],
  SUM(UsedOnceMB),
  CAST(1.* SUM(UsedOnceMB)/SUM(TotalMB) AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
ORDER BY TotalMB DESC