Sql-server – Weird memory pressure for SQL Server 2016

The closest internal documentation I can find on the RM is from the PSS team here: How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

Which specific to your question on which internal processes may contribute to changing the indicator there is no specific documentation that answers this...that I can find. The article references actually shows that there is a startup trace flag 8020 that can be used (with extreme caution) to narrow down what is triggering RM.

I think the main bit of text from the article above would be this:

There are memory broker decisions that come into play at this time. What the information is is saying is that a cache or the cache’s predicted usage will exceed internal targets and we need to start doing cache cleanup. By looking at the memory broker, ring buffer entries, you can see the behavior that broker it asking of RM. Using the clock hands you can see the internal and external hand movement. This will help you determine which caches are involved. The dbcc memorystatus() show good details and last notification states as well.

Not sure if that answers the question in full, but is about as close to what I can find online.

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.