MAXDOP setting

Question

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,

Answer 1

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.

Answer 2

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