CPUs Used for a SQL Server Instance

Question

Does anyone know how to extract the CPUs used for a give SQL Server instance?

The information required for the cpu_affinity_mask can be extracted from SQL Server thus:

SELECT * FROM sys.dm_os_nodes WHERE memory_id <> 64

I have looked at the affinity mask, which is meant to give this information as a decimal string. As far as I know, you then have to convert it to a decimal string and then each decimal value (from right to left) relates to a specific CPU:-

CPU Affinity Mask: 1099510579200 (Decimal)   
CPU Affinity Mask: 111111111111111111100000000000000000000 (Binary)

Therefore Processors used should be:
21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40

BUT if you bring up the server properties, and then look at the processor affinity, only 22 to 33 are flagged as having an affinity.

Any help from the SQL Server gurus out there would be much appreciated.

Answer 1

cpu_affinity_mask in sys.dm_os_nodes specifies which CPU's are connected to a particular NUMA node. This is related to the physical layout of cpus and memory controllers on the motherboard, not which CPU's are assigned to SQL Server.

'affinity mask' and 'affinity64 mask' in sys.configurations configures the cpus that are assigned to sql server, and will correspond to rows in sys.dm_os_schedulers where status = 'VISIBLE ONLINE'.

affinity mask configuration option