Many of you at least should have heard about Max Degree of Parallelism setting, which exists in SQL Server from the early versions. By default, its value is zero, which means that SQL Server determines by itself the optimal number of threads for your queries. However, let us go a little bit further and delve into mastering and controlling parallelism inside SQL Server Database Engine.
In this post, I’m not going to discuss, how to calculate the best MAXDOP value, but just discover, how queries behave depending on it. It could be changed either by SQL Server Management Studio Server Properties windows or by T-SQL sp_configure command.
For example, if set to one, it forces all the query plans to be serial, with no parallelism at all, like in the example below.
Don’t do this in production, if you are not 100% sure or you just read an article somewhere over the Internet, telling you that it’ll help to eliminate CXPACKET wait “problem”.
However, users and developers are still able to control the parallelism for selected queries by using MAXDOP query hint. They can override the server option easily just by defining the needed value.
Moreover, if we look at this query through sys.dm_exec_query_profiles DMV while it’s running, we’ll see, that it consumes 10 threads.
Thus, everyone can go over the server limitation set by DBA. Actually, this is normal behavior, because it’s not the limitation option. It just allows you quickly setting up the best option for all queries and then using MAXDOP hint tune the selected ones with other value.
But what can you do to guard your system from being overloaded by someone’s heavy uncontrolled parallel queries? If you really want to restrict the parallelism MAXDOP values, you should use Resource Governor. To read more about it and learn how to set up look here. I will just show its capabilities regarding the controlling parallelism.
Every workload group inside it has its own Degree of Parallelism option. Again, by default, it equals to zero, which means, that it’s not working. But if set up in 5, for example, it will limit all the queries, that are going to run with the degree of parallelism higher that this value. Therefore, the previous example with MAXDOP 10 option will be limited to five threads only.
Hence, the Resource Governor is the only feature, that should be used for controlling and limiting the degree of parallelism for different groups of users, while MAXDOP server option and hint are just for setting the optimal value for all and selected queries correspondingly.