A memory recommendation is to reduce MaxMemory by 4GB or 10% of RAM (equally divisible by cores) to dedicate that memory for the OS.

Most questions on CPU affinity deal with splitting them out between instances.

My question is should one (or more) cores be deselected for OS only use? One site did this on a Win2003/SQL 2005.

Q1: Was this a beneficial thing to do? As I understand, older Win versions put everything in NUMA 0 so it sort of made sense. But I just recently learned that SQL was aware of that and favored NUMA 1+ to offset. And now, as I understand it, the newer OS (Win2008+) utilize NUMA and spread their CPU usage across the cores.

Q2: So, even if it was a good idea under older Win/SQL versions is it still a good idea under newer Win/SQL versions?


Changing CPU affinity was never a common practice but did have it's uses on WindowsNT and later on Windows Server 2000/3,

The main issue was that processor load could be misaligned on multiprocessor systems and this allowed for freeing up resources. This could also be helpful on systems that where not dedicated to running SQL Server. So yes this could be beneficial in some edge cases. Old Small Business Servers come to mind.

On later versions of Windows the OS is more likely to distribute load between processor so for performance reasons this will not help but can be beneficial on servers running multiple instances if you want to limit those to specific CPU or NUMA node

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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