November 2, 2007 at 1:03 pm
Hello Everyone,
I'm using a server with 30gigs of RAM and would like to install 5 instances of SQL 2005, each instance would have 4 gig of RAM.
Now I know that you need to set each instance with MIN and MAX values, but how is this done? Do you set each instance with MIN value of 0 and MAX value of 4gig? Or is the 1st instance set to MIN=0, MAX=4 and the 2nd instance MIN=4, MAX=8??
Documentation is little in this area and any help/comments would be great.
Thank you in advance for your help.
Rudy
November 4, 2007 at 10:05 am
Set min and max to the same value.
November 5, 2007 at 6:44 am
You say set the min and max to the same value, can you explain this a little more?
Are you saying that on a 30gig of RAM on a server, if I want to give 5gigs to 4 instance, each instance would have the same setting for memory which would be 5gig (max and min setting)?
Thanks in advance for you help on this.
Rudy
November 5, 2007 at 6:55 am
Yes.. Set Max to 5G for each of the 5 instances for a total of 25G. You can also set the Min to 5G which will LOCK all 5Gig for each instances. You can also work with the min value if say one of the instances is less critical.
Say 4,6 for four of the instances
and 1,4 for the 5th.
That would give you a worst case of all 30G used, but since all of them have room to allow it to go down, you should still be ok with that situation.
November 5, 2007 at 7:03 am
Ok, I see. So that means that when setting memory setting for an instance, you set the values for that instance and not for the OS. What I'm trying to say is that you just tell SQL server how much you want (on each instance) and not where in the memory.
Is there any good articles on this? I would think more people are looking for this information.
Thanks a terrabyte!
Rudy
November 5, 2007 at 7:30 am
Given you are new to this. Why are we talking about 5 instances? You wouldn't have an Oracle Background would you? There are good reasons for more than one instance, but there are bad ones too.
The biggest reason for multiple instances would be security issues.
Second might be to limit impact from one application to another. However different servers would be more effective.
November 5, 2007 at 7:43 am
No Oracle background here. What we are thinking of doing is putting 5 SQL servers on to one large server. We have an HP585 with 8 cpu and 64gigs or RAM and would like to consolidate onto this server. It is running Windows 2003 R2 x64Bit and SQL 2005 64Bit.
The initial plan is to place 5 instances and then see how performance is and them added 2-3 more instances. Each instance would need a min of 4 gigs of RAM each.
Any experience in this?
Rudy
November 5, 2007 at 8:05 am
is this 32 bit or 64 bit?
0 and 4GB would be workable for each of the 5 instances. Avoid setting min and max equal to each other. You should give some space between the two for some reason which I can't seem to recall at the moment. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 5, 2007 at 8:29 am
These sql servers are currently on a 32bit environment, but the consolidated server is completely 64 bit and connected to a HP SAN (I forgot to mention that).
Since SQL 2008 is the last 32 bit application, we figured that it would be good to get 64 bit in house and be ahead of the game.
Thanks again for all the great help! That's what I vote this site to the be the best of the SQL world!
Rudy
November 5, 2007 at 11:31 pm
Rudy -
One comment - have you looked at that I/O patterns of your existing databases? CPU/RAM are important, but unless you can load entire databases into memory high speed access to disk is absolutely essential. If I remember correctly a DL585 has either 4 or 6 busses some at 100Mhz, others at 133Mhz, which mean that you need to pay attention to the mapping of both memory and PCI buses to processors or you can end up with some weird behavior in regards to both bus speed and I/O contention.
Joe
November 6, 2007 at 3:19 am
In addition to setting min/max memory on each instance, could anyone comment on MS's recommendation to 'lock pages in memory' when using Ent Edition on 64 bit servers (refer to KB 918483)? We have seen situations where our working set was being trimmed and MS said to lock pages in memory for the SQL Server service account. This can cause problems if max mem is not set appropriately on the instances running on that server. Just wanted to get some other real-world opinions! Also any other advice on consolidation? Thanks!
November 6, 2007 at 6:39 am
Thanks for your comments. I am in the process of performing real time monitoring on the current 5 SQL servers and will calculate the max I/O, data through put, memory usage, CPU usage, etc. These servers are not high demand but monitoring them is still a good idea. You are right about the slots in the DL585, need to be careful to only use the 133Mhz bus slots. We will be using 2 HBA LSI Logic cards to connect to our SAN unit and are teaming the 2 built in network cards to provide 2 Gigabytes of network connections. This will become our "pilot" project and if we see that this solution is not the best, we will "pilot" the PolyServe product for our consolidation needs.
Rudy
August 6, 2008 at 2:01 pm
I'm coming into this thread a little late but would you clarify your last comment, Joe? How do you handle memory mapping - by setting affinity? Do you have an example?
TIA,
Debbi
One comment - have you looked at that I/O patterns of your existing databases? CPU/RAM are important, but unless you can load entire databases into memory high speed access to disk is absolutely essential. If I remember correctly a DL585 has either 4 or 6 busses some at 100Mhz, others at 133Mhz, which mean that you need to pay attention to the mapping of both memory and PCI buses to processors or you can end up with some weird behavior in regards to both bus speed and I/O contention.
Joe
August 6, 2008 at 2:14 pm
in reading this discussion, i thought it'd be pertinent to post this here:
since you mentioned w2k3 and sql05 x64, when you copy a large file from a fast disk to a slower disk (on the same computer or another machine), windows will read as much of the file as it has RAM to hold into RAM, taking RAM away from other applications, including SQL, regardless of the min/max settings. for example, if you copy a 100gb file from a fast san to a slower hard drive, in your case, windows will read 64gb into RAM before it starts the actual copy to the slower disk. the system will ignore any memory locks and will take RAM away from SQL. eventually, SQL will stop accepting new connections and will eventually freeze.
if you're running sql enterprise, there's a fix for this, but i don't know if it really works (we don't run enterprise here).
just thought i'd mention it since you're running 5 instances w/4gb minimum.
August 17, 2009 at 9:54 am
Folks, is there a good link to study administration of SQL Server 2000 and 2005 multi-instancing administration? Particularly, I need to see how can I query all the isntances from one-admin-one on the same box. Also, what are the best performance considerations for memory usage? Should I just allocate 2G per each or should I just let it take what it wants considering the fact that loads on any of the istances won't be heavy. Security isnt an issue since there are different sa-s and all the local admins are OS-driven and their passwords change every week.
This deserves a stand alone article by a good MSVP.. I think.. everyone was waiting for multi-instancing heaven from MS for such a long time.. c'mon...
if one wants it.. one will justify it.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply