parallelism question

Question

Hi All,

We have 16 CPU's, 256GB RAM Azure VM on which SQL Server 2017 is running. Our workload runs pretty well when MAXDOP = 1 and when we try to set the MAXDOP = 4 or MAXDOP = 8, we start seeing deadlocks all over and application team start complaining about slowness of some queries.

Questions

1. Did anyone came across workloads where MAXDOP =1 and works fine and why is that? otherwise we see a lot of deadlocks in app.
   We are thinking like we aren't able to take advantage of more CPU's.
2. What should be the value set for 'cost threshold for parallelism' for OLTP and OLAP datawarehouse instead of default value 5.
   Our db size is 3.5 TB?
3. How to find the right value to be set for MAXDOP for workloads running on MAXDOP=1. What things to be monitored and analyzed further?

Please share your thoughts.

Thanks,
Sam

Answer 1

Hi,

(1) Did anyone came across workloads where MAXDOP =1 and works fine and why is that? otherwise we see a lot of deadlocks in app.
We are thinking like we aren't able to take advantage of more CPU's.

There are multiple cases where a query run much better without parallelism. When you configured the MAXFOP = 1 basically you improved these queries and reduced obviously it also reduce deadlocks related to CPU waits.

When you are using MAXDOP 1, then he server will not use PARALLELISM and you probably get a totally different execution plans (EP) for many queries. Take a look on the EP of the queries impacted by the change.

Setting the MAXDOP in the server level impact all queries. In most cases this is a bad idea and what needed is to monitor the queries which better run without parallelism and use query level setting.

By the way, remember that when the max degree of parallelism option is set to 1, then SQL Server ignores the cost threshold for parallelism.

The following post can help you select what value should be used for MAXDOP

https://www.mssqltips.com/sqlservertip/2650/what-maxdop-setting-should-be-used-for-sql-server/

What should be the value set for 'cost threshold for parallelism' for OLTP and OLAP datawarehouse instead of default value 5.
Our db size is 3.5 TB?

You can get some recommendation from Microsoft here:

https://learn.microsoft.com/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#Recommendations

It is not related to the size of the DB as it is to the way the Db is used.

How to find the right value to be set for MAXDOP for workloads running on MAXDOP=1. What things to be monitored and analyzed further?

You should test your workload and monitor for any parallelism-related wait types such as CXPACKET.

The number of NUMA nodes is important for the dissasion and not only the number of CPU. You can use the following query to gather the current NUMA configuration for SQL Server 2016 and later versions:

select @@SERVERNAME, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc, socket_count, numa_node_count 
from sys.dm_os_sys_info

Check this document for more actions and info:

https://learn.microsoft.com/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Answer 2

Hi @Samantha r,

How to find the right value to be set for MAXDOP for workloads running on MAXDOP=1. What things to be monitored and analyzed further?

To find the optimal value for MAXDOP, you can use query performance metrics such as execution time, CPU utilization, and I/O wait time. You can start by testing queries with different values for MAXDOP and comparing the performance metrics. You should also monitor system resource usage such as CPU, memory, and disk I/O to ensure that the workload is not overloading the server. Additionally, you should analyze the query plans to ensure that parallelism is being used appropriately and that there are no performance bottlenecks such as inefficient index usage or excessive data movement.

You can refer to this thread: https://learn.microsoft.com/en-us/answers/questions/1181302/how-to-correctly-assign-maxdop-on-a-server-with-mu

For large databases such as 3.5 TB, a value of 8-16 may be appropriate. However, it's important to monitor query performance and adjust the value as needed to ensure optimal performance.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".