Another way to limit CPUs is to use the Windows NUMPROC boot-time option. This limits the number of processor cores presented within Windows. If you use NUMPROC 20 on a 24-core server you are effectively turning it into a 20-core server.
There is some advice about using processor affinity to separate IO and CPU usage. Some configurations can boos performance, but if you get the balance wrong then affinity may not help you.
Also it is worth being aware that prior to Windows 2012, all IO activity is scheduled via core 0 on each CPU chip. If you do set affinity, then do not try to make core 0 set for CPU use only, as it will get some IO load regardless. With Windows 2012 and above, IO can be scheduled by any core.
Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016
: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara