How does OS/SQL server behave when respective entity needs more memory?



  • When SQL max memory is not set, then sql can use any amount of memory.

    1. Suppose SQL takes all the memory and then OS needs around 4GB memory, then will OS forcibly take the memory from the sql server or will OS use disk paging file?

    2. While OS is holding on to the above 4GB memory (for some ongoing OS work), and suppose SQL needs more memory then will SQL forcibly take the memory from OS or will it use the disk paging file?



  • Suppose SQL takes all the memory and then OS needs around 4GB memory, then will OS forcibly take the memory from the sql server or will OS use disk paging file?

    OS will always be in a position to ask SQL Server memory to lower its memory usage by trimming the caches. Now there are 2 cases

    1. SQL Server service account does not have LPIM: In this case OS will page sql server process to disk and use the physical memory freed. In turn process using paged memory will be super slow.

    2. SQL Server service account has LPIM: In this case SQL server will still try to lower its consumption but since memory is locked OS would not be able to page the memory to disk thereby causing OS to be paged which can result in OS process being super slow, OS crashing etc. This is something which you would never want.

    Also its HIGHLY unlikely OS would request 4 GB memory in one go, normally memory requests are in MB.

    While OS is holding on to the above 4GB memory (for some ongoing OS work), and suppose SQL needs more memory then will SQL forcibly take the memory from OS or will it use the disk paging file?

    SQL Server runs as service and "forcing" OS to release memory is not going to happen. Memory allocation will be done in conjunction.

    Again here you have 2 conditions:

    1. Max server memory set: In that case SQL Server process might be paged and the free memory given to other requesting SQL Server process. SQL server cannot ( barring few cases) go beyond max server memory value set.

    2. Max server memory not set: SQL Server if allowed can go and if OS responds it can get memory from OS. But this will happen making sure OS does not lacks in memory for its processes.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2