question

SeHor-8331 avatar image
0 Votes"
SeHor-8331 asked AmeliaGu-msft commented

MAXDOP setting

I have a SQL 2014 with which I have some performance issues, mostly read/disk related. the system is VMWare/SSD SAN. The environment is mainly used for DSS characterized by large queries, an average of 80% read and 20% write. The data volume is 3 TB with 128 MB memory (90% allocated to SQL). I need to calculate MAXDOP (there are 8 vCPUs on the machine).

I am looking for any guide/script (SQL/PS/etc) for this, or how to determine a good start for my environment. So far I have found 2 wondering if any further input from the community:

https://dba.stackexchange.com/questions/36522/maxdop-setting-algorithm-for-sql-server (where Kin Sha's script give me MAXDOP 8 and Hannah Vernon's give me 6)
https://dba.stackexchange.com/questions/232943/maxdop-settings-for-sql-server-2014 (also give me 8)

I realize the best is to trace a representative load and run it on a testing environment, but I do not have one even closely similar.

Thank you,

sql-server-general
· 1
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.

Hi SeHor-8331,

Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

8 vCPUs, but how many cores? 8 sounds like a small number for that environment.

How many concurrent users are there typically? If typically only query runs at a time, set it to 0, so that query can get all cores.

But if there can be multiple users, having a query that takes all cores is not that great. But if you only have eight cores, what can a poor boy do? If you set it to 6, there is some space for small queries, but all parallel will fight about the CPU.


· 4
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.

Thank you Erland, "SQL Server detected 1 sockets with 8 cores per socket and 8 logical processors per socket; using 8 logical processors based on SQL Server licencing...", so I take it 1 vCPU/core, if i am not mistaken. one only NUMA
There is mostly 1 user concurrently, but could go up to 3-4 concurrent, which happens rarely. Majority are Selects, DML, rarely DDL. read 80%, write 20% approx.

0 Votes 0 ·

There is mostly 1 user concurrently, but could go up to 3-4 concurrent, which happens rarely

In that case, I see no reason not to set MAXDOP to 0, so that you use as many cores there are available. (If you set it to 8, and add more cores, and forget to change the setting, you will not be using all cores.)

0 Votes 0 ·

Even with just a single user - I think setting the value is more dependent on the type of system and the type of queries that are executed. If the system is a reporting type of system (OLAP) then leaving it at 0 to allow for parallelism across all available CPUs would be the better option. If the system is more of an OLTP type system and queries generally retrieve a small set of data - then set it to 2 to start and increase if needed.

I would definitely recommend changing the cost threshold to a higher value than the default. Even in a reporting system you don't want all queries going parallel.

0 Votes 0 ·
Show more comments
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi SeHor-8331,

For Server with single NUMA node and less than or equal to 8 processors, it is recommended to keep MAXDOP at or below of processors.

So far I have found 2 wondering if any further input from the community.

You can set MAXDOP to 6 or 8, then monitor workload before and after, and change it based on the results.

Best Regards,
Amelia

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.