MAXDOP settings SQL DW

Question

Hi,

My current maxdop settings for SQL DW is setting at 8 and cost threshold for parallelism setting at 75. The CXPACKET wait is the most common waits observed.

I have two NUMA nodes and recommended setting will be 16. Since it is a SQL DW, I am not sure what will be best setting.

Regards,
kccrga

Answer 1

High CXPACKET isn't necessarily a problem, it just show you that you have parallelism. Which I assume that you want, considering it is a DW. If you can't tune the queries, then just accept that something need to be at the top of the list and in this SQL Server CXPACKET is the top one.

You can of course change MAXDOP and see how things plays out. We can't say that, since we don't have access to your database and your queries. It is possible that some queries will be faster by having access to more CPUs. OTOH other stuff running at the same time might suffer. Only way to tell is to test.

Answer 2

Hi @kccrga ,

This is a common phenomenon when queries running in a fact table in a data warehouse system as typical query in a fact table needs a large portion of the data to be extracted.

If the CXPACKET waiting is excessive and cannot be reduced by tuning the query (such as adding indexes, update statistics), consider adjusting the Cost Threshold for Parallelism or lowering the Max Degree of Parallelism (MaxDOP).

some articles for reference:
https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/
https://www.sqlshack.com/how-to-avoid-cxpackets/

---

If the answer is helpful, please click "Accept Answer" and upvote it.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November