Determining size of Plan Cache

• kwe477

  Right there with Babe

  Points: 770

  More actions

  November 14, 2012 at 5:33 am

  #266955

  Hello,

  I was wondering whether this query will return the current size of the Plan Cache? Specifically the column SUM_in_MB.

  Thanks

  select

  (Select SUM(convert(bigint, size_in_bytes))/1048576 from sys.dm_exec_cached_plans) as SUM_in_MB,

  size_in_bytes,

  usecounts,

  objtype,

  query.text

  from sys.dm_exec_cached_plans

  outer apply sys.dm_exec_sql_text(plan_handle) as query

• hs24

  Default port

  Points: 1440

  More actions

  November 14, 2012 at 5:49 am

  #1558880

  The following from a post on SQLSkills will help you find the size of the plan cache

  SELECT objtype AS [CacheType]

  , count_big(*) AS [Total Plans]

  , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

  , avg(usecounts) AS [Avg Use Count]

  , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

  , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

  FROM sys.dm_exec_cached_plans

  GROUP BY objtype

  ORDER BY [Total MBs - USE Count 1] DESC

  go