Proc cache slowly decreasing in size until restart is required

Question

Proc cache slowly decreasing in size until restart is required

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

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

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 1

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

Ken McKelvey SSCoach Points: 18975 More actions · Answer 2

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.

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

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)