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!
