SQL Server on VMware and EC2: Why Your Socket Configuration Is Quietly Killing Performance

You inherit a SQL Server virtual machine. It shows 8 logical CPUs, 1 socket, 4 cores per socket, 1 NUMA node. The VM admin tells you it is configured correctly. The database is slow. You tune indexes, update statistics, review wait stats. Performance does not improve enough. You add more vCPUs. Still slow.

The problem is not the query. The problem is not the index. The problem is that the VM was built the way the VM team always builds VMs, without understanding how SQL Server uses the hardware topology underneath it. The socket configuration, the NUMA topology, and how the hypervisor presents those to the guest operating system have a direct and measurable impact on SQL Server performance. And most DBAs never look at them.

This article explains what NUMA is, why socket configuration matters for SQL Server, what the community research and published guidance shows about socket configuration, how to collect the data you need to evaluate your own environment, and how to determine the right configuration for your specific workload on VMware and EC2.

1 What NUMA Is and Why SQL Server Cares Beginner

Non-Uniform Memory Access (NUMA) is a hardware architecture used in modern multi-socket servers. In a NUMA system, each physical CPU socket has its own bank of memory directly attached to it. A CPU can access its own local memory extremely fast. It can also access memory attached to a different socket, but that access is slower because it must travel across the inter-socket interconnect. The speed difference is significant: local memory access takes roughly 10 CPU cycles while remote memory access across a socket boundary takes 100 or more cycles.

SQL Server is NUMA-aware. When it starts, it detects the NUMA topology, creates a memory manager per NUMA node, and assigns worker threads to NUMA nodes to keep CPU and memory access as local as possible. Buffer pool pages are allocated on the NUMA node where the requesting thread runs. Parallelism decisions respect NUMA node boundaries. The entire SQL Server memory architecture is designed to exploit NUMA locality.

When the NUMA topology presented to the guest operating system does not reflect reality, SQL Server makes bad decisions. It allocates memory thinking the topology is one thing when the hypervisor is actually scheduling threads somewhere else. The result is remote memory access that the SQL Server optimizer never expected and cannot compensate for.

2 What the Socket Configuration Actually Controls Intermediate

In VMware vSphere, when you configure a virtual machine’s vCPUs you have two settings that work together: the total number of vCPUs and the number of cores per socket. These two numbers divide to give you the number of virtual sockets. They control how the guest operating system and any NUMA-aware applications inside the VM perceive the hardware topology.

The cores-per-socket setting tells the guest OS how many cores each physical socket has. This is what drives the virtual NUMA (vNUMA) topology that gets presented to the guest. In vSphere 6.5 and later, vNUMA presentation was decoupled from the cores-per-socket value, meaning the hypervisor will present the correct physical NUMA topology to large VMs automatically regardless of how you set cores-per-socket. But for SQL Server specifically, the cores-per-socket setting still affects how SQL Server itself counts sockets for licensing purposes, and for smaller VMs the vNUMA topology may not be presented at all without correct configuration.

3 The Problem with 1 Socket 8 Cores Beginner

The VM shown in this article’s opening example is a real and common configuration: 8 logical CPUs, 1 socket, 4 cores per socket, 1 NUMA node. This is how many VM teams build SQL Server VMs by default because they are following a general VMware recommendation to use fewer sockets. It is not wrong as a general VM recommendation. It is wrong for SQL Server specifically when the physical host is a dual-socket server.

Here is exactly what the problem looks like:

4 Published Guidance on Socket and NUMA Configuration Intermediate

Several SQL Server virtualization authorities have published specific guidance on this topic. Here is what each source actually says, with attribution to the right person or organization.

David Klee, Heraflux Technologies (SQLServerCentral, 2015 updated 2021)

