Solved

how many extra RAM for SQL server is needed

Posted on 2016-10-06
23
49 Views
Last Modified: 2017-04-18
hi,

I read all that:

https://www.sqlpassion.at/archive/2016/09/19/how-much-ram-do-i-need-for-sql-server/?awt_l=FjEA6&awt_m=3Vt_2eQyB6YUUTS
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

none of them are telling me how much MORE RAM we need for the SQL server.

I am measuring a SQL server box which is a publisher and distributor, the more replication we create the more RAM contention we have.

but how to measure/calculate HOW MUCH RAM we still need?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
23 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41831328
Basically it depends on the workload & requirement of your system.

Have you gone through the Brent Ozar blog. He is very good with hardware stuff. few links for your help.

https://www.brentozar.com/archive/2014/05/much-memory-sql-server-need/

https://sqlperformance.com/2013/10/sql-memory/how-much-ram

https://technet.microsoft.com/en-us/library/cc298801.aspx

Enjoy!!
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41831331
Let's take an example. If we have total of 80GB of database then we don't need 64GB of RAM. So we need to make assumtion like how much % of the entire database is going to be in use at a time.

In most of the OLTP cases we use about 15-20% of the entire DB at a time.
A BI system may use about 60% of the entire DB at a time.

So What kind of System we are developing and What's the workload are very important to start with.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831417
nono, there are no calculation + performance counter to measure that out
?


boss can simply ask, how much RAM extra I need ?

actually this one don't tell much as it is more on Sharepoint deployment and planning.

Or only this one is useful:

Memory: Pages/sec   This counter shows the rate at which pages are read from or written to disk to resolve hard page faults. Monitor this counter to make sure that it remains under 100.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41831422
I will tell you before that can you tell what kind of system we are developing ?

OLTP / OLAP - Tabular / Mutildimensional ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831429
just normal transaction database.

Mutildimensional usually but we don't actually use most of it.

I am measuring pages/sec, page life expectancy. % disk read time, % disk write time.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41831436
Can you also tell me the size of DB ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831437
831 GB. we are using SQL 2008 with SP3 and we can't use in-memory tech.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41831448
Okies so in my opinion you should go with 96GB of Ram.

Note that it depends on lot of factors. This is just an estimate.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831451
this is the problem, we are running 64GB of RAM now, how you comes up with 96GB of RAM?

the SQL box is both publisher and distributor. any rules by saying how much RAM per publication need ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41831461
Okay , so upgrade to 96GB first. :) Details below-

At one time you need around 45 GB of  Data in RAM , which is 20 % of the overall DB size we have.

Now we should max given 50% of the RAM for SQL DB Engine. So effectively we need to double the RAM.

In your case it comes out to 90. and 96 is the closest, that's why I choose it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41833131
"At one time you need around 45 GB of  Data in RAM "

we have 831 GB database size, why 45GB? I think it is 166GB.

"Now we should max given 50% of the RAM for SQL DB Engine. So effectively we need to double the RAM."

why 50% ?
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41833136

"Now we should max given 50% of the RAM for SQL DB Engine. So effectively we need to double the RAM."

You have define what max ram you want to give to SQL Server out of the total RAM available for the entire machine , You must provide RAM for other processes also like Operating System , office,,etc  

"At one time you need around 45 GB of  Data in RAM "we have 831 GB database size, why 45GB? I think it is 166GB.

Yes you are write, that is a typo.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41833137
so it is not 96GB of RAM, right? I have to proof that this is exactly what we need to add.

and also how much RAM extra each additional replication/publication need?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41833144
No we need 96 GB only. Additional how much need for replication I need to check.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41833254
"Additional how much need for replication I need to check."

tks.

a calculate with all variable you discussed is preferrable, like database size is a varible , which make your conclusion of 96GB of RAM with link to proof is perfect.

"No we need 96 GB only"

I don't know what make this figure comes out is needed, tks.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41833348
there is no Maths around this.  We just have to decide based on our requirement and later on increase the hardware when the load grows.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41837913
"We just have to decide based on our requirement "

what requirement ? if I tell my boss says for each publication we need extra 20B of RAM because of ......., then it is much better.

we add one more publication it will use RAM again.

"and later on increase the hardware when the load grows."

what if during the weekend we will see unresponsive replication agent error message or long running replication error message. usually we see it during the weekend only. so can't add RAM because of load grows.

we need time to order it.

and I saw before that publisher shows replication tempdb contention and a lot of time this replication agent log gone without any reason.

any tools you can suggest me to use to detect if there are ?

I knew there are tempdb speed measurement on write speed latency, but for replication ,how can we measure we need to do sth on the tempdb.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41837979
Well there are many tools available in the market. There are many good from redgate.

http://www.red-gate.com/products/

Other tools to check performance are -

Database health monitoring <<http://databasehealth.com/>>.
Cloudmonix.  <<http://cloudmonix.com/blog/the-ultimate-list-of-top-sql-monitoring-tools/>>

I have not tried these. Freeware or not - verify. You may be need try out few and choose based on your need.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41837982
then forget about it, I will tried to make use of the idera DM for that.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838020
No problem. If Idera works for you then it is great.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41838126
I am using this guideline for that:

https://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/

and it seems publisher still have just enough RAM.

let see what will be told by DM.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41838127
tks.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 42098108
hi,

I re read that it seems no fixed calculation on how much RAM we need further for our SQL server?

and this one: https://www.brentozar.com/archive/2014/05/much-memory-sql-server-need/

don't even tell us at all, it just say need more RAM but how much RAM ?  I am happy if there are a performance counter check this.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 52
SQL syntax question 6 74
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 64
Need more granular date groupings 4 45
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question