The SUM aggregate is required to return NULL if no rows are aggregated. SQL Server implements this requirement by also computing COUNT_BIG over the summed expression, and using a later CASE expression to return NULL if the count was zero.
SQL Server may evaluate the aggregated expression twice - once for the SUM and once for the hidden COUNT_BIG. This occurs even where the SUM and COUNT_BIG operations reference an expression label (e.g. [Expr1003]).
Where the query plan shows an expression label twice in the same iterator it seems reasonable to expect that query execution would compute the value once and reuse it (assuming the expression is deterministic).
If the query plan instead showed the aggregate computing SUM(-some sql expression-) and COUNT_BIG(-same sql expression-) instead of referencing [Expr1003], we might suspect that the value would be recomputed, and perhaps look to rewrite the query to help expression matching.
In the reproduction script given below, I am computing SUM(CONVERT(BIGINT, CHECKSUM(REVERSE(padding)))) over 100,000 rows. Since 'padding' is a CHAR(8000) column, this is a very CPU-intensive operation.
Using the most natural SQL expression for the query, the optimizer produces a plan containing a Compute Scalar defining:
[Expr1003] = Scalar Operator(CONVERT(bigint,checksum(reverse([[padding].[padding])),0)).
The following Stream Aggregate computes:
[Expr1009] = Scalar Operator(COUNT_BIG([Expr1003]))
[Expr1010] = Scalar Operator(SUM([Expr1003]))
The value of [Expr1003] is calculated twice per row. Rewriting the query to use an OUTER (not CROSS!) APPLY, the expression is evaluated only once, halving response time. An alternative workaround to using opaque SQL constructions is to add a (non-persisted) computed column for the expression and indexing it.
I'm reporting this as a bug specifically for the case where SQL Server adds a COUNT_BIG expression to support the semantic of SUM (and AVG).
I really do appreciate that SQL Server makes no guarantees about the order of scalar expression evaluation, or the number of times a scalar might be evaluated during execution, and that these are all wider problems that no doubt already keep the developers awake at night.