In his Stairway to SQL Server Virtualization series on SQLServerCentral, David Klee directly compares two 8-vCPU configurations and states that a VM configured as 2 sockets x 4 cores is designed to equally scale across two physical CPU sockets and keeps memory access patterns confined to just the two sockets, enabling efficient memory lookups on each vNUMA node. The alternative configuration of 8 sockets x 1 core each is potentially scattered across all available physical CPU sockets and cores, and the memory management is not optimal and results in a performance loss.

His earlier 2013 article on SQLServerCentral covers vNUMA topology fundamentals: that NUMA places banks of memory in close proximity to each physical CPU socket, that virtual machines in vSphere 5.0 and later can expose this topology to the guest, and that the goal is to construct a virtual machine to take advantage of NUMA when performance truly counts. Both articles are linked in the references section and worth reading in full.

Straight Path Solutions (VMware and SQL Server Best Practices, March 2026)

Straight Path Solutions published updated VMware and SQL Server guidance in March 2026 with two specific points worth quoting directly. First: align your cores and sockets to the host’s physical NUMA topology. Second on memory: if you are using less than half of the RAM on a 2-socket host, CPU alignment is the only concern. If you are exceeding that, spread your cores across 2 sockets even if you do not strictly need them to align CPUs. Their recommendation: you probably need one or two sockets, not 16.

VMware vNUMA Rightsizing Guidelines (2017, updated)

VMware’s own performance blog explicitly used a SQL Server example: a virtual machine hosting SQL Server 2016 Enterprise Edition created with 8 Sockets x 2 Cores per Socket may behave differently than the same machine created with 2 Sockets x 8 Cores per Socket, even though both are 16 vCPUs. That difference is SQL Server’s soft-NUMA auto-configuration triggering differently based on the detected socket and core topology.

Community consensus across all sources

The guidance that appears consistently across all published sources, and that this article’s investigation workflow is built on, comes down to three verifiable points. First: the VM socket configuration should match the physical NUMA topology of the host. Second: always verify what SQL Server actually detects using DMV queries, not just what the VM console shows. Third: SQL Server Standard Edition’s 4-socket limit makes the socket count a licensing concern, not just a performance one.

5 VMware’s Guidance and Where It Falls Short for SQL Server Intermediate

VMware’s general recommendation for vCPU configuration has historically been to set cores per socket to 1, meaning each vCPU becomes its own virtual socket. The intent was that this would cause ESXi to present the correct physical NUMA topology to the guest VM automatically, because the number of virtual sockets would match the number of vCPUs and ESXi would group them into NUMA nodes matching physical sockets.

This recommendation is not wrong for general workloads. It becomes a significant problem for SQL Server specifically for two reasons.

Problem 1: SQL Server Standard Edition Socket Limits

SQL Server Standard Edition is limited to the lesser of 4 sockets or 24 cores. If you configure a VM with 8 vCPUs as 8 sockets of 1 core each, SQL Server Standard Edition will only use 4 of those 8 sockets. You have paid for 8 vCPUs but SQL Server is running on 4. Half your licensed cores are idle from SQL Server’s perspective. This is one of the most common and expensive misconfigurations in SQL Server virtualization.

Problem 2: vNUMA Activation Threshold

In vSphere, virtual NUMA is only automatically presented to VMs with more than 8 vCPUs by default (the activation threshold). A VM with 8 or fewer vCPUs may not receive vNUMA topology at all, meaning it appears as a single flat NUMA node to the guest regardless of the physical host’s actual NUMA structure. For a SQL Server VM with exactly 8 vCPUs on a dual-socket host, explicitly configuring 2 virtual sockets ensures NUMA is presented correctly even if the VM falls at or below the automatic activation threshold.

The VMware vNUMA rightsizing guidelines (2017, updated) acknowledge this directly with the example: a virtual machine hosting SQL Server 2016 Enterprise Edition created with 8 Sockets x 2 Cores per Socket may behave differently than the same machine created with 2 Sockets x 8 Cores per Socket, even though both are 16 vCPUs total. That difference is SQL Server’s soft-NUMA auto-configuration triggering differently based on the detected socket and core topology.

6 EC2 and AWS: A Different but Related Problem Intermediate

On Amazon EC2, you do not have the same level of socket configuration control that VMware provides. EC2 does not expose a vSphere-style cores-per-socket setting. The topology you see inside a Windows guest on EC2 is determined by the instance type and AWS’s underlying hardware mapping.

The specific EC2 challenge for SQL Server is that many EC2 instance types present as a single NUMA node even when the underlying hardware is multi-socket. A large EC2 instance might have 32 or 64 vCPUs but appear to SQL Server as a single NUMA node with no memory locality structure. SQL Server’s NUMA-aware memory allocation gets no information to work with.

What to Check on EC2

AWS does allow some control over the CPU options for an EC2 instance at launch time. The --cpu-options parameter lets you specify the number of CPU cores and threads per core (enabling or disabling hyperthreading). This affects the logical CPU count but does not directly map to socket configuration in the VMware sense.

For SQL Server on EC2, the practical guidance from AWS prescriptive guidance and independent community research (including Glenn Berry’s EC2 instance type analysis) is:

• Choose instance types where the vCPU count divided by the logical processor count fits a sensible NUMA boundary
• Verify the NUMA topology inside the guest using the DMV queries in Section 8 before tuning anything
• On EC2 instances where SQL Server sees 1 NUMA node with many vCPUs, soft-NUMA becomes the primary mechanism for SQL Server to subdivide internal structures
• Verify that soft-NUMA is firing correctly and review the SQL Server error log at startup to confirm the detected configuration
• AWS RDS for SQL Server and RDS Custom handle some of this at the managed service level but you still need to verify MAXDOP and soft-NUMA are configured correctly for your instance size

Soft-NUMA: SQL Server’s Internal Fallback

When SQL Server 2016 and later detects more than 8 physical cores per NUMA node or socket at startup, it automatically creates soft-NUMA nodes. Soft-NUMA subdivides a large single NUMA node into smaller logical nodes, ideally containing 8 cores each (minimum 5, maximum 9). This allows SQL Server to distribute internal structures and worker threads even when the hypervisor is presenting a flat single-node topology.

Soft-NUMA is valuable but it is a compensation mechanism, not a solution. It does not create actual memory locality. It subdivides scheduling to reduce contention. A properly configured vNUMA topology is always preferable to relying on soft-NUMA to compensate for a misconfigured VM.

7 SQL Server Standard Edition: The Licensing Trap Intermediate

SQL Server Standard Edition has hard limits on the hardware it can use. Understanding these limits relative to socket configuration is essential for anyone running Standard Edition on virtual infrastructure.

For Standard Edition the socket limit is the dangerous one. If your VM is configured with more than 4 virtual sockets, SQL Server Standard Edition silently ignores the additional sockets. The vCPUs on those ignored sockets are allocated, visible to the OS, and billed by your cloud provider or consuming resources in your VMware cluster, but SQL Server never uses them.

The correct Standard Edition configuration for a VM with up to 24 vCPUs is to use no more than 4 virtual sockets. If you have 8 vCPUs, use 2 sockets x 4 cores, not 8 sockets x 1 core. If you have 16 vCPUs, use 4 sockets x 4 cores. Both configurations keep SQL Server Standard Edition able to use all allocated vCPUs.

8 Step 1: Collect Your Current Configuration Data Beginner

Before making any recommendations or changes, collect the complete picture of what SQL Server currently sees. Run all of these queries and save the output. This is your baseline and your evidence.

-- ============================================================
-- STEP 1A: What SQL Server sees about the hardware
-- Run this on every SQL Server VM you are investigating
-- ============================================================

