RAM to each Instance

Question

RAM to each Instance

David Rich

Right there with Babe

Points: 750
More actions
November 28, 2015 at 12:58 am

#322930

We are designing a new environment . 2 node cluster with 3 instance . 2 Instance in Node 1 and 1 instance in Node 2 . 64GB per node.
How to split the RAM between these 3 instances??
Expected growth of databases is 1.4TB for first 2 instances and 600GB for second instance.
Concern is when the failover happens to 1 node. or do we need to change the failover nodes from 2 to 3??

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

When you start talking about tera-bytes for an instance, I'd start thinking about a lot more memory than 64GB per node and I wouldn't think about splitting it up much. Either you're in business, or you're not. Skimping on hardware doesn't make sense especially when it comes to RAM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Jeff Moden (11/28/2015)
When you start talking about tera-bytes for an instance, I'd start thinking about a lot more memory than 64GB per node and I wouldn't think about splitting it up much. Either you're in business, or you're not. Skimping on hardware doesn't make sense especially when it comes to RAM.

I have to agree with Jeff 1000% here. If you're using SQL 2014 SE, then the max SQL Server will use is 128 GB. When you compare the price between 64 GB and 128 GB, it's almost inconsequential when you consider the cost of your SQL licensing. If you're using SQL 2014 EE, then it can use as much as the server and OS can support. When you look at the high price tag of EE, you want to feed it as much power as you can to utilize your investment as much as possible.

SQL Server loves memory. Skimping out on memory is handicapping your environment right out of the gate.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Agree with above. If you have a 2 node cluster then if there is a failover, you will be sharing 64GB among three instances. If you don't set max/min for the instances, then you'll have them balancing the RAM as best they can.

Realistically, you'd really want a three node cluster so that if there are failovers, you don't overload the remaining node. While 2 instances will be fighting on a failover for resources, that's better than three instances, which could cause all the instances to appear to stop responding with starved processes.