I have a single SQL2012 SP4 node running several databases.
The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box).
Every few minutes SQL dumps the whole of the buffer cache. Page life expectancy hits zero, the buffer cache descriptors show that there's nothing in the cache.
I had a look at the resource monitor notifications and the notifications are bouncing around from high/steady/low every few milliseconds:
RESOURCE_MEMPHYSICAL_HIGH RESOURCE_MEM_STEADY RESOURCE_MEMPHYSICAL_LOW
With timestamps that are several milliseconds apart. The PLE is essentially a saw-tooth pattern.
I've seen this happen before with SQL2012 SP1 and this question:
Seems to be a similar issue, though I've already updated to SP4.
I've tried turning on LPIM for the service account and I've tried messing with the max memory setting. Lowering max memory seems to have caused the buffer cache to empty more frequently.
Any ideas of what to check next?
Server workload is literally nothing (I'm scrolling through lists of items in an ERP system and it gets to about 40-50MB before the cache just drops again).
It's interesting because I upgraded from SP1 to try and fix this - the cache there was getting to around 500MB. Since then I dropped the max memory setting to 14GB which seems to have made it worse.
I'm wondering if Windows is panicking and throwing incorrect notifications for memory pressure at SQL - it follows that the server with max memory set to unbounded seemed to run ok-ish but never fill the cache more than a few hundred MB - but now it barely gets to 50...