Proc cache slowly decreasing in size until restart is required

Question

Post reply

Proc cache slowly decreasing in size until restart is required

Viewing 4 posts - 1 through 5 (of 5 total)

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

Site Owners SSC Guru Points: 80412 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

Like I said on the other thread with the identical name (Note: there is zero need to double post on this forum)...

This sounds like either a "Connection Leak" or a "Memory Leak". A "Connection Leak" is where no connection is ever closed and everyone gets a new connection (which take memory) or a "Memory Leak" where certain types of "Cursors" are simply not being closed and the code keeps opening new ones. It could also be some sort of "in-memory logging" that is never cleared except by a restart.

These types of things are frequently difficult to find but, man... is it ever worth it to find them and fix them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bouke Bruinsma SSCrazy Points: 2194 More actions · Answer 3

Thanks for your response Jeff. The number of connections is stable. I should have mentioned that we have several of these database servers (1 instance per server) with comparable workload and different SQL versions. On 2019 or 2022 servers the proc cache remains constant. We are planning an upgrade on short term. Until now we might not have noticed it because of the monthly patching we're doing; it takes about a month before we hit critical levels. In December however, we had a freeze and skipped maintenance.

If we see it happening after the upgrade as well, we've got work to do.

Bouke Bruinsma SSCrazy Points: 2194 More actions · Answer 4

To round up this topic, we upgraded to SQL 2019 and the issue is gone or at least not a problem anymore. There is a bit of a decrease but nowhere near as steep as before.