Parallelism denied

sqlkoala 2015-05-06 12:40:37

I have trouble with a query that does not "go parallel". I think I have figured out the reason (hard enough) but I don't know how to fix it.
SQLkiwi 2015-05-06 13:15:57
This question essentially asks, "how do I write this query to get a parallel plan".

There are often ways to express a query differently (with the same semantic) to get a parallel plan, but it is hard to propose a specific rewrite from the information provided. A statistics copy of the tables involved and hardware details like number of schedulers and max memory setting would be required (all these things affect plan choice, including the use or not of parallelism). Knowledge of the data and task being performed might also be needed. All in all, this would be consultancy work 🙂

Perhaps the simplest answers would be:

  1. Capture the desired parallel plan and use that in a plan guide; or
  2. Hint the query with QTO 8649 (beware, unsupported) also using a plan guide. This use does not require sysadmin/control server permissions at runtime.

Appropriate permissions are required to create the plan guide(s) of course.

Running queries directly from Plan Explorer captures useful performance information not present in *.sqlplan files.