Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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 Jul 26 '12 at 20:43
1  
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. –  Aaron Bertrand Jul 26 '12 at 20:59
    
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! –  SQL3D Jul 26 '12 at 21:03
    
Thanks! A lot of work has gone into it and it's been great hearing all the feedback. –  Aaron Bertrand Jul 26 '12 at 21:04
1  
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. –  Jon Seigel Jul 27 '12 at 0:24
show 1 more comment

3 Answers

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 :)

share|improve this answer
add comment

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.

share|improve this answer
    
Can you elaborate on this with some references or some other info? As it stands this is almost a comment. Thanks! –  JNK Nov 27 '12 at 14:06
add comment
up vote 1 down vote accepted

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...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.