question

ChiragSachdeva-6150 avatar image
0 Votes"
ChiragSachdeva-6150 asked TomPhillips-1744 answered

Can OS pressurize SQL to release memory?

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


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ChiragSachdeva-6150 edited

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ErlandSommarskog,

I tested both the below scenarios

  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.
    Yes, OS takes memory from sql when required.

  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.
    Yes, OS takes memory from sql when required.


So this makes me wonder what the use of LPIM (Lock pages in memory) if OS would eventually takes it back from sql on requirement.

Thanks
Chirag

0 Votes 0 ·
TiborKaraszi avatar image
1 Vote"
TiborKaraszi answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.