1

I'm running a 2008r2 EE version of SQL Server on a machine (Not a VM) with 4 Sockets of Intel® Xeon® Processor E7-8880 v3 with Hyperthreading enabled. I'm attempting to validate with the affinity for both Processor and I/O that my application can run on less cores so I'm manually setting the Processor and I/O to balance the usage so no one core is used for both operations. However once I got to the third NUMA node (i.e CPU 64) I am only able to set the usage for the Processor Affinity and the I/O Affinity is grayed out.

The documentation is a bit confusing as it's making references to CPUs and not cores/processors but does reference having to set the affinity64 options to control processors 33-64 even though on the Processor side of things that seems to be controllable in the UI even when the I/O portion isn't. I haven't found anything that says the I/O is only able to be bound to the first 64 cores of a system.

I have another physical box with two CPU sockets that have 16 physical cores with hyperthreading enabled to provide 32 cores per CPU. On this server I'm able to control all of the cores regardless of if they are a Hyperthread or not.

Why I am I restricted from setting the I/O Affinity on all 128 visible cores? Does it have to do with Hyperthreading?

2
  • @DanGuzman Result is 128.
    – Aaron
    Sep 23, 2021 at 19:56
  • 1
    "Not a VM" so use a VM. If you really must use software from the ancient past, always run it in a virtualized environment. Let Hyper-V on Windows Server 2019 interact with the modern hardware, and install some old version of Windows in a VM for your old version of SQL Server. Sep 25, 2021 at 15:41

1 Answer 1

6

I'm running a 2008r2 EE version of SQL Server [...]

You're also out of support by many years. Good luck with this endeavor. This "out of date" theme will continue through this answer so I thought it was appropriate to point out.

However once I got to the third NUMA node (i.e CPU 64) I am only able to set the usage for the Processor Affinity and the I/O Affinity is grayed out.

Correct. I/O affinity is not available past 64 cores. Why? It's been deprecated ever since it was created. It was a benchmark-ish special configuration and generally causes way more issues than performance. It's deprecated so hard there isn't even support for it past 64 processors. Again, it's completely out of date and supported only in the sense of you can do it given the limitations, albeit it's not a smart move.

The documentation is a bit confusing as it's making references to CPUs and not cores/processors [...]

CPU is the same thing, generically, unless stated otherwise such as "hyperthreaded-cores" which aren't full execution units. Whether or not the pedantic among us want to get into the debate is up to them. Most use cpu/core/processor interchangeably.

I have another physical box with two CPU sockets that have 16 physical cores with hyperthreading enabled to provide 32 cores per CPU. On this server I'm able to control all of the cores regardless of if they are a Hyperthread or not.

Correct, because it's 64 cores or less.

Why I am I restricted from setting the I/O Affinity on all 128 visible cores?

Because of the attempt to use a deprecated and out of date part of the product (but hey let's keep it in for backwards compatibility) that is marked to not be used in the Docs on a product that is 11 years old and out of support on hardware that wasn't readily available 11 years ago when the product came out and thus the feature wasn't designed for it.

3
  • Yep, not my choice for the version we are on. Several of the items you posted in your answer give me pause to even bother with manually setting the Processor side while letting the I/O side be automatically set. Is that route an equally bad idea?
    – Aaron
    Sep 23, 2021 at 20:02
  • 1
    99.9%+ of the time SQL systems are configured with the system responsible for cpu & io affinity & as Sean answer says those options are really there for lab benchmarking jobs & rarely scene elsewhere. In short that route is a good idea and the norm Sep 24, 2021 at 7:03
  • 1
    @Aaron Correct, I wouldn't change these options. Let it be default which is to use all. Sep 24, 2021 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.