0

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

1 Answer 1

0

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 here.

Does NUMA node and socket mean the same thing?

Short Answer: No

From Wikipedia:

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: 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: Differences between physical CPU vs logical CPU vs Core vs Thread vs Socket by Daniel

3
  • Hi! Lazy writer threads are provisioned per SQLOS memory node (dm_os_memory_nodes where node_id<64) rather than per softNUMA node (dm_os_nodes where node_state_desc=‘ONLINE’). the number of txlog writers is based on softNUMA node count, though, up to version max. Before 2016 - 1 only. 2016 & 2017 - 1 per softnuma node up to 4 max. 2019 - 2 per softnuma node up to 8 max. All txlog writers in softnuma node 0.
    – sqL_handLe
    May 6, 2022 at 3:23
  • The above sql shows me 4 nodes, each with 6 processors. Where as the task manager shows me 2 NUMA nodes. How can I confirm which is correct?
    – variable
    May 6, 2022 at 14:36
  • You are comparing two different things. The query will give what SQL OS sees. Task Manager shows you what the operating system is seeing. May 6, 2022 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.