none
SQL memory (general)

    Question

  • during years I installed SQL servers running on the same server where apps run...

    I used to limit MAX memory, always referring to advices/best practices on web.

    Just set up new SQL server that planned to contain multiple DBs and is a dedicated SQL machine.

    After reading this article:

    https://msdn.microsoft.com/en-us/library/ms178067.aspx

    I came to conclusion that it is absolutely fine to leave the default on SQL server machine.

    "Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access."

    In the article it's clearly explained that SQL accurately manages server memory.

    I guess it is right approach in standard SQL utilization and I don't have to touch memory config especially at starting point.

    BTW, can memory be configured by DB or per instance only (did not take a look at DB options)?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 14, 2017 7:02 PM

Answers

  • It may not be absolutely required to set max_server_memory, however, it's still a good idea to restrict amount of memory that SQL can address because in memory pressure situations it could take all available memory leaving Windows OS with insufficient memory to be stable.

    Memory Dangerously Low or Max Memory too High

    Memory cannot be configured within db level properties per se, however, You may want to look into Resource Governor for ways to allocate resources in a granular fashion at the SQL instance level.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, February 14, 2017 7:25 PM
    • Marked as answer by pob579 Tuesday, February 14, 2017 7:34 PM
    Tuesday, February 14, 2017 7:16 PM
  • Also, setting min memory is still really important to prevent SQL from giving back too much memory to the OS.

    Joie Andrew "Since 1982"

    • Marked as answer by pob579 Tuesday, February 14, 2017 11:41 PM
    Tuesday, February 14, 2017 8:21 PM

All replies

  • It may not be absolutely required to set max_server_memory, however, it's still a good idea to restrict amount of memory that SQL can address because in memory pressure situations it could take all available memory leaving Windows OS with insufficient memory to be stable.

    Memory Dangerously Low or Max Memory too High

    Memory cannot be configured within db level properties per se, however, You may want to look into Resource Governor for ways to allocate resources in a granular fashion at the SQL instance level.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, February 14, 2017 7:25 PM
    • Marked as answer by pob579 Tuesday, February 14, 2017 7:34 PM
    Tuesday, February 14, 2017 7:16 PM
  • Thanks. I will check your links.

    From the link I refer in my initial post it mentioned that SQL is able intelligently manage memory and protect OS from paging. That is why I was so glad :) to not touch it. 


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 14, 2017 7:34 PM
  • Also, setting min memory is still really important to prevent SQL from giving back too much memory to the OS.

    Joie Andrew "Since 1982"

    • Marked as answer by pob579 Tuesday, February 14, 2017 11:41 PM
    Tuesday, February 14, 2017 8:21 PM
  • so now I have really to double check memory configuration and not rely on Microsoft article about dynamic memory management :)

    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 14, 2017 11:42 PM
  • Miles,

    The recommendation by MS that SQL Server can aptly manage memory applies correctly after SQL Server 2012 version before that due to bug in Windows server 2003 and 2008 which used to hard trim SQL Server memory it was necessary to set max server memory with Locked pages in memory priv to SQL Server service account.

    Max server memory becomes more of a necessity if you either have multiple instance of SQL Server or some application running on OS. I normally set max server memory because keeping restriction on something which is shared keeps things under control.

    User can run query which can take huge memory depriving OS of required memory similarly a large file copy and many RDP session can increase memory requirement of OS and can pull memory from SQL Server we all know that we have little control over it.

    My advise: Set max server memory.

    Dont touch min server memory unless you really understand what it does. I have hardly seen a requirement to change min server memory


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 17, 2017 5:26 AM
    Moderator