Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know the knee-jerk solution is to turn off all parallelism by setting MAXDOP to 1 - sounds like a bad idea. But another idea is to increase the cost threshold before parallelism kicks in. The default of 5 for the cost of an execution plan is fairly low.

So I was wondering if there's a query out there already written that would find me the queries with the highest execution plan cost (I know you can find those with the highest duration of execution and so on - but is the execution plan cost retrievable somewhere, too?) and that would also tell me if such a query has been executed in parallel.

Does anyone have such a script at hand, or can point me in the direction of the relevant DMV, DMF or other system catalog views to find this out?

I'm sure you can do this for RECENT queries by parsing the xml in the plan cache...
–
JNK♦Jul 23 '12 at 18:42

@JNK: so that would be stored in dm_exec_cached_plans? There's a lot of DMV's and DMF's in that regard - but I still haven't found any of them showing the cost of the execution plan ...
–
marc_sJul 23 '12 at 18:44

CXPACKET waits are not a problem per se. The issue is typically an uneven distribution of work across threads such that one is still working away after the others are done. If you can identify the thread that's still working, it may have a waittype of interest that you can do something about. Also take a look at the estimated vs actuals row counts for these queries as that can be the cause of poor distribution of rows across threads.
–
Mark Storey-SmithJul 23 '12 at 18:45

3 Answers
3

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of work, which is why it is such a prevalent "fix."

If you can get an actual plan for a query that seems to be responsible for high CXPACKET waits, load it into SQL Sentry Plan Explorer. There's usually a reason behind this; we show which parallel operations led to thread skew, and you can easily correlate that to estimates that are off (we highlight operations with estimates that are off by a at least certain threshold). Usually the underlying problem is really bad/out-of-date (or unavailable) statistics.

Unfortunately what you'll find in sys.dm_exec_cached_plans are estimated plans. They won't tell you whether the plan went parallel when it was actually used, because the actual plan is not what's cached. In some cases you expect to see both a serial and parallel plan for the same query; this is not how SQL Server deals with the situation for parallel plans that might be parallel at runtime. (Lots of information about that here.)

Thanks for your insightful response, Aaron! I'll try and see if I can get an actual execution plan of a horrible query - and see if the excellent SQL Sentry Plan Explorer can help me identify the issue(s) - thanks again!
–
marc_sJul 24 '12 at 8:58