1

I am running SQL Server Standard 2014 with SP3. I am noticing a lot of activity on the log viewer around insufficient system memory in resource pool and memory Broker.

I have searched on this error and it was pointing back to SQL 2008 with a KB as a fix. Am I looking at this right that SQL is only using less than 4GB of the memory? We are Running Dynamics AX 2012, so the queries can be quite intense.

My physical server has 56Gb of memory and SQL has 16384 min and 36864 as max memory. When querying SQL is shows that only 3.51 is being consumed. I am using the following query to determine that which lines up with the Windows Task manager.

SELECT CONVERT(DEC(8,2),OSPM.physical_memory_in_use_kb/1024.0/1024,0) AS SQL_GB FROM sys.dm_os_process_memory OSPM
SELECT CONVERT(DEC(8,2),OSM.available_physical_memory_kb/1024.0/1024,0) AS AvailGB FROM sys.dm_os_sys_memory OSM

The memory seems available but SQL is only using a tiny portion, if I am looking at this right. My other test systems do not experience this type behavior.

SQL Log

SQL Statement on memory

  • Are you using in-memory OLTP in any of the databases on this instance? – Josh Darnell Dec 7 '18 at 21:33
  • I am not using in-memory OLTP. I might add this this is hosted on Azure and Lock pages in memory is set. – Girac Dec 7 '18 at 21:35
  • Can you add complete output of errorlog, you only added part of it in the question. The memorystatus output should tell us what is causing the OOM error. – Shanky Dec 11 '18 at 17:21
3

Essentially, the error is saying that there is not enough memory available to Sql Server for it to grow its internal buffers - this generally happens if Windows is having to page a lot or other processes are consuming the memory.

You should also remember that just because you tell Sql Server to use a minimum amount of RAM, it can use less if the host OS needs it more - the host always takes priority and the buffer pool will get trimmed if the host starts to detect memory pressure. For instance, in the documentation for Server Memory Configuration Options it says:

SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

I would check to see what other processes are running (for instance Service Broker), and how much strain they are putting on the server. If possible, increase the size of the VM or move some of the other processes onto other boxes.

Lock Pages In Memory Option

It is a common misconception that when you use Lock Pages In Memory that it can never be released. Using the LPIM setting prevents Windows moving pages in memory to virtual memory on disk (the page file). It does not however, prevent Sql Server from reducing its memory footprint if it detects that the host OS is low on memory. The snippet below is taken from the same page the previous snippet is taken from:

Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks.

Sql Server uses the Memory Notification API's within Microsoft Windows to detect when the host is running low on system resources. If the host is running low, Sql Server will begin to trim the buffer pool in response. This is an important fact - it is Sql Server that is releasing the memory in response to an external event - Windows is not taking it from Sql Server. The below is taken from the Memory Management Architecture Guide:

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS.

  • 1
    For what it's worth, the OP said they have LPIM enabled on this instance. So the OS should not be taking memory away from the buffer pool. But I think you're likely right that it's resource contention with the host OS / other apps running on the box. – Josh Darnell Dec 8 '18 at 0:11
  • Thanks for the update. Using LPIM does not prevent Sql Server from trimming the buffer pool though if it detects there is memory pressure on the host OS: "Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks." – Mr.Brownstone Dec 8 '18 at 0:30
  • 1
    I think that means it will trim the buffer pool to give memory to other memory clerks within SQL Server - not back to the host OS. But I could be wrong, I haven't used that setting in practice. – Josh Darnell Dec 8 '18 at 2:44
  • I have updated my answer with more information regarding the LPIM option. – Mr.Brownstone Dec 8 '18 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.