NUMA node from SSMS is different from t-sql query on dmv

Question

Hi all,

I'm trying to understand the right global value for the MAX degree of parallelism. I have already read a good part of the documentation on MS regarding best practices or the various definitions of NUMA and soft-NUMA.

I'm on SQL Server 2019.

These are the settings directly from SQL log:

• SQL Server detected 4 sockets with 4 cores per socket and 4 logical processors per socket, 16 total logical processors; using 16 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

and naturally after... 'cause I'm on 2019

• Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.

OK now from here I'm little bit confused.

From SSMS seems a only NUMA node with 16 CPU (0-15)

but using t-sql, I see 2 nodes which refer to the same physical node(correct me if I'm wrong!)

I also tried using a script that reported values that didn't match (https://github.com/MadeiraData/MadeiraToolbox/blob/master/Best%20Practices%20Checklists/MaxDOP_Configuration_Check.sql)

This gives me values that do not correspond to my situation, namely:

@NumaNodeCount: 1 (NO I GOT 2 NUMA from SSMS or is DMV the correct one?)
@LogicalProcessorPerNumaNodeCount: 16 (this is right/wrong depending on whether you interpret it from SSMS or from t-sql)

The only fixed point is that the server created the soft-NUMA by seeing the logs... the MS recommendation is clean on soft-NUMA (https://learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16):

"NUMA node in the above table refers to soft-NUMA nodes automatically created by SQL Server 2016 (13.x) and higher versions, or hardware-based NUMA nodes if soft-NUMA has been disabled."

In conclusion I would like to understand whether to consider 1 NUMA node or 2? In any case, whether it is 1 or 2, the maximum conceivable degree is always 8.

Thank, ALEN

Answer 1

So, I play an SQL expert on TV, but I'm not even understanding half of this. If I were to set MAXDOP on a server, I would ask myself, how many cores am I prepared to give a query. If it is a data warehouse with a few analysts running mean queries, the answer would be "all". If it is a busy OLTP system with lots of concurrent users, my answer might in the one-digit range, even if the number of cores has three digits.

If you want the Microsoft recommendation for your system, insert the SQL Server installation media, and run the wizard to install a new instance. I seem to recall that there is a screen where you can select MAXDOP. Just move to the screen and see what default/recommendation you have. (You would obviously not start the actual installation.)

Answer 2

Do you have any performance issues?

If not, keep hands away from the SQL Server default setting, they are already following best practice; of course.

Answer 3

I've done a bit of research that I want to share...
This PDF is very interesting and explains the whole NUMA process very well from a VMVARE perspective: https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

On page 39 I found the answers I was looking for. There are soft-NUMA nodes (0 and 1) created by SQL Server and which were encountered by a HW with 4 sockets in my case. Since there are 16 logical cores, sql server created this new subdivision to better manage memory access. So in my end the answer was:

• 0 and 1 are soft-NUMA;
• these two must be evaluated to set the MAX degrees of parallelism (8)

Of course this is an indicative value, some OLAP queries will go faster at 16 but the value 8 is fine as a general limit. So we need to think about the soft-NUMA created by sql server and the number of vCPUs/SOS Schedulers present in these.

So...now that I am sure of how many soft-NUMAs there are, the tables shown in the document are clear:
https://learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16, the table the values refer exclusively to soft-NUMA and nothing else for SQL 2019!

ALEN