Understanding of Numa Node in SQL Server


Modern processors have multiple cores per socket. Each socket is represented, usually, as a single NUMA node. The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. Prior to SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server Database Engine service starts.

Automatic Soft-NUMA

With SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. Hyper-threaded processor cores are not differentiated when counting physical cores in a node. When the detected number of physical cores is more than eight per socket, the SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores, but can go down to five or up to nine logical cores per node. The size of the hardware node can be limited by a CPU affinity mask. The number of NUMA nodes never exceeds the maximum number of supported NUMA nodes.

You can disable or re-enable soft-NUMA using the ALTER SERVER CONFIGURATION (Transact-SQL)statement with the SET SOFTNUMA argument. Changing the value of this setting requires a restart of the database engine to take effect.

The figure below shows the type of information regarding soft-NUMA that you see in the SQL Server error log, when SQL Server detects hardware NUMA nodes with greater than eight physical cores per each node or socket.

2016-11-14 13:39:43.17 Server      SQL Server detected 2 sockets with 12 cores per socket and 24 logical processors per socket, 48 total logical processors; using 48 logical processors based on SQL Server licensing. This is an informational message; no user action is required.     
2016-11-14 13:39:43.35 Server      Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.     
2016-11-14 13:39:43.63 Server      Node configuration: node 0: CPU mask: 0x0000000000555555:0 Active CPU mask: 0x0000000000555555:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.    
2016-11-14 13:39:43.63 Server      Node configuration: node 1: CPU mask: 0x0000000000aaaaaa:0 Active CPU mask: 0x0000000000aaaaaa:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.    
2016-11-14 13:39:43.63 Server      Node configuration: node 2: CPU mask: 0x0000555555000000:0 Active CPU mask: 0x0000555555000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.     
2016-11-14 13:39:43.63 Server      Node configuration: node 3: CPU mask: 0x0000aaaaaa000000:0 Active CPU mask: 0x0000aaaaaa000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.   

Manual Soft-NUMA

To manually configure SQL Server to use soft-NUMA, disable automatic soft-NUMA, and edit the registry to add a node configuration affinity mask. When using this method, the soft-NUMA mask can be stated as a binary, DWORD (hexadecimal or decimal), or QWORD (hexadecimal or decimal) registry entry. To configure more than the first 32 CPUs use QWORD or BINARY registry values (QWORD values cannot be used prior to SQL Server 2012 (11.x)). After modifying the registry, you must restart the Database Engine for the soft-NUMA configuration to take effect.

Tip

CPUs are numbered starting with 0.

Warning

Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend that you back up any valued data on the computer.

Consider the example of a computer with eight CPUs, that does not have hardware NUMA. Three soft-NUMA nodes are configured.
Database Engine instance A is configured to use CPUs 0 through 3. A second instance of the Database Engine is installed and configured to use CPUs 4 through 7. The example can be visually represented as:

CPUs 0 1 2 3 4 5 6 7

Soft-NUMA <-N0--><-N1-><----N2---->

SQL Server <instance A ><instance B>

Instance A, which experiences significant I/O, now has two I/O 

Upgrade or Patch SQL Server Failover Cluster Instance Running with Availability Group

BY selventhiran· PUBLISHED JUNE 20, 2018 

I have explained step by step process to upgrade or patch SQL Server instance that hosts an Always on Availability Group (AOAG) to latest version in attached article. Here, I will explain how to upgrade or patch SQL Server failover cluster instance running with Always on Availability Group. We should plan and execute such upgrades carefully to avoid any risk during execution. The process to upgrade or patch SQL Server cluster instances running with AOAG is quite similar.

NOTE: Always test any solution in Lower life cycle environment before deploying it to Production.

Points to Remember

1. I have described steps to upgrade or patch SQL Server failover cluster instance having always on availability group. Do not use these steps while upgrading or patching Windows clustering or standalone SQL Server instances running with Always on Availability groups. If you have AOAG configured on standalone SQL Server instances then read attached article.
2. During the upgrade process, a secondary replica will not be available for failover or for read-only operations, and after the upgrade, it may take some time for the secondary replica to catch up with the primary replica node depending upon the volume of activity on the primary replica node (so expect high network traffic).
3. A higher version of a SQL Server instance cannot be added as a new replica to an existing AOAG. For example, a SQL Server 2016 replica cannot be added to an existing SQL Server 2014 AG.
4. Always first upgrade secondary replica then primary replica because an upgraded primary replica can no longer ship logs to any secondary replica. This may cause data loss as data movement to a secondary replica is suspended.

Planning 

We should be very careful while planning upgrade or Patching for your SQL Server Failover cluster instances and if your SQL Server cluster instances are running with availability groups then you need to be more careful considering its complex configuration. Here, i have given multiple steps that you need to plan during patching any AOAG cluster instance.

1. Make sure you can directly upgrade to the latest version of SQL Server 2017. If you are running with SQL Server 2005, you cannot go directly to SQL Server 2017. You must upgrade SQL Server 2005 to SQL Server 2012, 14 or 2016 before moving to latest version SQL Server 2017.
2. Identify your databases if they are involved in Change Data Capture or Replication then we need to perform some additional activities. These additional steps are given later in this article.
3. Run DBCC CHECKDB to make sure there is no corruption on databases hosted on these cluster instances.
4. You must run Full database backups for all your databases to secure a copy in case anything goes wrong and we need it to restore databases.
5. Make sure Always on Availability Group is working fine and in Healthy state. Fix the issues and make your AOAG configuration in healthy state before going with the upgrade.
6. Disable the backup job from the system that is going to upgrade (current secondary replica) and make sure to run it from the server that is online and acting as primary replica at the time upgrade will process. You can also change the Backup Preferences of AOAG to run backups always from primary replica during this upgrade.
7. No replicas are readable or available for backups during a version upgrade whereas during a non-version upgrade you can configure automated backups to run on secondary replicas prior to upgrading the primary replica.
8. Choose Upgrade approach. As we know, there are three approaches to perform any SQL Server upgrade. In-place upgrade, Side by side upgrade and Rolling upgrade. We will be using rolling upgrade process to apply patch or upgrade SQL Server cluster instances. This approach is used if your SQL Server Instances are required to upgrade in particular sequence. We use this approach if SQL Server is configured with Always on availability group, Database Mirroring, Log Shipping, Replication, Failover Cluster Instances, SQL Server Reporting Scale-out environment.
9. Make sure that synchronization state of the failover target is SYNCHRONIZED during any failover of Availability Group.
10. Develop a rollback plan. Executing this plan will enable you to restore your original environment if you need to rollback.
11. Plan to test the upgrade on Lower life cycle first before deploying on productions.

Upgrade or Patch SQL Server Failover Cluster Instance running with Availability Group

Here, I will explain how to upgrade or patch SQL Server failover cluster Instance running with Always on Availability group. Let me describe the cluster setup first for this upgrade.

• We have four database servers that are hosted in two data centers. Two nodes in each data centers.
• All four nodes are part of same windows cluster group. two set of storage have been shared between two nodes to install SQL Server failover cluster.
• Two SQL Server Failover Cluster Instances have been installed on these nodes of each data centers. One SQL Server failover cluster instance named FCIA is installed in data center 1 and another SQL Server cluster instance named FCIBis hosted in data center 2.
• We have configured Availability Group between these two SQL Server failover cluster instances.
• Each SQL Server cluster instance has two nodes. One active and one passive node in their respective data centers. Although active node in data center 2 is not active for connections but this will work as primary in case AOAG failover.
• Databases are hosted on shared storage that is connected to their respective nodes in their data centers. You can see this configuration depicted in below image.

Now we have to upgrade or patch this AOAG cluster instance configuration. Keep reading this article to understand step by step process to patch such complex AOAG configuration.

We can see above SQL Server cluster instances have Always on Availability Group so we will follow rolling upgrade process to perform upgrade or patch on these SQL Server cluster instances. The sequence to upgrade SQL Server cluster instances having AOAG configuration is to go from passive to active. We will upgrade the inactive nodes before upgrading the active nodes. Have a look at below step by step process.

1. First upgrade or patch passive node of SQL Server cluster instance FCIB i.e. Node 4.
2. Once you will update node 4 of above image configuration then next step is to failover second SQL Server cluster instance FCIB to upgraded instance Node 4 that is hosted in data center 2.
3. Once you performed failover for cluster instance FCIB to node 4 then next step is to patch node 3 that will be in passive state of this inactive SQL Server cluster instance. You can failback SQL Server cluster instance FCIB to node 3 again post patching. This failback is an optional step and not mandatory. Now you have applied latest patch level or upgraded your SQL Server cluster instance FCIB hosted in two cluster nodes in data center 2. Next, we will follow same process to apply patches in data center 1.
4. Apply patch on SQL Server cluster node 2 or upgrade SQL Server cluster node installed on node 2 machine as it’s passive node of SQL Server cluster instance FCIA.
5. Once you have upgraded or patched node 2 in data center 1 next we will failover SQL Server cluster instance FCIA to node 2. Now node 2 will run active instance of SQL Server cluster setup. But If you have enabled change data capture feature or replication configured on your SQL Server cluster instance then you should run few additional steps as described here. Once you upgraded or patched all secondary replicas then fail over the Availability Group to an upgraded instance. Now run below command on your primary replica.