The key is theGetLogicalProcessorInformationAPI, and the same logic occurs on bare metal and VM based SQL Server installations. For Virtual Machine (VM) installations you may need to consult the VM documentation for support capabilities.
SQL Server uses Windows APIs to determine the processors and package interactions.
The calls toGetLogicalProcessorInformationretrieve theRelationProcessorCoreandRelationProcessorPackagedetails. Using the returned information SQL Server assigns nodes and schedulers accordingly.
For example. SQL Server 2016, automatic soft NUMA avoids assignment of logical processors from the same package to the same soft NUMA node. X86 Only:If SQL Server is unable to obtain the processor information from the API call the relationships are determined usingCPUIDcalls. This is a process of calling the CPUID instruction to obtain feature information reported by the CPU.
Running readtrace or ostress –? outputs the processor relationship using the GetLogicalProcessorInformation API
This is the output from my 8 processor system. This is a single CPU, 4 CORE system, HT enabled.
Active proc mask(0): 0x000000FF Architecture: 9 Page size: 4096 CPUs: 8 Processor groups: 1 Highest node: 0 Proximity: 00 Node: 00 ————————————— Group: 0 ————————————— Processor(s): 0x00000003 Function units: Shared <——- You can see 2 processors per logical unit when converted to binary value Processor(s): 0x0000000C Function units: Shared <——- Processors show the RelationProcessorCore Processor(s): 0x00000030 Function units: Shared Processor(s): 0x000000C0 Function units: Shared Package mask: 0x000000FF <———–Package shows the RelationProcessorPackage Processor(s): 0x000000FF assigned to Numa node: 0
The details of each processor unit are highlighted during SQL Server startup and logged to the error log from which you can determine the package relationships.
SQL Serverdetected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
Hyper-V VM On This Same System Assigned 4 Processors
The processors are exposed in this configuration as a single CPU, 4 core, single CPU per core system.
Active proc mask(0): 0x0000000F Architecture: 9 Page size: 4096 CPUs: 4 Processor groups: 1 Highest node: 0 Proximity: 00 Node: 00 ————————————— Group: 0 ————————————— Processor(s): 0x00000001 Function units: Separated Processor(s): 0x00000002 Function units: Separated Processor(s): 0x00000004 Function units: Separated Processor(s): 0x00000008 Function units: Separated Package mask: 0x0000000F Processor(s): 0x0000000F assigned to Numa node: 0
The API is telling SQL Server the system is a 4 CPU system in a single CPU package. By reporting the processors as separate units, SQL Server treats them as separate when determining the scheduler and node assignments.
You know the bare metal is a single CPU, 4 core, HT enabled so what do the 4 CPUs really map to? In the default case of Hyper-V the processor is a logical (floating) entity. While there are optimizations the processor exposed to the VM may be moved around as needed to provide CPU cycles to the VM.
‘Hard Affinity’ may or not be supported by your VM solution along with additional options such as NUMA spanning. Review of the SQL Server scheduler layout (error log or DMVs), the VM and bare metal configuration may be required to optimize the desired layout and use. For example, setting thevirtual machine reserve percentageto 100% provides a hard affinity like capability to a Hyper-V based VM.