Is socket a synonym of NUMA, and virtual processor synonym of physical processor in a virtualized SQL server environment?



  • Link: https://docs.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#Recommendations

    I understand the basic concept of max dop and cost threshold.

    I am reviewing 3 SQL servers (virtualized, not physical machines) with the following combinations of socket and virtual processor:

    1. 1 socket 2 virtual processors

    2. 12 sockets 24 virtual processors

    3. 24 sockets 24 virtual processors

    The max dop is 0 and cost threshold is 5.

    I am reading the recommendation from the above link and they have used terms like logical processor, NUMA node. Where as when I open the task manager I can see number of sockets and virtual processors.

    Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default.

    I understand that Soft NUMA means that - it creates software based NUMA so that cores can be shared with multiple sockets.

    Questions:

    1. Does NUMA node and socket mean the same thing?

    2. In my examples, does the server configuration (see table from the above link) classify as single NUMA node or multiple NUMA nodes? If NUMA and socket is the same thing then I assume answer is multiple NUMA nodes?

    3. Does physical core also mean virtual processor?



  • I am not sure if your understanding is accurate with below statement.

    I understand that Soft NUMA means that - it creates software based NUMA so that cores can be shared with multiple sockets.

    The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA. There is a single I/O thread and a single lazy writer thread for each NUMA node. Depending on the usage of the database, these single threads may be a significant performance bottleneck. Configuring four soft-NUMA nodes provides four I/O threads and four lazy writer threads, which could increase performance. Read more details https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers/ba-p/316044 .

    Does NUMA node and socket mean the same thing?

    Short Answer: No

    From https://en.wikipedia.org/wiki/CPU_socket :

    In computer hardware, a CPU socket or CPU slot contains one or more mechanical components providing mechanical and electrical connections between a microprocessor and a printed circuit board (PCB). This allows for placing and replacing the central processing unit (CPU) without soldering.

    Non-uniform memory access (NUMA) is a computer memory design used in multiprocessing, where the memory access time depends on the memory location relative to the processor. Under NUMA, a processor can access its own local memory faster than non-local memory (memory local to another processor or memory shared between processors). The benefits of NUMA are limited to particular workloads, notably on servers where the data is often associated strongly with certain tasks or users.

    In my examples, does the server configuration (see table from the above link) classify as single NUMA node or multiple NUMA nodes? If NUMA and socket is the same thing then I assume answer is multiple NUMA nodes?

    I use this query to determine the count of NUMA and processor per NUMA.

    SELECT 
      parent_node_id AS NUMA_ID,
      COUNT(scheduler_id) AS NumberOfProcessor
    FROM sys.dm_os_schedulers
    WHERE status = 'VISIBLE ONLINE'
    GROUP BY parent_node_id
    

    You can also read: https://www.dcac.com/syndication/how-can-you-tell-how-many-numa-nodes-your-sql-server-has/ by Denny Cherry

    Does physical core also mean virtual processor?

    Short Answer: No

    Read: https://www.daniloaz.com/en/differences-between-physical-cpu-vs-logical-cpu-vs-core-vs-thread-vs-socket/ by Daniel




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2