External Memory Pressure

Question

I have a few "gaps" in my understanding of external memory pressure. I've found quite a bit of helpful information online, including this info from SQLSkills.com.

SQL Server has a built-in component of the SQLOS, the Resource Monitor, that monitors the QueryMemoryResourceNotification Windows Server API to get status about the OS level memory availability on the server. If the Windows OS is under memory pressure, it will set a low memory notification that the Resource Monitor thread will detect and force the external clock hands on the caches internally to begin sweeps of the caches to cleanup and reduce memory usage allowing the process to free memory back to the OS.

1. I have inferred that the Windows OS does not actively "push" the OS level memory availability status to SQL Server. Rather, the SQLOS Resource Monitor must actively ask for it via the Win API function. This would seem to be an asynchronous operation. Am I interpreting this correctly?
2. If my reasoning for #1 is sound, what causes the Resource Monitor to get the OS level memory availability status? Is is performed on a schedule? Event driven? How much time might pass from the moment there is low memory at the OS level to the moment the SQL OS Resource Monitor becomes aware of it?
3. When SQL Server reduces memory usage to free memory back to the OS, just how bad is that? Is this an "expensive" operation that should be avoided at all costs?

Answer 1

1. Yes you are correct, from SQL Server 2005 onwards with arrival of SQLOS( actually it was called UMS in SQl Server 2000) Wiindows OS never directly asks SQL Server to trim down its memory consuption when OS is seeing memory pressure. Instead it raises flags(MEMPHYSICAL_HIGH and MEMPHYSICAL_LOW). These flags are monitored by SQLOS dedicated threads ,that in turn sets a internal High Memory resource flag that tells the various caches they can allocate additional memory, or Low Memory resource flag that tells the various caches to try and reduce their memory allocations.

If memory pressure is severe apart from raising low memory notificaion OS can page out SQL Server as well. There are certain scenarios where things would work differently regarding paging. No paging would happen if SQL Server service account has LPIM. In case of LPIM VirtualAlloc() function do not do major chunk of memory allocations instaed AWEAPI() does the major memory allocation and this is non pageable memory

2. The monitoring is continuous this is one of the tasks that SQLOS dedicated threads do. If I say dedicated they are bound to keep looking for any notification. I am not aware about schedule if any but whatever may be the schedule it would be definitely in seconds.The reaction time is quick and moment SQL server sees low memory notification it would immediately start trimming its memory consumption. But sometimes if external memory pressure is grave SQL Server might not get enough time to trim its memory consumption and free its caches and before it can complete it, OS might page out SQL server process.

3. Yes its expensive in terms that SQL Server has to clear its caches, trim down its memory consumption basically devoiding its processes from memory Or partially catering to there memory requests. The process of consuming and releasing memory involves cost so once SQL server consumes memory it does not gives it back(unless OS flags low memory notification) because it has to go all the way to process of asking memory from OS and the VirtualAlloc() function of OS would find a VAS region and map it to physical memory to actually provide memory to the process.

Edit:

I'm wondering about the ramifications of not setting Max Server Memory.

Microsoft suggests that if you have DEDICATED SQL Server box solely running SQL Server database engine services leave max server memory to default. SQL Server can manage in better way. If you take my opinion this is not true if you have Windows server 2008 and 2005 having SQL Server 2005-2008 R2 installed on it because of various Bugs in Windows server release trimming SQL Server memory consumption. This holds true if you have latest Windows server 2012 having SQL Server 2012. But again this is as per my experience and scenario is limited.

What I suggest is its better to fix max server memory if you correctly set optimum value. Whatever version you are in please see answer given(By me and others) on This SE Thread and This Thread there is lot of evidence to say you should fix max server memory.

If SQL Server "knows" the OS is low on memory immediately, and can return memory to the OS in a timely fashion, why not let it perform that task

That is good question but like I wrote before sometimes if memory crunch faced by OS is too severe SQL Server will respond to memory pressure but in that case before SQL Server gets enough time to clear cache it would be paged out by OS(because OS is in desperate need of memory) eventually slowing SQL Server process very much. Cases where OS can face severe memory crunch

1. Bug in OS
2. Third party driver installed leaking memory
3. Max server memory not set correctly and point 1 and 2 are true.
4. Someone start big file copy and any of the above 3 points are true.

In all above case problem was with OS and applications installed on it and SQL Server became victim.

Answer 2

According to: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server (http://support.microsoft.com/en-us/kb/918483) makes it appear that this is asynchronous:

On a Windows Server 2003 or a Windows XP or later version system, SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API.

Jonathan Kehayias explains more here at https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/ An excerpt includes the following:

As a general rule, SQL Server will use as much memory as you can give it, and it will not release the memory that it has allocated under normal operations, unless the Windows Server OS sets the memory low resource notification flag. A component of the SQLOS called the Resource Monitor monitors the QueryMemoryResourceNotification Windows Server API and when Windows sets the low memory resource notification, the SQLOS will respond by sweeping its caches internally to reduce the process working set, and release memory back to Windows.

It is asynchronous, but I have never experienced a dramatic change. As the buffers are flushed, the memory is released to the OS. Of course, this means that the data cache is smaller and less data is kept in cache which will affect performance since it may result in more storage I/O.

Jonathan's post at https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/#comment-145204 makes the general default recommendation:

Reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

This has worked well for me on relatively large SQL Servers.