3

I have SQL 2016 Standard Edition instance with 40 logical processors in 2 nodes. (2x20=40)

Per Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server I want to set MaxDOP to 8

Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

BUT per max degree of parallelism Option I can only set it 4, but it looks like that applies to 2008 and I am at 2016

The maximum value for the degree of parallelism setting is controlled by the edition of SQL Server,

In reality, I changed it to 8, SQL did not complain and the value stayed at 8. I set it back to 0 while continuing to research.

My concern is that if I set to 8, I might really be setting it to 4. Before I make any changes I want to know that 8 = 8. I have searched and not found anything else saying that MaxDOP limits in newer versions of SQL is edition based.

Is the MaxDOP limit in SQL 2016+ Edition based?

EDIT Soft NUMA is currently setting to 4 nodes at startup. still learning but If I understand correctly I think this means a single node with 10 logic processors is the most that could be applied to any one statement (query) so setting the MaxDOP to 8 in order to preserver two processors is probably not worth the effort, without some existing problem to solve.

EDIT2 Nodes do not limit parallelism

Place threads on multiple nodes; This is the most common decision made by SQL Server. The decision spreads the workers across multiple nodes as required. The design is similar to full except the starting position is based on the saved, next node, global enumerator. source

  • It is two physical cores, each with 20 logical processors – James Jenkins Feb 23 '18 at 14:55
3

SQL Server 2016 Enterprise Edition allows you to set maxdop to any value up to 64. However, SQL Server 2016 Standard Edition has limits around parallelism, per this docs page:

  • Partitioned table parallelism is not supported.
  • Standard Edition is not NUMA Aware and does not support Large Page Memory and Buffer Array Allocations
  • The degrees of process parallelism (DOP) for an index build is limited to 2 DOP for the Standard Edition
  • Limited to lesser of 4 sockets or 24 cores
  • I am not sure what you or the reference mean by "not NUMA Aware". Checking the logs at start up I find 'Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.' and 4 nodes are configured. – James Jenkins Feb 23 '18 at 15:02
  • That is taken from docs.microsoft.com/en-us/sql/sql-server/… - presumably this means Standard Edition does not attempt to keep query processing and memory within a single NUMA node the way Enterprise Edition does. – Max Vernon Feb 23 '18 at 15:27
  • You might have a false assumption that Enterprise Edition limits to a single NUMA node, see my EDIT2 from blogs.msdn.microsoft.com/psssql/2016/03/04/… – James Jenkins Feb 23 '18 at 15:32
  • 1
    I'd assume, from the content of Bob Dorr's article you shared above that Standard Edition is not "numa aware" in that it uses the "full" behavior. – Max Vernon Feb 23 '18 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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