November 14, 2012 at 5:33 am
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
November 14, 2012 at 5:49 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply