MAXDOP setting

SeHor 66 Reputation points
2021-07-01T19:26:23.73+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,693 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-07-01T21:50:03.43+00:00

    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.


  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-07-02T03:23:04.963+00:00

    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

    0 comments No comments