Last month in June, I wrote about capping SQL Server's memory so that it does not use up all available memory. You can read about it here. In this blog, I want to look at another setting in SQL Server, and that setting is the Max Degree of Parallelism. We will also look at another setting, Cost Threshold for Parallelism, which affects the Max Degree of Parallelism option. Let's take a look at these options and what you need to know.
What is it? How does it work?
So, what is the Max Degree of Parallelism option? This option in SQL Server lets SQL Server control the number of CPUs it can use to run T-SQL queries in parallel or to perform indexing tasks in parallel. By default, the value is zero which means that SQL Server can use infinite (all) CPUs for each query. If the value set is not zero, at that point that the single query can use the number of CPUs specified.
When does SQL Server decide to use parallelism? It looks at the value set in the Cost Threshold for Parallelism. Note, that the value is not in seconds or minutes. It relates to the cost of the T-SQL query. I will not go deeper except to say that cost can be defined as the amount of computing resources required to fulfill the T-SQL query. The default value for Cost Threshold for Parallelism is 5.
Where can I find the values my system is using?
- Right mouse click on your SQL Server
- Select Properties
- From Select a Page, select Advanced
- Scroll down and locate Max Degree of Parallelism (Also note Cost Threshold for Parallelism option which we will discuss)
Should I change the values for Max Degree of Parallelism or Cost Threshold for Parallelism?
The short answer is no. Sage X3 recommends the default value of Max Degree of Parallelism to be 0. By default, the Cost Threshold for Parallelism is set to 5. That's how both of those options should remain. The settings are adjustable. If you feel there is a reason they should be changed, discuss with your Business Partner / SQL DBA otherwise, we recommend to leaving them as they are.
Your business partner or SQL DBA would need to perform analyses against the utilization of CPU and how queries are being distributed, which is beyond the scope of this blog. As always, if you are going to make any changes you should make any changes on your TEST servers and satisfy yourselves from your own testing there are no unexpected effects
introduced by the change.