This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
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.
...