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?

2 Answers 2

1

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
  • 1
    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
    Commented 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
    Commented 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. Commented May 6, 2022 at 14:45
-1

About NUMA...

In the past, when servers (physical machine) had few CPUs and the concept of a core did not yet exist, memory usage was uniform. Each CPU accessed the entire RAM

With the increase in the number of CPUs and cores, conflicts of access to memory pages have appeared more and more frequently (two separate cores trying to access the same page in memory).

enter image description here In the figure an access conflict occur because CPU1/Core4 has an access to page 20 and CPU2/Core2 want the same page...

Computer manufacturers then reacted by proposing non-uniform access to memory (Non Uniform Memory Access: NUMA), that is to say dedicating entire sections of memory to processors by affinity. In general, the alignment is done at the level of the notion of CPUs.

enter image description here Less conflicts occurs beaucause the memory has been split on 2 partitions with NUMA

Soft NUMA is a logical version of NUMA... Because processors have more and more cores. In the examples given in the images above and below, the machine has a single processor with 36 physical cores and therefore 72 logical cores. As soon as SQL Server detects more than 8 physical cores, it activates the soft NUMA process which allows it to act as a physical NUMA in order to divide the cache memory into different areas with an affinity of exclusivity of the cores to the memory partitions.

enter image description here Soft NUMA partitioning on a machine with 72 logical cores having only one processor (ntel(R) Xeon(R) w9-3475X - 36 cores)). 4 Partitions are created despite a single processor with 36 physical cores.

About cores

logical cores and virtual processors are synonym. The first term is about processor (or CPU / Socket, that are synonyms) the second is an internal point of view in Virtual Machine because wee do not know if the VM relies on heperthreading (so logical cores will be there) or only physical core.

About DOP

Degre Of Parallelism is the maximal number of core that the query enfine will use for a query. This parameter do not care with CPU, Socket, virtual ou physical core... By the way it is not good to have a high degree of parallelism...

This parameter limits the number of threads that a single query can use to execute. The default value 0 indicates that the engine chooses. In the 2022 version of SQL Server, you can possibly leave this default value because the significant improvements made in the optimizer as part of Intelligent Query Processing mean that the degree of parallelism will adapt little by little for each request. This is a contribution linked to the Query Store which is now activated as standard in any new database with 2022 compatibility and which learns through successive execution tests what the value of the number of threads is for a specific query (DOP feedback). .

In an article on a comparative benchmark between SQL Server and PostGreSQL on GIS, which I wrote and published on developpez.com, I had fun measuring the effects of parallelism in the execution of a query presenting most of these operations in parallel. By limiting the number of threads from 1 to 48, the response times were as follows:

enter image description here

Or we see that between 24 and 48 cores, no improvement in response time is visible.

Between 1 and 2 cores we go from 2.4 seconds to 1.4 (improvement factor 1.71)

Between 1 and 4 cores we go from 2.4 seconds to 0.8 (factor of 3 improvement)

Between 1 and 8 cores we go from 2.4 seconds to 0.45 (improvement factor 5.33)

Between 1 and 16 cores we go from 2.4 seconds to 0.3 (improvement factor 8)

But between 12 and 16 we go from 0.35 to 0.3 (improvement factor 1.16)

the curve is interesting because beyond 12 cores the gain is minimal and certain fortuitous regressions appear... But with 12 cores out of 48 that this machine had, how many parallelized requests can I make at the same time? Theory tells us that 48 /12 = 4... Except that you still need a few threads available for Windows (or Linux) and SQL Server...

Reasons why I recommend on servers with more than 16 logical cores to limit parallelism to a number of threads of a quarter of the total number of cores minus one. For example, if your SQL Server instance is installed on a server with 24 cores (including hyperthreading), then limit parallelism to 5 threads.

Small anecdote, Soft NUMA was developed by Microsoft in its labs to be able to carry out tests of SQL Server from version 2008 R2 (2010) in extreme conditions... but the engineers quickly realized the benefits in terms of performance provided by this solution which will be implemented from the 2016 version...

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.