MAXDOP setting

SeHor 66 Reputation points

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: (where Kin Sha's script give me MAXDOP 8 and Hannah Vernon's give me 6) (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

    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

    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,

    0 comments No comments