Sql-server – sql server NUMA binding to ports

sql-server-2008-r2

I am trying to allocate a set of CPUs on a 2 node NUMA server as below :

Below is from coreinfo that shows physical processors, logical processors and NUMA nodes on the server.

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

I am running 2 instances of sql server 2008R2 Standard Edition.

What I have done is :

Bind NODE 0 to Instance 1. So instance1 will have 8 logical processors.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0
GO

2013-11-07 13:01:20.79 Server      Processor affinity turned on: node 0, processor mask 0x00000000000000ff. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:01:20.79 Server      Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:01:20.82 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-11-07 13:01:20.82 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-11-07 13:01:20.83 Server      Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-11-07 13:01:20.83 Server      Node configuration: node 1: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

I can see instance 1 able to listen on NODE 0 as below :

2013-11-07 13:01:21.05 Server SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x0000000000000001. This is an informational message only. No user action is required.

NOW ON INSTANCE2 :

Bind NODE 1 to instance 2. So instance2 will have 8 logical processors.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 1
GO

The error log confirms this as well :

2013-11-07 13:03:51.16 Server      Detected 16 CPUs. This is an informational message; no user action is required.
2013-11-07 13:03:51.49 Server      Processor affinity turned on: node 0, processor mask 0x000000000000ff00. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:03:51.49 Server      Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:03:51.52 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-11-07 13:03:51.52 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-11-07 13:03:51.53 Server      Node configuration: node 0: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x000000000000ff00:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-11-07 13:03:51.53 Server      Node configuration: node 1: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

But why is INSTANCE2 listening on NUMA 0 and NOT NUMA 1 ?

Both instances will have MAXDOP set to 4.

2013-11-07 13:03:51.78 Server SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x0000000000000001. This is an informational message only. No user action is required.

Also, below are my TCP/IP SETTINGS :

enter image description here

enter image description here

Note:

I have tried below things as well:

  1. turn off CPU affinity first and then bind the ports to see if it works. This works Fine, and I am able to see NUMA 0 listening on one port and NUMA 1 listening on second port. This is what I want, but I dont want to turn off CPU Affinity. Am I in the right direction ?

  2. CPU Affinity ON. Try NUMA assigning to ports. This does not work in the sense that Instance1 is bind to NUMA 0 and Instance 2 does not show in error log that it is listening on NUMA 1.

Thanks !

Best Answer

Since you’ve affinitized each instance to a NUMA node with ALTER SERVER CONFIGURATION, that is all you need to do, and port mapping is not required for this setup. The reason it is not required is that the instances can only assign tasks to the ONLINE node, and because each only has one ONLINE node, that is where all tasks have to be assigned as a part of the CPU affinitization of the instance. Within Soft NUMA you could further subdivide and isolate within the individual instances but I see no reason to do so at all.