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...
More Info: for those that asked
Number of cores: 4
Database Size: 80GB
Error log shows:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 247928, committed (KB): 495656, memory utilization: 50%.
Results of running scripts from this link: https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/
Not sure how to interpret these - it looks like there is both internal and external memory pressure at various times..
Even more info:
This is a Hyper-V guest sitting on a host with 96GB of total ram of which about half is allocated to the guests.
The symptoms seem similar to this:
However, when I allocated 14GB to SQL the symptoms kicked in immediately (barely 3GB of the server memory was committed)
Last night I bumped the guest memory to 32GB and the issue went away but I see only 14GB commit of total server memory (and the business that runs the DB is busy this morning and this is when they usually have their performance issues).
About 8-9GB of data in the cache at the moment, seems to be stable.
It seems to suggest that 20GB is enough for the workload on this box. I'm happy leaving it with 32GB for now but I'd really like to get to the bottom of this so I can better configure the VMs/SQL.
I'll keep digging and update if I find the answer!
Even more more info:
I didn't restart SQL after turning on LPIM (not realising that was a requirement) but I did leave this setting on and restart to upgrade the memory so now I'm not sure if the increase in memory or LPIM has alleviated the issues.
Will jump on tonight when the server is idle and check how it looks at 20GB again.
Even Further More More Info:
Currently the server is ticking along ok with 32GB allocated and we've not seen the problem since. If this crops up again I'll come back to this question and keep digging.
Currently remains a mystery but my guess is that I'm only masking the issues at the moment.