SELECT
    cpu_count                   AS LogicalCPUCount,
    hyperthread_ratio           AS LogicalCPUsPerCore,
    cpu_count / hyperthread_ratio AS PhysicalCoreCount,
    socket_count                AS SocketCount,
    cores_per_socket            AS CoresPerSocket,
    numa_node_count             AS NUMANodeCount,
    softnuma_configuration      AS SoftNUMAConfig,
    softnuma_configuration_desc AS SoftNUMAConfigDesc,
    sqlserver_start_time
FROM sys.dm_os_sys_info;

-- Expected output for the example VM from this article's screenshot:
-- LogicalCPUCount:    8
-- LogicalCPUsPerCore: 2  (hyperthreading enabled)
-- PhysicalCoreCount:  4
-- SocketCount:        1
-- CoresPerSocket:     4
-- NUMANodeCount:      1  -- PROBLEM: only 1 NUMA node

-- What you WANT to see for a dual-socket aligned config:
-- SocketCount:   2
-- NUMANodeCount: 2

-- ============================================================
-- STEP 1B: How SQL Server has distributed schedulers
-- across NUMA nodes
-- ============================================================

SELECT
    node_id,
    node_state_desc,
    memory_node_id,
    cpu_affinity_mask,
    online_scheduler_count,
    idle_scheduler_count,
    active_worker_count,
    avg_load_balance
FROM sys.dm_os_nodes
WHERE node_state_desc NOT LIKE '%DAC%'
ORDER BY node_id;

-- A well-configured dual-socket VM shows 2 rows
-- with schedulers balanced across both nodes
-- A misconfigured 1-socket VM shows 1 row
-- with all schedulers on node 0

-- ============================================================
-- STEP 1C: Scheduler details per NUMA node
-- ============================================================

SELECT
    scheduler_id,
    cpu_id,
    node_id,
    status,
    is_online,
    current_tasks_count,
    runnable_tasks_count,
    current_workers_count,
    active_workers_count,
    work_queue_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY node_id, scheduler_id;

-- Look for: uneven distribution of runnable_tasks_count
-- All load on node 0 with node 1 idle = misconfiguration
-- Balanced load across nodes = correct configuration

-- ============================================================
-- STEP 1D: Memory nodes and allocation per NUMA node
-- ============================================================

SELECT
    memory_node_id,
    virtual_address_space_reserved_kb / 1024   AS VirtualReservedMB,
    virtual_address_space_committed_kb / 1024  AS VirtualCommittedMB,
    locked_page_allocations_kb / 1024          AS LockedPagesMB,
    pages_kb / 1024                            AS PagesMB,
    foreign_committed_kb / 1024                AS ForeignCommittedMB
FROM sys.dm_os_memory_nodes
ORDER BY memory_node_id;

-- IMPORTANT: foreign_committed_kb
-- Non-zero foreign_committed_kb means SQL Server is allocating
-- memory on a NUMA node different from where the requesting
-- thread is running. This is remote memory access.
-- High foreign_committed_kb = NUMA misconfiguration confirmed

-- ============================================================
-- STEP 1E: Confirm soft-NUMA status from error log
-- ============================================================

-- Check SQL Server error log for NUMA detection at startup
-- Look for lines like:
-- "SQL Server detected 1 sockets with 4 cores per socket..."
-- "Automatic soft-NUMA was enabled because SQL Server has
--  detected hardware NUMA nodes with greater than 8 physical cores."

EXEC xp_readerrorlog 0, 1, N'NUMA', NULL, NULL, NULL, N'asc';

-- Also check:
EXEC xp_readerrorlog 0, 1, N'socket', NULL, NULL, NULL, N'asc';

9 Step 2: Understand Your Physical Host Topology Intermediate

You cannot evaluate whether your VM configuration is correct without knowing the physical host it runs on. This step requires information from the VM or infrastructure team. Ask them for:

• Physical socket count on the ESXi host
• Physical cores per socket
• Whether hyperthreading is enabled
• Total physical memory per socket (NUMA node memory size)
• The ESXi version (vNUMA behavior changed in vSphere 6.5)

If you have access to the ESXi host directly, the following vSphere commands give you the information you need:

