Share via

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

  • Article

 

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.  https://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

For fun I ran CPU-Z on the platforms. Look at the bottom, right Cores and Threads values showing you the package relationships.

Bare Metal VM
image image

 

Bob Dorr - Principal Software Engineer SQL Server

Comments

  • Anonymous
    June 10, 2016
    It would be really nice if sys.dm_os_sys_info had another column or two with better information about the processor(s). Right now, we get the total logical cpu count (from cpu_count) and we get the hyperthread_ratio. We can't actually tell whether HT is enabled or not from this DMV.With SQL Server 2012 or newer, you can read the SQL Server error log to get the processor core count information that SQL Server is using, but this won't work if the error log has been recycled.We can also get the processor description from the Windows Registry, but that is kind of hacky.
  • Anonymous
    June 10, 2016
    It would also be really nice if we could retrieve the Windows Power Plan setting and the current processor clock speed from one or more new or existing DMVs. For a VM, being able to get the hypervisor power policy would be very useful.