September 21, 2015 at 12:05 am
This was removed by the editor as SPAM
September 21, 2015 at 1:26 am
Very interesting question.
Some resource material:
What is the CXPACKET Wait Type, and How Do You Reduce It?[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 21, 2015 at 6:04 am
Great question. Here's some more reading material on the different waits and what they mean.
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
September 21, 2015 at 6:11 am
nice question steve, thanks for sharing
September 21, 2015 at 10:12 am
I'm not sure "reducing CXPACKET waits" is really a good goal to have, unless you suspect that the query would be more efficient with less parallelism... I think of CXPACKET as a neutral thing. It literally just means there is parallelism, and not all threads finish at exactly the same time.
It does make slow queries hard to troubleshoot though, since it obscures the actual cause of query running slow... so for troubleshooting I'd consider temporarily setting MAXDOP to 1 for the query.
September 21, 2015 at 10:12 am
I am not wrong to want to do something about the indication. The question is misleading if "parallelism" was all you were looking for.
This is verbatim from the MSDN: "Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism."
sys.dm_os_wait_stats (Transact-SQL)
Of course parallelism is happening. That's obvious. What does it indicate? What does it suggest? One does not observe an oil light on their vehicle dashboard and wonder, "I have oil flowing in my car's engine". They see it as an indication that something needs to happen: namely, likely an oil change, check the engine, etc.
In this case, what to do about CXPACKET? For starters, as MSDN suggests, lower adjust the cost threshold, or lower the degree of parallelism. i.e. "your processes may be too parallel".
September 21, 2015 at 12:09 pm
Almost entirely correct.
The actual correct answer is that a CXPACKET wait indicates that one thread in a parallel plan has to wait for others as part of the synchronization.
Whenever a plan runs in parallel, there will always be CXPACKET waits, simply because how parallel execution is implemented. Hence the common warning that one should not do any knee-jerk reactions when seeing CXPACKET waits.
But a high amount of CXPACKET waits does indicate a problem. When multiple threads are working on a query, synchronization (and hence waiting) is unavoidable, but it becomes a problem when some threads have to wait a very long time - that means that the work distribution among threads is skewed.
So far the easy part. The hard part is indentifying how much CXPACKET waits is too much (hard because there is no absolute number, it is relative to the amount of work done in parallel and the amount of threads used). And the even harder part is fixing this - you'll have to find the queries with the skewed distribution, identify the root cause of the skew and then find a way to fix it. Three steps, and none of them has an easy standard tool. Good luck! 😉
Or you can disable parallelism on yor system. A lot of your queries will probably go slower, but at least you eliminate all CXPACKET waits. :Whistling:
September 22, 2015 at 1:11 am
Ed Wagner (9/21/2015)
Great question. Here's some more reading material on the different waits and what they mean.http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
This is a vey useful link, thank u.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 22, 2015 at 5:20 am
Hany Helmy (9/22/2015)
Ed Wagner (9/21/2015)
Great question. Here's some more reading material on the different waits and what they mean.http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
This is a vey useful link, thank u.
No problem. There's a lot of good stuff on Paul's site. Enjoy.
September 22, 2015 at 7:23 am
porter.james (9/21/2015)
It does make slow queries hard to troubleshoot though, since it obscures the actual cause of query running slow... so for troubleshooting I'd consider temporarily setting MAXDOP to 1 for the query.
Good idea, thanks.
September 22, 2015 at 12:11 pm
Iwas Bornready (9/22/2015)
porter.james (9/21/2015)
It does make slow queries hard to troubleshoot though, since it obscures the actual cause of query running slow... so for troubleshooting I'd consider temporarily setting MAXDOP to 1 for the query.Good idea, thanks.
I respectfully disagree. A parallel plan will *often* have the same shape as a serial plan - but not always! Some operators are relatively cheaper when running in parallel; others are relatively more expensive, so when you disable parallelism when tuning, you may not be tuning what will eventually be the actual plan.
(And if you use columnstore indexes, it gets even worse - batch mode execution is only possible in parallel plans...)
September 27, 2015 at 8:28 am
Interesting question.
I don't much like playing with MAXDOP as first response to an excess of XCPACKET waits. The optimizer isn't stupid but the default value of Cost Threshold for Parallelism is (or was last time I looked) and it makes sense to adjust that to something sensible first and see if XCPACKET WAIT still happens so frequently as to suggest there's a problem. Only if that doesn't have the desired effect should one play with MAXDOP.
Brent Ozar described it nicely a couple of years ago (www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/[/url]).
I guess if all your quereis are massive data crunchers the above doesn't apply, but even then you will need to keep MAXDOP high enough to get decent response on those massive queries.
I rather like Hugo's comment. Maybe I would express it more forcibly that he did. It is pretty crazy to do performance tuning in an environment that is radically different from the intended production environment, and setting MAXDOP to 1 to do the tuning and having the tuned SQL run with MAXDOP 0 or 16 or whatever is a perfect example of that craziness.
Tom
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply