I have a big linq to sql query which runs in parallel mode.
It is returning results in 8 seconds and it is pretty good.
I noticed in the execution plan that the extreme right part(from where execution plan is read) of the execution plan has a serial portion.
I checked the execution plan and i see that the serial part is calculating some expressions such as the where conditions Start Date and End date is applied and calculated.
But i don't understand why it is being in serial mode.
AS per the screenshot,it says Scalar Operators[Expr1169] and Scalar Operator(Expr1170)
As you can see in the next screenshot, after the expressions are calculated and after from Nested Loops ,the execution goes into Parallel mode.
I am trying to understand the serial part of the query ,because i have optimized the query few times to strictly run in parallel mode.
I have removed some scalar functions earlier and other db objects which can cause serial execution.
Screenshot 2
Please find the anonymized execution plan.
Execution PLan
Additional Info:
- Statistics are updated
- Index Fragmentation is taken care off
- 96 GB out of 128GB is used by SQL Server.
- SQL Server 2016
Statistics are updated.
Best Answer
In answer to the question being asked (why is this portion of the query serial), I would ask another question: what would you gain from that part of the query being parallel?
Zooming in a little on one of the small serial portions of the plan that you mentioned:
There is only one row coming out of the "Merge Interval" operator. Having that one row flow in to a parallel nested loops join would result in essentially the same behavior as the current plan: a serial nested loops join (with the added negative repercussion of there being unnecessary thread reservations).
There will be at most 2 rows coming out of the "Merge Interval" operator, depending on the specific values of the parameters being used in the query (manifested as the separate "Constant Scan" operators see this question and answer for a general explanation of what's going on there). In the 2-row scenario, you're still unlikely to get a lot of benefit from parallelism here.
Overall: even if you were able to manage to get the NL join to be parallel, it doesn't seem like it would help you very much.
For a more general treatment of why parallelism might be inhibited in portions of a query plan, or the entire thing, see SQL not engaging parallelism for extremely large query.