This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Are CPU/Memory waits better than CXPACKET/CXCONSUMER waits

I'm new to SQL Server database tuning. I read where the Microsoft default values for MAXDOP and Cost Threshold for Parallelism (0 and 5, respectively) are inherently flawed and should be adjusted. My SQL server is running on a Hyper-V VM with 24 vCPUs assigned. According to Microsoft, the recommended MAXDOP value is eight (8). I set the Cost Threshold to Microsoft's recommended initial value of fifty (50). That eliminated all CXPACKET and CXCONSUMER waits. I then dialed back the Cost Threshold to a point where I just began seeing a few CXPACKET and CXCONSUMER waits. that occurred at MAXDOP/Cost Threshold settings of 8/25, respectively.

This reduced the number of CXPACKET and CXCONSUMER waits considerably, but nearly doubled the amount of CPU/Memory waits. Total wait time increased almost 30%. I'm trying to understand if that's an improvement or not. Should I try to optimize the amount of CPU/Memory waits at the expense of total wait times...or should I accept more CXPACKET/CXCONSUMER waits if it drops the total wait time. Thanks!

Parents Reply Children
No Data