Moved from: bobsql.com
SQL Server, as outlined in the following post (https://blogs.msdn.microsoft.com/psssql/2016/03/30/sql-2016-it-just-runs-faster-automatic-soft-numa) adjusts to various processor configurations. I have received questions related to how SQL Server accomplishes the alignment on both bare metal and VM installations.
The key is the GetLogicalProcessorInformation API, 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.
- GetSystemInfo https://msdn.microsoft.com/en-us/library/windows/desktop/ms724381(v=vs.85).aspx
- GetLogicalProcessorInformation https://msdn.microsoft.com/en-us/library/windows/desktop/ms683194(v=vs.85).aspx
The calls to GetLogicalProcessorInformation retrieve the RelationProcessorCore and RelationProcessorPackage details. 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 using CPUID calls. This is a process of calling the CPUID instruction to obtain feature information reported by the CPU.
What Is Your System Showing?
Years ago I added the processor information output to the RML utilities. We can use the output to map the system capabilities and SQL Server behavior.
RML Install: https://www.microsoft.com/en-us/download/details.aspx?id=4511
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
MSINFO32 Reports The Following
Processor Intel(R) Core(TM) i7-4810MQ CPU @ 2.80GHz, 2801 Mhz, 4 Core(s), 8 Logical Processor(s)
The SQL Error Log
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 Server detected 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: 0MSINFO32: Processor Intel(R) Core(TM) i7-4810MQ CPU @ 2.80GHz, 2794 Mhz, 4 Core(s), 4 Logical Processor(s)
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 the virtual machine reserve percentage to 100% provides a hard affinity like capability to a Hyper-V based VM.
VMWare provides options to ‘Assign a Virtual Machine to a Specific Processor’ that may be helpful in tuning your installation. http://pubs.vmware.com/vsphere-4-esx-vcenter/index.jsp?topic=/com.vmware.vsphere.resourcemanagement.doc_41/managing_cpu_resources/t_assign_a_virtual_machine_to_a_specific_processor.html
CPU-Z
You can run CPU-Z to show you the package relationships.
Posted at https://sl.advdat.com/3IwCDbhhttps://sl.advdat.com/3IwCDbh