April 9, 2010 at 2:20 pm
My organization is in the process of acquiring a new database server. The server will have 2 quad-core processors (effectively 8 processors). In addition, we are looking to consolidate our two environments (i.e. 2 SQL Servers) into this one server. The plan is to install two instances of SQL on the one server. My question is, is it best to allow each instance of SQL to handle the processor and I/O affinity or would it benefit to change the processor affinity to possibly dedicate 4 CPUs to one instance of SQL and the other 4 to the other instance? Thanks in advance.
April 9, 2010 at 4:44 pm
Let SQL make the decision. If you dedicate 4/4 and the first instance is using 100% (hence processor queueing) and the second instance is using 5%, you have available cycles that are going unused and performance issues.
The only time I have limited what SQL can use is if it's a shared server and I just don't want SQL using everything (which means SQL will be slower than it could otherwise operate).
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 10, 2010 at 10:05 am
It depends on what the two instances are for - it's really impossible to say one way or the other without knowing more about your situation.
In general, though, if the workload is roughly balanced, you might see an advantage by configuring each instance to use separate processors - especially if your server uses hardware NUMA. Returns on general parallelism diminish pretty quickly for many tasks for MAXDOP > 4 anyway.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 10, 2010 at 11:34 am
In my opinion - analyze the CPU requirement of each instance and allocate those many CPU's to each instance.
At any point of time if one instance takes all CPU's it will not affect the another.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply