- 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
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.
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.
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
- Bug in OS
- Third party driver installed leaking memory
- Max server memory not set correctly and point 1 and 2 are true.
- 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.