SQL dumping all pages from buffer cache every few minutes

by Charleh   Last Updated March 14, 2019 20:06 PM - source

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:

SQL Server 2012 Free Pages in Buffer Cache Not Being Used

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...



Related Questions



Plan cache size and reserved memory

Updated June 11, 2016 08:02 AM

Memory Usage by SQL Server

Updated July 23, 2018 22:06 PM

Improving mongodb read throughput for tiny database

Updated April 22, 2016 09:02 AM

Lowering MySQL Memory Usage without Restart

Updated May 28, 2015 22:02 PM