0

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 1 enter image description here

Screenshot 2 enter image description here 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.

Improve this question
2
  • Can you add the query? Looks like you have a case statement wrapped in isnull.
    – Jacob H
    Jul 10, 2019 at 15:50
  • Actually the query is pulling from a view which has like 10 tables joins and these CASE statements exists in the view.Yes there is case statement wrapped in IS NULL in the view.
    – user9516827
    Jul 10, 2019 at 15:57

1 Answer 1

Reset to default
1

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:

screenshot of plan explorer, zoomed in on the serial portion of the query

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.

Improve this answer
2
  • I am trying to fix possible issues as i am not sure what is the reach cause.
    – user9516827
    Jul 12, 2019 at 14:06
  • 1
    What is the issue you are trying to fix then? This seems like an X Y problem.
    – Jacob H
    Jul 15, 2019 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.