I have a 2016 Enterprise db server with 4 processors and 24 cores which is CAL licensed. So only the first 20 cores are used. Now I would like to set affinity so SQL Server will not use the first 20 cores (6 of the first 3 processors and 2 of the 4th processor), but will use 5 cores per processor. What do I do with I/O affinity? Leave it on 'automatically'? When both CPU affinity and I/O affinity are not set to 'automatically', you have to choose between CPU and I/O. When I manually select 20 of 24 cores for CPU affinity, will the 'automatically' setting for I/O affinity mean that only the remaining 4 cores will be used for I/O?
Been a long time since I have played around with this setting.
The point of setting up CPU affinity is so you can manually load balance the CPUs and make sure certain task always have resources and do not compete with each other system loads.
If the affinity is set to automatic MSSQL CPU scheduler will pick a CPU for the load. This has the advantage that if a CPU becomes loaded and other CPUs are unloaded it can switch to an unloaded CPU
But if the affinity is set MSSQL scheduler will not switch the load to another process unless its in the affinity list.
IO and CPU both have an affinity list and can be controlled independently. Setting one to Automatic and the other to a specific list means the MSSQL scheduler can move one around automatically
Key point do not set both OS affinity and MSSQL it can cause interesting problems
FROM the help files
When specifying the affinity I/O mask option, you must use it in connection with the affinity mask configuration option. Do not enable the same CPU in both the affinity mask switch and the affinity I/O mask option. The bits corresponding to each CPU should be in one of these three states:
0 in both the affinity mask option and the affinity I/O mask option.
1 in the affinity mask option and 0 in the affinity I/O mask option.
0 in the affinity mask option and 1 in the affinity I/O mask option.
It also suggested by MS and many others not to configure this unless there is a clear need to exclude CPU's and/or manually load balance resources.