what max server memory (MB) should be?

  • Hello all,
    I have a dedicated SQL Server, Windows server 2012 R2 x64 GUI and SQL Server 2014 x64. Total Physical memory is 32GB, logical processes is 8, total database size is around 200GB.
    I have set my max server memory (MB) is 26GB which is same as the result using http://www.sqlservercentral.com/scripts/max+memory+setting/86998/

    But another  SQL Server Memory calculator gave me suggestion is 16G.  it suggested:
    Windows Server Memory Size: 5.60GB
    SQL Server Plan Cache:5.80GB
    SQL Server MTL: 4.0GB
    SQL Server Process Memory(DLLs): 1GB
    SQL Server Buffer Pool (max memory setting): 16GB

    which one, 16GB or 26GB should I use in Max Memory setting?

    Thanks,

  • I'd probably set it at 24gb and leave 8gb for the OS. 32gb is not much for a production server so keep an eye out for any memory alerts as you may need to add more memory to the server.

    Thanks

  • Judy Why - Friday, February 10, 2017 7:06 AM

    Hello all,
    I have a dedicated SQL Server, Windows server 2012 R2 x64 GUI and SQL Server 2014 x64. Total Physical memory is 32GB, logical processes is 8, total database size is around 200GB.
    I have set my max server memory (MB) is 26GB which is same as the result using http://www.sqlservercentral.com/scripts/max+memory+setting/86998/

    But another  SQL Server Memory calculator gave me suggestion is 16G.  it suggested:
    Windows Server Memory Size: 5.60GB
    SQL Server Plan Cache:5.80GB
    SQL Server MTL: 4.0GB
    SQL Server Process Memory(DLLs): 1GB
    SQL Server Buffer Pool (max memory setting): 16GB

    which one, 16GB or 26GB should I use in Max Memory setting?

    Thanks,

    I would start with 26-28Gb and monitor the server for any signs of memory pressure, obviously this depends on which other activity is on the server, i.e. SSMS, SSIS etc. Question, do you RDP on to the server when managing it?
    😎

  • When was that second memory calculator posted online?  Technology changes, and MemToLeave isn't an issue in modern versions of SQL Server.  The memory allocated to the plan cache is included in the memory that you assign with max server memory.  Therefore, I'd go with the 26GB figure, and possibly increase it to 28GB if, as you say, your server is a decidated server (no SSRS, no SSIS running large ETL jobs).

    John

  • Eirikur Eiriksson - Friday, February 10, 2017 7:20 AM

    I would start with 26-28Gb and monitor the server for any signs of memory pressure, obviously this depends on which other activity is on the server, i.e. SSMS, SSIS etc. Question, do you RDP on to the server when managing it?
    😎

    I have RDP, but I normally remotely manage server.

  • I use the rule you cited, so I'd say give it 26 GB (26624 MB).

  • Judy Why - Friday, February 10, 2017 7:27 AM

    Eirikur Eiriksson - Friday, February 10, 2017 7:20 AM

    I would start with 26-28Gb and monitor the server for any signs of memory pressure, obviously this depends on which other activity is on the server, i.e. SSMS, SSIS etc. Question, do you RDP on to the server when managing it?
    😎

    I have RDP, but I normally remotely manage server.

    Any other services running on the server?
    😎

  • John Mitchell-245523 - Friday, February 10, 2017 7:26 AM

    When was that second memory calculator posted online?  Technology changes, and MemToLeave isn't an issue in modern versions of SQL Server.  The memory allocated to the plan cache is included in the memory that you assign with max server memory.  Therefore, I'd go with the 26GB figure, and possibly increase it to 28GB if, as you say, your server is a decidated server (no SSRS, no SSIS running large ETL jobs).

    John

    I got this http://www.sqlservercentral.com/blogs/sql-and-sql-only/2016/12/31/data-channel-interview-02-hamid-fard-on-sql-server-memory-capacity-planning/ 

    inside this topic, there is a link  http://fard-solutions.com/sql-server-memory-capacity-planning/
    it talks about how to caculate memory. it suggested 16G.

  • the server is dedicated server, no SSRS, no SSIS, no SSAS running.

  • Judy Why - Friday, February 10, 2017 7:38 AM

    Fair enough - the article isn't as old as I suspected it was.  But read the comments at the bottom of the article in your first link above, then make your own mind up.

    John

  • Judy Why - Friday, February 10, 2017 7:39 AM

    the server is dedicated server, no SSRS, no SSIS, no SSAS running.

    Normally leave between 2-4Gb for headless servers, 4-6 for servers without regular RDP activity and if anything more  on the server one will have to assess the memory requirement of that activity and factor that in.
    😎

  • John Mitchell-245523 - Friday, February 10, 2017 7:49 AM

    Judy Why - Friday, February 10, 2017 7:38 AM

    Fair enough - the article isn't as old as I suspected it was.  But read the comments at the bottom of the article in your first link above, then make your own mind up.

    John

    I  read the comments.  I would say I will keep my original setting (26GB).  This article is so confused especially for those DBAs like me without in-depth solid knowledge about Memory. 

    I suggest forum admin to take off this article from this forum.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply