Post reply

Proc cache slowly decreasing in size until restart is required

  • January 9, 2025 at 12:56 pm

    #4512613

    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.

    DecreasingProcCache

     

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

  • January 9, 2025 at 1:25 pm

    #4512631

    Not a clue. I haven't seen something like this before. However, I'd suggest not bothering to look at the proc cache any more. Look elsewhere. What the heck is using memory on the server? That's the question. Specifically, I'd look outside SQL Server to see if something is starving it of memory.

    Otherwise, the usual suspects:

    DBCC MEMORYSTATUS

    sys.dm_os_memory_clerks

    sys.dm_os_process_memory

    If it looks like it's SQL Server itself (and I'm leaning away from that, but, just guessing, not enough info), then get Extended Events involved to look at which queries are using more memory.

    This is an odd one.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 9, 2025 at 2:46 pm

    #4512688

    I have never come across this either.

    The first thing I would do is run Brent Ozar's sp_blitz. As well as the health check, there are parameters which allow you to drill down a bit.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply