Why is SQL Server setup recommending MAXDOP 8 here?

Question

I'm running SQL Server 2022 RC1 setup on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether.

Setup is recommending MAXDOP 8:

But if you click on that link for configuring MAXDOP, the recommendations say:

Based on that KB article, MAXDOP should be 16, not 8. Sure, technically 8 is less than 16 - but so is 2, or 4, or 15. Where's the 8 coming from?

After SQL Server installation finishes and the service starts up, the log shows that SQL Server is automatically implementing Soft-NUMA with 4 nodes, each with 16 logical processors:

So again, that indicates that MAXDOP should be 16.

Is this a bug, or did I miss something obvious? Is there another unwritten rule somewhere that setup will stop at MAXDOP 8?

Answer 1

Setup is recommending MAXDOP 8

Setup calculates the MAXDop as follows:

Step 1: Calculate Hardware NUMA and Soft NUMA
Step 2: Decide whether Hardware or Soft NUMA will be used
Step 3: Divide the total logical processors by the NUMA used
Step 4: If > 15 LPs/NUMA, MAXDop = (LPs/NUMA)/2, otherwise MAXDop = LPs/NUMA

In your specific case:

64 LPs total
HW NUMA = 2
Soft NUMA = 4

Soft NUMA will be used, 64 LPs/4 (Soft) = 16. 16 > 15, take 16 / 2 = 8.

Whether people agree with the extra /2 or not, given >15 LPs/NUMA is a discussion point. Regardless, that's what currently exists and does fit within the MSDN TechNet BOL Docs Learn article recommendations.

Answer 2

I understand your desire to understand why SQL Server setup is recommending 8 for MAXDOP. Unfortunately, under default conditions (auto soft-NUMA enabled), the documentation will recommend an acceptable range for MAXDOP for nearly all servers instead of an exact single value.

The documentation says the following:

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.

Your server has two sockets with hyperthreading enabled. There are 16 physical cores per socket and 32 logical cores per socket. Auto soft-NUMA is enabled as well. Here is an estimated chart as to how auto soft-NUMA handles that situation, with column A as the number of schedulers per socket:

For your server you will end up with 4 soft-NUMA nodes of 16 logical processors each. That means that the guidance of line 3 applies to your situation:

Keep MAXDOP at or below # of logical processors per NUMA node

A MAXDOP value of 8 is less than your value of 16 logical processors per soft-NUMA node so there is no conflict with the documentation.

The documentation does not appear to be designed to give exact guidance for most scenarios when auto soft-NUMA is enabled. Only lines 2 and 4 give precise guidance instead of an acceptable MAXDOP range. For line 2, the only way to get that outcome with auto soft-NUMA is a single socket server with hyperthreading enabled that has between 10 and 16 logical cores. For line 4, it is not possible to get that outcome with auto soft-NUMA enabled.

Going back to how SQL Server setup works and why it picks 8, it may not be documented anywhere. I'm no longer in a position where I can test with big servers so I can't look for server configurations that lead to a default value that's greater than 8. With that said, Microsoft has over the years recommended not exceeding 8 in various places. For example:

In Azure SQL Database, the default MAXDOP setting for each new single database and elastic pool database is 8. This default prevents unnecessary resource utilization, while still allowing the database engine to execute queries faster using parallel threads. It is not typically necessary to further configure MAXDOP in Azure SQL Database workloads, though it may provide benefits as an advanced performance tuning exercise.

In September 2020, based on years of telemetry in the Azure SQL Database service MAXDOP 8 was made the default for new databases, as the optimal value for the widest variety of customer workloads. This default helped prevent performance problems due to excessive parallelism.

Those quotes are for Azure SQL Database so they aren't directly applicable to your situation but I feel that it illustrates the general mindset held by Microsoft that going above MAXDOP 8 is an "an advanced performance tuning exercise".

Personally, MAXDOP 8 for your server's hardware configuration feels like a reasonable starting point. I would not start with MAXDOP 16 unless there was some driving workload factor. Consider the generally believed best performance outcome for parallel worker distribution: all of the workers should be on different physical cores on the same hard NUMA node. Without TF 2467 or hypervisor tricks, here are odds for how your worker threads will be distributed:

MAXDOP 16 only guarantees the best outcome 9% of the time.

Personally, I don't believe that Microsoft's documentation in this area is very good. There are a number of ambiguous, misleading, or just plain incorrect statements contained in it. Detailed thoughts on that are here.