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.
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!
I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.
I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.