question

Vishu-2318 avatar image
0 Votes"
Vishu-2318 asked JeffreyWilliams-3310 commented

Node Configuration

Experts,

Is there any best practice recommendation for mdop settings. Should it be equal to the number of numa node.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Vishu-2318,
The MAXDOP option is related to number of processors and NUMA nodes.
Starting with SQL Server 2016 (13.x), use the following guidelines when you configure the max degree of parallelism server configuration value:
104705-image.png

From SQL Server 2008 through SQL Server 2014 (12.x), use the following guidelines when you configure the max degree of parallelism server configuration value:
104692-image.png


Best Regards
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (84.1 KiB)
image.png (74.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeonLaude avatar image
0 Votes"
LeonLaude answered

Hi @Vishu-2318,

Did you have a look at the official Microsoft documentation about this?

Configure the max degree of parallelism Server Configuration Option
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)


Best regards,
Leon

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

I HIGHLY recommend against changing the default settings, unless you are having a problem. For the most part the default configuration, except for "Max server memory" works fine for 99% of workloads.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JeffreyWilliams-3310 commented

In addition to the other posts, I like to point out that you need to consider the type of workload for the server. For an strict OLTP workload with a lot of concurrent users, you want a lower value for MAXDOP than for a data warehouse where a few data analysts run some occasional, but wild, queries.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition - you do want to change the degree of parallelism which is a much more important setting (in my opinion) than MAXDOP. The default value for that setting is way too low for any system regardless of OLTP or OLAP workflows.

0 Votes 0 ·

Jeffery, reading between the lines, it seems that you have in mind, is "cost threshold for parallelism". Am I right?

0 Votes 0 ·

Yeah - that is what I meant to write and thought I wrote...:)

0 Votes 0 ·