Monday, February 28, 2022

How It Works: How SQL Server Determines Logical and Physical Processors

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.

 

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: 0

MSINFO32: 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