I have a SQL server 2016 SP1 that has been up and running over a year and using the query below, I see that the plan cache size is approx only 4GB where as the physical server has 500GB and there is no max memory setting on the sql instance and there is only one sql instance on the server and no other application.
My understanding is based on how sql server sizes the plan cache using the formula below, the plan cache on my server should be atleast 30GB or more but sql server allocated only 4GB and i have a mix of SP's, sp_executesql and exec(sql) queries on the db. Can experts please help me understand why the plan cache would be so small. Thanks!
SQL Server 2005 SP2 and higher 75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory > 64GB
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;