March 7, 2016 at 3:27 am
Hi Experts,
In our test environment we have 2 instances of SQL Server running on 8 core machine.
Can I split the CPU between these instances?
What is the purpose of Processor Affinity Mask & IO affinity Mask. I have observed that if we chose Processor Affinity for CPU 1 IO affinity cant be set for thatt CPU ,why? Can someone explain this in simple terms?
March 7, 2016 at 12:25 pm
Yes, you can, and you could use those affinity masks for that (but read the note below). So with your eight cores, you could for instance assign two cores to instance 1 and the other six to instance 2; the affinity masks for this could for instance be 3 and 252 (which corresponds to binary 00000011 and 11111100 - every 1 means that the node can be used by the instance).
However, I am not sure if this is smart. Especially on dev and test boxes, you will sometimes be using one instance, sometimes the other, and hardly ever both at the same time. So why not allow both instances access to all nodes? They will do some fighting over the resource when both instances are used hard, but that will probably happen very rarely on a test box.
To address your question about affinity mask and affinity I/O mask, they are used for different purposes. The affinity I/O mask can be used to reserve a node to do I/O only, and remove it from the pool of processors for queries. This is a very specialised tuning option that is only needed in very few, very extreme cases. But you are right that there is a connection: a processor that is enabled in the affinity I/O mask must be disabled in the affinity mask. However, I recommend not using the affinity I/O mask unless you have definite proof that it really is needed in your environment. A longer explanation with pretty pictures is here: https://blogs.msdn.microsoft.com/psssql/2010/11/19/how-it-works-io-affinity-mask-should-i-use-it/[/url].
And finally, instead of using the affinity mask option, which is deprecated, you should use the ALTER SERVER CONFIGURATION SET PROCESS_AFFINITY syntax. (If you decide to do this at all).
March 8, 2016 at 3:23 am
Thanks a lot Hugo. Really appreciate it.
Makes things clear for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply