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 …
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.
HOW TO CALCULATE
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
DOWNLOAD MAXDOP CALCULATOR
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!
Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.
He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.
Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.
His maxdop calculator is really helpful to all database administrator and those personnel in-charge for the security and maintenance of the database.
You’re welcome, I’m glad it helped you.
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!
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.
Can this calculator be used effectively with Azure SQL?
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.
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?
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.
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!
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.
so many thanks for you.
Hello, very good contribution, thank you very much!! is there a calculator for the Cost Threshold?
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