-- From ESXi host (SSH or CLI):
-- Physical CPU and NUMA topology
vsish -e get /hardware/cpu/cpuList/0 | grep -i numa
esxcli hardware cpu global get

-- Physical NUMA node layout
vsish -e get /hardware/numa/nodeList/0
vsish -e get /hardware/numa/nodeList/1

-- VM's virtual topology as seen by ESXi
vim-cmd vmsvc/get.config VMID | grep -i numa
vim-cmd vmsvc/get.config VMID | grep -i socket

Once you have the physical host topology, compare it to what SQL Server sees using the output from Step 1. If the virtual socket count does not match the physical socket count, or if the vNUMA node count does not align with physical NUMA nodes, the configuration needs attention.

10 Step 3: Identify the Symptoms Intermediate

NUMA misconfiguration does not announce itself with a specific error. It shows up in performance metrics that look like other problems. These are the signs to look for.

-- ============================================================
-- SYMPTOM CHECK 1: Foreign memory (remote NUMA access)
-- High foreign_committed_kb is the clearest signal of
-- NUMA misconfiguration causing remote memory access
-- ============================================================

SELECT
    memory_node_id,
    pages_kb / 1024                     AS LocalMemoryMB,
    foreign_committed_kb / 1024         AS RemoteMemoryMB,
    CASE WHEN (pages_kb + foreign_committed_kb) > 0
         THEN CAST(foreign_committed_kb * 100.0 /
              (pages_kb + foreign_committed_kb) AS DECIMAL(5,2))
         ELSE 0 END                     AS RemoteMemoryPct
FROM sys.dm_os_memory_nodes
WHERE memory_node_id < 64
ORDER BY memory_node_id;

-- Threshold: any foreign_committed_kb > 5% of total is worth investigating
-- > 20% indicates significant NUMA misconfiguration impact

-- ============================================================
-- SYMPTOM CHECK 2: Scheduler imbalance
-- If all load is on one NUMA node's schedulers, either
-- the workload is imbalanced or NUMA topology is wrong
-- ============================================================

SELECT
    n.node_id,
    n.online_scheduler_count,
    n.active_worker_count,
    n.avg_load_balance,
    SUM(s.runnable_tasks_count)         AS TotalRunnableTasks,
    SUM(s.current_tasks_count)          AS TotalCurrentTasks
FROM sys.dm_os_nodes   n
JOIN sys.dm_os_schedulers s ON s.node_id = n.node_id
WHERE n.node_state_desc NOT LIKE '%DAC%'
AND   s.status = 'VISIBLE ONLINE'
GROUP BY n.node_id, n.online_scheduler_count,
         n.active_worker_count, n.avg_load_balance
ORDER BY n.node_id;

-- ============================================================
-- SYMPTOM CHECK 3: SOS_SCHEDULER_YIELD and CXPACKET waits
-- Elevated CPU-related waits on a properly resourced server
-- often indicate scheduling inefficiency from NUMA issues
-- ============================================================

SELECT TOP 10
    wait_type,
    wait_time_ms / 1000.0               AS WaitSeconds,
    waiting_tasks_count,
    ROUND(100.0 * wait_time_ms
        / NULLIF(SUM(wait_time_ms) OVER (), 0), 2) AS PctOfTotal
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'SOS_SCHEDULER_YIELD',
    'CXPACKET',
    'CXSYNC_PORT',
    'CXCONSUMER',
    'BPSORT',
    'PAGEIOLATCH_SH',
    'PAGEIOLATCH_EX',
    'RESOURCE_SEMAPHORE'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- SOS_SCHEDULER_YIELD: CPU pressure, threads yielding scheduler
-- CXPACKET / CXSYNC_PORT: parallelism issues, often NUMA-related
-- RESOURCE_SEMAPHORE: memory grant waits, can indicate NUMA pressure

11 Step 4: Determine the Right Configuration Intermediate

