Affinity mask on the separate instances question

Question

Hi,

we have sql server 2008r2 cluster with production instance installed on it. Since we are consolidating another environment to this server, we are considering installing separate named instance. It is not as high usage as current production, and it runs processes only once in a while (there are some restrictions why we cannot keep it on production instance).

We have 16 CPUs and 128GB of memory on this cluster. So far, production has max sql server memory set at 110GB (the rest is for OS and some apps). We want to lower max memory on production instance to 90GB, and set new instance to 20GB. However, we are wondering about setting up CPU affinity mask (i.e. to cap new instance to 4 CPUs). Here is the question:

If we set affinity mask to 4 CPUs on the new instance, and leave production instance intact (no affinity mask), are those new instance's 4 CPUs going to be available to production instance (assuming that nothing or at least small load only is running on the new instance), or production will have only remaining 12 CPUs available at any time? 

Thanks,

Pedja

---

Pedja

Answer 1

Hello,

To my knowledge, you will have to configure affinity on both instances, to tell each instance on what CPUs they can run their threads. You will see examples on the following article:

http://msdn.microsoft.com/en-us/library/ms187104(v=sql.105).aspx

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com