Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?

Question

I'm testing against SQL Server 2019 CU14. I have a pure row-mode query that selects the top 50 rows from a complicated view. The full query takes 25426 ms of CPU time at MAXDOP 1 and 19068 ms of CPU time at MAXDOP 2. I'm not surprised that the parallel query uses less CPU time overall. The parallel query is eligible for bitmap operators and the query plan is different in a few ways. However, I am surprised by a large reported difference in operator times for the top N sort.

In the serial plan, the top N sort is reported to have taken around 10 seconds of CPU time by operator execution statistics:

The MAXDOP 2 plan reports around 1.6 seconds of CPU time for the same top N sort:

I don't understand why such a large difference is reported between the two different query plans. The compute scalars in the parent operator are very simple and cannot explain the discrepancy in operator times. Here is what they look like:

[Expr1055] = Scalar Operator(CASE WHEN COLUMN_1 IS NULL THEN (0) ELSE datediff(day,COLUMN_1,getdate()) END),
[Expr1074] = Scalar Operator(CASE WHEN [Expr1074] IS NULL THEN (0) ELSE [Expr1074] END)

There are other compute scalars in different parts of the plan. I uploaded anonymized actual plans for the serial plan and the parallel plan if someone wants to review them.

When I load the full query results without TOP into a temp table and perform a TOP 50 sort on the temp table, both the parallel and the serial plan take around 1200 ms of CPU time to perform the sort. So, the reported operator time for the parallel sort in the full query feels reasonable to me. The ten seconds for the serial query does not.

Why does the serial top N sort have a much higher reported CPU time than the parallel sort? Is it truly so much less efficient or could this be a bug with operation execution statistics?

Answer 1

It's hard to be sure based on an anonymised plan without a repro, but the first mechanism that springs to mind is deferred expression evaluation.

In the serial plan, the Sort may be responsible for evaluating a large number of expressions deferred from earlier operators. This includes values that must be materialised at that point in the sort buffers, not just sort keys. The cost of evaluating the expressions is therefore borne by the Sort.

In the parallel plan, these expressions might have to be evaluated earlier, for example when values must be materialised in exchange buffers at Parallelism operators. The cost of the expression evaluation is spread out among plan operators rather than concentrated at the Sort.

There are a large number of expressions (Exprxxxx) at the Sort. A selection is shown below (they would not all fit in a single screenshot):

I did not attempt to track which of these might be responsible because the size and anonymized nature of the plan makes this infeasible.

There are many operators that can define expressions, not just Compute Scalar. Quoting from my article linked previously:

Compute Scalars are not the only operators that can define expressions. You can find expression definitions with labels like [Expr1008] in many query plan operators, including Constant Scans, Stream Aggregates...

Expression evaluation can be deferred for a long time, across many operators. The key point is evaluation is deferred until the result of the expression is needed by another operator, or even until the server needs to assemble rows for return to the client. Blocking or semi-blocking operators are just one example where expression evaluation might need to be materialised.

Users of other languages might be familiar with the concept under the name 'lazy evaluation'. Computation is deferred for as long as logically possible.

Naturally, materialising the results in a temporary table means all expressions are evaluated. This explains why you see expected CPU times for execution from the temporary table source.