MAXDOP Calculator

Every time I conduct a SQL Server health check, it’s pretty common for me to find wrong configurations at instance level. But one of the most common mistakes I see on the wild, is SQL instances having the “max degree of parallelism” (MAXDOP) set to “1” (single core) , probably this behavior is related to a misconception of how parallelism works or because some DBAs thinks this parameter reduces the “CXPACKET” wait types, who knows …

In order configure MAXDOP properly, DBA’s should read and understand what Microsoft’s has as best practice guideline in the KB# 2806535. This article has a table with the explanation on how to calculate the right value for this configuration:

If you are not familiar with NUMA nodes, logical CPUs or CPUs at all this can be a little bit confusing …

Calculating MAXDOP

NUMA nodes, CPU specs, logical CPU’s, wait …. WHAT???

I understand, we live in a very busy world where we need to do things quick and make everyone’s work easier. Just imagine having to make this calculation every time you want to change the MAXDOP configuration, you truly need something to make your life easier… enter MAXDOP calculator.

The concept of a MAXDOP calculator was created by a Microsoft field engineer long time ago, the article helps you to set the right value for the MAXDOP configuration, the problem I noticed with this tool is that requires a lot of manual intervention. Anytime you would need some help calculating the correct MAXDOP value you will have to visit the website and provide the input from a couple of queries, there is also an Excel version but I do see the same problem. Don’t get me wrong I think this is a wonderful idea but I do personally don’t like manual stuff.


Based on this need, I have created the same calculator but this time in T-SQL. The same concept but in runs in a SQL environment directly, returning the correct MAXDOP value without using any input as the original approach.

Here is an example of the output:

MAXDOP recommendation at Instance level for also T-SQL statement suggestion to re-configure value

At you can see, it provides you the current and recommended MAXDOP configuration also including the syntax to change it in case it is wrong. For SQL Server 2016 or major, it also returns the MAXDOP value configured at database level.

MAXDOP recommendation at DB and Instance level for SQL Server 2016 (and major) also T-SQL statement suggestion to re-configure MAXDOP


If you want to give try it, you can download the T-SQL script from my GitHub repository.

The requirement to execute this T-SQL script is to have SQLCMD mode enabled in SSMS, you can take a look at my previous post to learn how to enable it.

Please feel free to provide me feedback about this tool, stay tuned for more DBA mastery tips!

13 thoughts on “MAXDOP Calculator

  1. Fraction.Calc

    His maxdop calculator is really helpful to all database administrator and those personnel in-charge for the security and maintenance of the database.

  2. Mike Allen

    I searched SSSSOOOOO many places to get a good script for MAXDOP. After much comparing and research, this one is by far the best. Thank you so much for posting it!

    1. DBA Master Post author

      Those are great news Mike!!
      I’m working on the version 2 of this script also thinking to make some kind of integration with Azure data studio.

      Thanks for the feedback by the way, please don’t hesitate to ping me in case you have more comments or suggestions.

    1. DBA Master Post author

      It should but I will not recommend. Azure SQL DB is a PaaS database, that means you have no control over the OS and hardware resources because everything is fully managed by Microsoft.

      In case you are interested to change the MAXDOP for a particular query, you can do it via query hint.

  3. Brandon

    How should you handle this when you have multiple instances on a server. For example, I have a server that has 2 sockets with 8 cores per socket and 16 logical processors per socket so 32 total logical processors. When I run your script on any of the instances it tells me MAXDOP should be set to 8. Should I set it to 8 for all instances or should I scale that back?

    1. DBA Master Post author

      This is a great question! Each instance of SQL Server works isolated from the rest installed on the same box. I would start looking at the CPU utilization and wait stats of each instance, it will help you to understand the workload better. You can use PerfMon, DVM’s or XE for such thing, let me know if you need a hand will be more than happy to help.

      Without having any performance data to analyze and understand the workload it is quite difficult to help. Once you understand the workload you can plan how to configure MAXDOP by instance, perhaps will be one of those instances that is not using parallelism at all or too much.

  4. Tube

    I understand what is said in the MS link and the post but would all of this change if I were to run 2 instances of SQL on a single SQL 2014 SP2 cluster? My servers have 2 processors, 8 cores each for a total of 16 cores. (32 Logical Processors) If I set MAXDOP on both instances to 8 will that be potentially assigning all processors to be accessed in parallel if a complex SQL operation were requested by both instances at the same time? Or does MAXDOP refer to logical processors and not cores? Thanks in advance!

    1. DBA Master Post author

      This is a great question! Pretty much the same as the previous question from Brandon.
      I will say the same, each instance of SQL Server works isolated from the rest installed on the same box. I understand you have a FCI but that doesn’t change the fact that a SQL Server instance could run on any of the nodes.

      It is really hard for me to tell you what’s best for you without having access to your environment. For example, something important to know is about the NUMA. That will determine how many logical processors MAXDOP can use, and yes the number is related to logical processors been 16 the maximum.

      Hopefully this information helps you.

  5. Andrews

    Hello, very good contribution, thank you very much!! is there a calculator for the Cost Threshold?

  6. Muhammad Subhan

    Hi… Im Subhan as DBA. worked for Bodyshop Indonesia.
    I had time to think about how to determine the value of Maxdop … when I searched for the calculation of Maxdop it turned out that I got this sophisticated query … thank you very much


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.