July 3, 2019 at 10:55 am
has anyone seen where a situation where there was 1 large table joining a few smaller ones causes a lot of cxpacket waits? Query runs about 1.5 hours. If I change it to use option (maxdop 1) it runs in 30 minutes. I can furnish other info but from what i have here, is there an obvious explanation?
July 3, 2019 at 11:12 am
CXPacket waits (prior to SQL Server 2016 sp1 and above) just mean that parallelism is occurring on your system. Look to other wait types to see what issues are happening.
Yeah, you have a query that is going parallel but is evidently not benefiting from parallelism. That happens. It's common.
Instead of setting MAXDOP to 1, either on the server or through a query hint, I'd suggest checking your "Cost Threshold for Parallelism" setting. Is it the default value of 5? Probably needs to be a higher number. Get the estimated plan for this query and look at the total cost. Depending on that value, set your number higher. However, if it's some stupid insane number > 100 ignore this and use the MAXDOP hint instead.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 4, 2019 at 12:46 pm
Thanks Grant!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply