This article is contributed. See the original author and article here.

Moved from: bobsql.com


Download attachment to read the full content.


SQL Server uses 3 types of affinity to control where the SQL Server worker threads execute.  Before explaining the different scheduler affinity types let me clarify some terminology.


 


Node Types


SQL Server makes a specific distinction between scheduling and memory nodes.


 


Scheduling nodes:        sys.dm_os_nodes


Memory nodes:            sys.dm_os_memory_nodes


 


A scheduling node is a used to group a set of SQLOS schedulers.  The scheduling node must :



  • Remain within a single memory node.

  • Can be configured to use a subset of the CPUs presented by the OS from the same memory node.


For example: A memory node with 64 CPUs is a complete, Operating System, scheduler group.  SQL Server may choose to divide the memory node allowing for better partitioning and performance.  The Soft Numa feature may take the 64 CPUs and create 8 scheduler nodes, each managing 8 CPUs or 4 scheduler nodes managing 16 CPUs, etc.  The decision is performance driven.  


 


A memory node represents the memory associated with a group of CPUs from the physical hardware.   SQL Server aligns schedulers and other partitioned structures with the memory node to reduce access to remote, NUMA node memory when possible.  A memory node may have 1 or more scheduling nodes, but a scheduling node can only be assigned to a single memory node.


BobDorr_0-1644081647907.png



 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.