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 …
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:
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.
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!
I’m a Microsoft Data Platform MVP also a very experienced multi platform DBA (MySQL, Oracle, SQL Server, SQL Azure) with over 10 years of experience in the database field.
I work in database support as primary consultant and DBA manager for larger US based companies in the healthcare, insurance, retail, food and energy industries.
I have a few certifications under my belt MCSE, MCSA, MCP, MCTS, ITIL v3, Docker, Kubernetes and RedHat openshift.
International speaker, blogger, Guatemala SQL community group leader, mentor.
Love everything related to technology, sci-fi and video games.