While looking at a SQL server health report, I found affinity mask parameter in sp_configure output showing a negative value.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 -1066394617 -1066394617
Affinity mask is a SQL Server configuration option which is used to assign processors to specific threads for improved performance. To know more about affinity mask, read this. Usually, the value for affinity mask is a positive integer (decimal format) in sp_configure. The article in previous link shows an example of binary bit mask and corresponding decimal value to be set in sp_configure.
I was curious to find out why the value of affinity mask could be negative as according to BOL https://technet.microsoft.com/en-us/library/ms187104(v=sql.105).aspx
The values for affinity mask are as follows:
· A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.
· A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.
· A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.
· A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.
· To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.
Time to unfold the mystery. Windows Server 2008 R2 supports more than 64 logical processors. From ERRORLOG, I see there are 40 logical processors on the server:
2014-03-31 18:18:18.18 Server Detected 40 CPUs. This is an informational message; no user action is required.
Further, going down in the ERRORLOG I see this server has four NUMA nodes configured.
Processor affinity turned on: node 0, processor mask 0x0000000000001c00.
Processor affinity turned on: node 1, processor mask 0x0000000000000007.
Processor affinity turned on: node 2, processor mask 0x0000000000700000.
Processor affinity turned on: node 3, processor mask 0x00000001c0000000.
:
Node configuration: node 0: CPU mask: 0x00000000000ffc00:0 Active CPU mask: 0x0000000000001c00:0.
Node configuration: node 1: CPU mask: 0x00000000000003ff:0 Active CPU mask: 0x0000000000000007:0.
Node configuration: node 2: CPU mask: 0x000000003ff00000:0 Active CPU mask: 0x0000000000700000:0.
Node configuration: node 3: CPU mask: 0x000000ffc0000000:0 Active CPU mask: 0x00000001c0000000:0.
These were hard NUMA nodes. No soft NUMA node configured on the server (no related registry keys exist)
An important thing to note is that the affinity mask value forsp_configure ranges from -2147483648 to 2147483647 = 2147483648 + 2147483647 + 1 = 4294967296 = 2^32 = the range of int data type. Hence affinity mask value from sp_configure is not sufficient to hold more than 64 CPUs. To deal with this, ALTER SERVER CONFIGURATION was introduced in SQL Server 2008 R2 to support and set the processor affinity for more than 64 CPUs. However, the value of affinity mask in sp_configure, in such cases, is still an *adjusted* value which we are going to find out below.
Let me paste the snippet from ERRORLOG again:
Processor affinity turned on: node 0, processor mask 0x0000000000001c00.
Processor affinity turned on: node 1, processor mask 0x0000000000000007.
Processor affinity turned on: node 2, processor mask 0x0000000000700000.
Processor affinity turned on: node 3, processor mask 0x00000001c0000000.
As it says, the underlined values above are for the processor mask i.e. processor affinity or affinity mask. These values correspond to that of online_scheduler_mask in sys.dm_os_nodes which makes up the ultimate value for affinity mask in sp_configure. Ideally, affinity mask should be a sum of these values. Let’s add these hexadecimal values using windows calculator (Choose Programmer from Viewmenu)
0x0000000000001c00
+ 0x0000000000000007
+ 0x0000000000700000
+ 0x00000001c0000000
--------------------
= 0x00000001C0701C07
= 7523539975 (decimal)
So, affinity mask in sp_configure should have been equal to 7523539975. Since this no. is greater than the limit of 2^32 i.e. 4294967296 we see an *adjusted* value (apparently a negative value). The reason I say it an *adjusted* value is because sum of processor mask values (in decimal) is adjusted (subtracted from the int range i.e. 4294967296 so that it fits within the range and falls below or equal to 4294967296 ). Here’s is an example which explains the theory:
7523539975 – 4294967296 – 4294967296 = –1066394617 = the negative value seen in sp_configure
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 -1066394617 -1066394617
That explains why affinity mask shows up as a negative number in sp_configure.
To make the calculation easier, I wrote a small script to find out the sp_configure equivalent value of affinity mask in case of NUMA nodes
— Find out the sp_configure equivalent value of affinity mask in case of NUMA nodes ————————————————————————————– BEGIN DECLARE @real_value bigint; — to hold the sum of online_scheduler_mask DECLARE @range_value bigint = 4294967296; — range of int dataype i.e. 2^32 DECLARE @config_value int = 0; — default value of affinity_mask in sp_configure output. to be set later. — Fetch the sum of Online Scheudler Mask excluding node id 64 i.e. Hidden scheduler SET @real_value =( SELECT SUM(online_scheduler_mask) as online_scheduler_mask FROM sys.dm_os_nodes WHERE memory_node_id <> 64 ); — Calculate the value for affinity_mask in sp_configure WHILE (@real_value > 2147483647) BEGIN SET @real_value=(@real_value – @range_value); END; — Copy the value for affinity_mask as seen in sp_configure SET @config_value = @real_value; PRINT ‘The current config_value for affinity_mask parameter in sp_configure is: ‘ + cast(@config_value as varchar); END;
This script will give the current config value for SQL server in any case, NUMA nodes, >64 procs, SQL Server 2008 R2..
Hope this post will help you if were as puzzled as I was seeing the negative no. in sp_configure.
Stay tuned!
