2

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;
  • 2
    When was the instance last restarted? Is there an active workload? SQL Server won't automatically just use up 30 GB of memory because of some calculation, it will use memory when it needs to... – Aaron Bertrand Jan 29 '17 at 21:42
  • Found the cause -The database gets restored every night from production. Thanks for pointing me in the right direction Aaron! – miledba Feb 7 '17 at 19:26
2

SQL Server uses memory only as it needs to... with in-memory technology as the exception, it won't jump to using up all of the memory indicated in the configuration for max server memory (or even min server memory), until you actually request data or plans that require memory.

So if SQL Server has not been up for a long time (maybe you restart every night, or recently installed patches), it won't use that memory until your workload makes it happen. And it may never happen, depending on your available memory and the size of your data. As an example, if you allocate 30GB to SQL Server, but the sum of your data is only 10GB, you're unlikely to see all of your max server memory ever being used, regardless of uptime.

Several of these aspects of SQL Server memory management are widely misunderstood.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.