February 10, 2017 at 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,
February 10, 2017 at 7:19 am
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
February 10, 2017 at 7:20 am
Judy Why - Friday, February 10, 2017 7:06 AMHello 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): 16GBwhich 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?
😎
February 10, 2017 at 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
February 10, 2017 at 7:27 am
Eirikur Eiriksson - Friday, February 10, 2017 7:20 AMI 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.
February 10, 2017 at 7:32 am
I use the rule you cited, so I'd say give it 26 GB (26624 MB).
February 10, 2017 at 7:34 am
Judy Why - Friday, February 10, 2017 7:27 AMEirikur Eiriksson - Friday, February 10, 2017 7:20 AMI 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?
😎
February 10, 2017 at 7:38 am
John Mitchell-245523 - Friday, February 10, 2017 7:26 AMWhen 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
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.
February 10, 2017 at 7:39 am
the server is dedicated server, no SSRS, no SSIS, no SSAS running.
February 10, 2017 at 7:49 am
Judy Why - Friday, February 10, 2017 7:38 AMI 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.
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
February 10, 2017 at 7:56 am
Judy Why - Friday, February 10, 2017 7:39 AMthe 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.
😎
February 10, 2017 at 7:59 am
John Mitchell-245523 - Friday, February 10, 2017 7:49 AMJudy Why - Friday, February 10, 2017 7:38 AMI 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.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