Can OS pressurize SQL to release memory?

Question

In which of the following two cases, OS can pressurize SQL to release memory.

Suppose a VM has 6Gb RAM

1. If I set sql MAXMemory to 3Gb (without setting Lock pages in memory), would OS be able to free some memory from sql if it needs.
2. If I set sql MAXMemory to 3Gb (And also set Lock pages in memory), would OS be able to free some memory from sql if it needs.

Can OS really pressurize SQL to release memory? I have not encountered any such issue when using non-default value in MAXmemory setting.

Thank you in advance

Answer 1

So Windows does not just go and grab the memory from SQL Server and give it to someone else - that could have been drab effects.

But SQL Server listens to the OS, and if SQL Server finds that there is memory pressure in the machine, it will yield, mainly by giving up data in its caches. And this is true, no matter you have "lock pages in memory" set.

You can easily try this by first getting a lot of data into the buffer cache so that SQL Server maxes out is 3GB of RAM. Then you can start something else that needs memory, and if you monitor sys.dm_os_process_memory, you will find that SQL Server memory will decrease.

If you have not seen this, it could be that you have not been able to find a process that grabs that much memory. Tip: another SQL Server instance could be a thing to try.

Answer 2

The OS doesn't "take memory from sql when required", as you state it. The OS signals to whoever is listening that there is memory pressure in the machine, and SQL Server is trying to be a good citizen and releases some of the memory it has allocated.

As for LPIM, what it does that it prohibits the OS from swapping some of SQL Server to the windows swapfile. That is not the same thing as above.

Answer 3

This is more of a Windows question, than a SQL Server question.

I suggest you read this:

https://www.red-gate.com/simple-talk/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/