SQL Server 2012 Standard Edition - multiple instances and memory utilization

Question

If we have multiple instances of a SQL Server 2012 Standard Edition (which has a 64 GB memory limit) on one server which has 192 GBs of RAM, do both instances only have access to the first 64 GBs of memory, or can they access different portions of memory, so that they can each have their own 64 GB "chunk".

This is in consideration for an Active/Active cluster if both nodes fail over to a single node.

• Aaron Bertrand: I believe you can use NUMA to set affinity. But (and I haven't tested this) it may very well be the case that they won't be locked to the "first" 64 GB. Sadly I don't have a box like this to test on right now (and if I did I wouldn't be wasting Standard Edition on it :-)).
• Aaron Bertrand: PS that's a multi-instance cluster. Active/active isn't a real thing and sounds like nails on a chalkboard to most, if not all, HA/DR folks.
• SQL3D: Aaron, I couldn't agree more. Unfortunately, in this situation, I might be stuck with this (although I'm still working on that). BTW, love the new sqlperformance.com site!
• Aaron Bertrand: Thanks! A lot of work has gone into it and it's been great hearing all the feedback.
• Jon Seigel: Call Microsoft Sales... This is a totally valid question, but just thinking about the overhead required to coordinate capping the memory like that... it would be performance suicide. And I've yet to hear anyone complain about bumping into a limit like this.

Answer 1

Knowing that two instances are absolutely not related to each other, i guess that you can use two instances on one server with 64gig for each. At least to my understanding, OS is the one who assigns memory to apps, so it should be fine :)

Answer 2

Some time ago, I had the same question. Sales reps from MS related company answered, that indeed, this is the case - you can use 64GB for each instance. They even went to say, that this is totally valid and part of the idea.

Unfortunately, I haven't yet a chance to test it.

• JNK: Can you elaborate on this with some references or some other info? As it stands this is almost a comment. Thanks!

Answer 3

I just wanted to follow up with a definitive answer on this. SQL Server Standard instances are not limited to the "first" 64GBs of memory. So, in the case above, on a server with 192 GBs of RAM, you can have 2 SQL Server instances both able to use a full 64GBs of memory.

Since neither of these instances are processor bound, we have taken the additional step of assigning each instance to it's own physical NUMA node through processor affinity. Thanks to Aaron Bertrand's comment as well as Bob Ward's excellent PASS 2012 presentation, "Inside the SQLOS 2012" for configuration details...