March 2012 - Posts

Decoding an affinity mask
07 March 12 10:18 PM | GavinPayneUK | with no comments

Recently, in preparation for my SQLBits NUMA internals session I began looking at some of the SQLOS DMVs and trying to understand how their contents directly related to the physical server architecture that SQL Server was running on.

While their contents used regular terms such as node and affinity mask the results were often in an “internals” format that can be distracting to the human reader.  An example of this is the DMV sys.dm_os_nodes (link to Technet here), or more specifically the column cpu_affinity_mask which returns a bigint value that bears no resemblance to the server’s hardware configuration.  Although, as many of you will probably know, the bigint value is a decimal representation of a bitmask that represents the processor value.  To show what I mean, below is an example of the output from a server we manage:

NUMA Node CPU Mask – Decimal
0 65535
1 4294901760
2 281470681743360
3 -281474976710656

So how do we turn the –281474976710656 value into something meaningful?  The answer comes from a query I found on the SQLServerCentral forums which I then modified, the original link is here, while my version is shown below:


bits as
(select 7 as n,128 as e union all select 6, 64 union all
select 5, 32 union all select 4, 16 union all select 3, 8 union all
select 2, 4 union all select 1, 2 union all select 0, 1), bytes as
select 1 m union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9)
select node_id, cpu_affinity_mask,
select convert(varchar(3), (( convert(tinyint,
substring(convert(binary(9), cpu_affinity_mask), m, 1))
e )) / e) as [text()]
from bits
cross join bytes
order by m, n desc
xml path('')
as binaryString
from sys.dm_os_nodes
where node_id < 64
This then gives the more human readable results as follows:

NUMA Node CPU Mask –Binary
0 0000000000000000000000000000000000000000000000001111111111111111
1 0000000000000000000000000000000011111111111111110000000000000000
2 0000000000000000111111111111111100000000000000000000000000000000
3 1111111111111111000000000000000000000000000000000000000000000000
For those not familiar with the output, each “bit” is a logical CPU within the server.  In this example, the bits set to 1 represent logical CPUs which are assigned to a specific NUMA node, more about that in my SQLBits session!