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!
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.