November 2, 2009 at 5:17 am
I'm involved in setting up a new database cluster for SQL2005 in W2008. On the nodes several instances of SQL Server will be installed. One of the things I am thinking about is should we assign dedicated processors to instances of SQL Server, thus assigning more processor power to heavily trafficked instances. We do this with memory and assigning (fast) drives on our SAN. I am unable to find articles on wheather this is a good thing or not, probably because I cannot come up with a suitable search phrase.
What experiences do ppl have with this? Are there any links to articles on the merits of such a division?
Greetz,
Hans Brouwer
November 2, 2009 at 6:17 am
I personally found it to be better assigning the limit on what sql can use.
Leaving everything available to sql can cause the OS to stop responding, as was my experience.
November 2, 2009 at 3:17 pm
I think each scenario is different, but as long as the servers in the cluster have the same physical elements, i.e. they have the same amount of CPUs I do not think it will cause an error. I think the main issue with CPU affinity is when it is done on VMs, which does not sound like that is your case.
That being said though, this needs to be tested on a non-production system. No matter what advice you get it would not be fun to test this on a live system only to find out what you planned for did not work.
Joie Andrew
"Since 1982"
November 2, 2009 at 3:44 pm
Processor affinity should probably be tested carefully. But if you have pretty fast machines with lots of cores, I would think that your bottleneck would probably NOT end up being your processors, but your IO subsystem (ie: disks). However, mileage may vary. I've seem a LOT more systems bog down because of disk and memory that CPU. But it depends on your app so it is hard to tell. Also keep in mind you have to do the processor affinity on each SQL instance keeping in mind ALL instances on ALL other boxes, so on box 1 you might allocate cores 1-4 to instance 1, and 5-6 to instance 2, then when yo go to box 2 you would probably start at core 7, etc..
Short answer, I wouldn't do it. I would need a good reason to do it LATER. Be careful how many instances you have running. And let them use the resources they have, monitor for problems. If you have a problem instance you might just limit it..
CEWII
November 3, 2009 at 2:31 am
Tnx for your input all. This has been very helpful and educational.
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply