A few days ago, I was call to see about a query that was some strange behavior.
The query was composed by two queries with a UNION ALL .
Q1 UNION All Q2
and was taking around 15-20 sec to execute.
Q1,Q2 have like 15-20 tables and views joining together. There were filtering for this month and year. Q1 has the result data, Q2 – hold the old data, fox data so, no result here.
Taking each on separately and execute them, they were finishing in less then 1 sec.
Q1 – was able to produce a parallel plan , Q2 – was unable to produce a parallel plan.
Reunion them, QO -was unable to produce a parallel plan because of Q2.
MaxDOP = 0 (this setting should be change , but I’m not in control) –
Threshold for Parallel = 5 (this setting should also be change , but I’m not in control)
No Resource Governor limitation in place…
From this point , they should trigger parallel plan. Looking at the execution plan , in xml output, here there was NonParallelPlanReason = CouldNotGenerateValidParallelPlan .
Button line, in Q2 , was a view, that had an scalar function , this was disable parallel option. Rewrite it , and then all when well.
Conclusion:
Look at execution plan, also in xml version, you will find good information.
S
Sql Server 