Linq and parallelism - SQL Server

Question

I ran into a problem with LINQ To Entities.

If I run my LINQ query, then it uses Parallelism (collect streams and repair stream) in terms of execution, which causes a lot of CXPACKET expectations.

But if I ran the LINQ translated query (which I received through the ToTraceString function) directly to my SQL server, then the execution plan does not contain parallelism.

Why is there a difference regarding SQL parallelism when I run it through LINQ or the SQL query itself?

How can I solve this problem? I want my LINQ query to run the same way as when I ran SQL directly.

Example implementation plans:

using LINQ:

SQL directly:

I can post my SQL query, but I don't think it can help here ...

Answer 1

Is CXPACKET actually a problem?

When SQL Server receives a query, it optimizes it. If the estimated cost of the request is greater than the cost threshold for Parallelism, it will consider the parallel request. You can always raise this threshold if too many queries are parallelized.

I would like to know if there is any difference between an entity framework query and your query, which can raise the estimated cost.

In any case, CXPATCKET expectations are NOT a problem. They are a natural part of any concurrent request, and we do not pay extra money for quad-core servers with multiple threads so that we can run everything in a series. Another thing to check is the network.

When streaming IO over the network, this may take longer if you return a bunch of results. This is why running a query in SQL Server Management studios may return quickly, but your query from another server may take some time.