With the data collected from Steps 1 through 3, apply this decision process to determine the correct socket configuration for your VM.

-- Calculate whether your VM memory fits within one physical NUMA node
-- Requires knowing the physical memory per NUMA node from the VM team

DECLARE @VMMemoryGB         INT = 64;   -- your VM's assigned RAM in GB
DECLARE @PhysicalMemPerNodeGB INT = 384; -- physical memory per NUMA node on host

SELECT
    @VMMemoryGB                         AS VMMemoryGB,
    @PhysicalMemPerNodeGB               AS PhysMemPerNUMANodeGB,
    CASE WHEN @VMMemoryGB <= (@PhysicalMemPerNodeGB / 2)
         THEN 'Fits in one NUMA node - 1 socket may be fine'
         WHEN @VMMemoryGB <= @PhysicalMemPerNodeGB
         THEN 'Fits in one NUMA node - monitor foreign_committed_kb'
         ELSE 'Spans NUMA nodes - 2 sockets strongly recommended'
    END                                 AS MemoryNUMARecommendation;

12 Step 5: Validate After Changes Intermediate

After working with the VM team to change the socket configuration, restart SQL Server and validate that the new topology is being detected correctly. Do not assume the change worked without verifying inside SQL Server.

-- Run immediately after SQL Server restart following socket reconfiguration

-- 1. Confirm new topology detected
SELECT
    cpu_count, socket_count, cores_per_socket,
    numa_node_count, softnuma_configuration_desc
FROM sys.dm_os_sys_info;

-- 2. Confirm NUMA nodes are visible
SELECT node_id, node_state_desc, online_scheduler_count
FROM sys.dm_os_nodes
WHERE node_state_desc NOT LIKE '%DAC%';

-- 3. Confirm schedulers distributed across nodes
SELECT node_id, COUNT(*) AS SchedulerCount
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY node_id
ORDER BY node_id;

-- 4. Confirm error log reflects new configuration
EXEC xp_readerrorlog 0, 1, N'socket', NULL, NULL, NULL, N'asc';
-- Should now show: "SQL Server detected 2 sockets with 4 cores per socket..."

-- 5. Re-check foreign_committed_kb baseline after workload
-- Compare to pre-change baseline after the system has been running
-- for at least one representative workload cycle (24-48 hours)
SELECT
    memory_node_id,
    pages_kb / 1024           AS LocalMemoryMB,
    foreign_committed_kb / 1024 AS RemoteMemoryMB
FROM sys.dm_os_memory_nodes
WHERE memory_node_id < 64
ORDER BY memory_node_id;

13 Configuration Decision Guide Beginner

Quick reference for the most common scenarios.

References

• David Klee, Heraflux Technologies (@kleegeek) – Enterprise SQL Server Virtualization Consulting
• David Klee: SQL Server Virtual Machine vNUMA Sizing (SQLServerCentral, 2013)
• VMware Performance Blog: Virtual Machine vCPU and vNUMA Rightsizing Guidelines (2017)
• VMware Cloud Foundation Blog: Sizing SQL Server VMs on ESXi – 64 Cores Per NUMA Node Limit (August 2024)
• Straight Path Solutions: VMware and SQL Server Best Practices (March 2026)
• Microsoft Docs: Soft-NUMA in SQL Server
• Microsoft Docs: sys.dm_os_sys_info
• Microsoft Docs: sys.dm_os_nodes
• Microsoft Docs: sys.dm_os_memory_nodes
• AWS Database Blog: Best Practices for Configuring Performance Parameters for Amazon RDS for SQL Server (February 2024)
• AWS Prescriptive Guidance: Configuring SQL Server on Amazon EC2
• Glenn Berry: Picking a Good AWS EC2 Instance Type for SQL Server (August 2022)
• SQLYARD: SQL Server MAXDOP Tuning Guide
• SQLYARD: SQL Server Performance Tuning Complete Guide