Proc cache slowly decreasing in size until restart is required

• Bouke Bruinsma

  SSCrazy

  Points: 2189

  More actions

  January 9, 2025 at 12:55 pm

  #4512611

  In SQL 2017 (14.0.3475.1) the size of the proc cache is slowly decreasing. The workload on the instance has the same profile every day and does not change with time. The proc cache size started at a little over 5G and after about 3 weeks is down to below 1G. Just before the previous restart, it even was 300MB. The effect of such a small proc size is a huge rate of recompilations causing severe performance problems in the app. A restart helps but is very intrusive.

  The server has 16 cores and 128G RAM. Max memory is 120G. Page Life Expectancy is almost always above 2000 seconds.

  The fact that this seems to be a slow and steady regression where the workload on average remains constant, suggests to me that we cannot relate it to the workload directly but we are looking at unwanted behavior in SQL Server 2017. I used the below query to monitor the cache size. We log every 15 minutes resulting in the below chart.

  Has anyone ever seen anything like this? Would upgrading to 2019 or 2022 help? Google or ChatGPT gives no leads.

  

   

  SELECT COUNT(*) AS NumberOfExecutionPlans, 
         SUM(cp.size_in_bytes) / 1048576 AS TotalSizeInMB
    FROM sys.dm_exec_cached_plans cp
   WHERE cacheobjtype = 'Compiled Plan'