Can OS pressurize SQL to release memory?

Chirag Sachdeva 261 Reputation points
2021-04-03T09:47:11.497+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,508 questions
No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2021-04-03T10:54:46.473+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. tibor_karaszi@hotmail.com 4,291 Reputation points Microsoft MVP
    2021-04-05T11:11:54.413+00:00

    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.

    No comments

  2. Tom Phillips 17,511 Reputation points
    2021-04-05T13:24:46.05+00:00

    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/

    No comments