We have been battling an issue with weird memory pressure on a specific server for a couple of months now. Here's what the last incident looked like in SentryOne:
SQL Server Memory
- Total Server Memory - 96GB
- Max Server Memory - 84GB
The reason why this seems weird is that, if it were external memory pressure I'd expect the Other category on System Memory to grow during this, which is doesn't.
Part of what we see happen during this time is that queries end up generating bad plans and end up causing performance issues for the app. Historically, running DBCC FreeProcCache during this situation has alleviated the pressure, but we still don't know the cause. I think the plans getting a bad plan is a symptom rather than a cause of this issue, but I may be wrong.
Things that we have done to try and resolve this issue:
- Removed a join in what we thought was the problematic sp
- Deleted duplicate records in the database
- Increased memory on the server (I think we added 16-32 gb)
- Enabled Lock Pages In Memory
I am at a complete loss as to what to look at next. Our architect thinks we might need to fiddle with some VM settings with memory, but we aren't there yet.
What can I look at to potentially fix this weird memory pressure issue?