Scalar expression evaluated twice with SUM aggregate - by Paul White NZ

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 636382 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/16/2011 8:27:43 PM
Access Restriction Public


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.

Sign in to post a comment.
Posted by Paul White NZ on 1/28/2011 at 5:06 PM
Hi Eric,

That's great news!

Would I be correct in assuming that the fix will not be made available for users of current products, either because it does not meet the bar or because the change would be too complex/ large for a hotfix?

There's no sarcasm in that question by the way - I'm just wondering. This is a sneaky little side-effect with a large performance impact, and it is impossible for us to spot from the query plan (the 2008 plan also appears to evaluate Expr1003 once and refer to it twice in the Aggregate). The OUTER APPLY workaround is ugly and problematic as I hope the code sample illustrated.

As far as the private-build plan is concerned, the only difference I can see is the lack of a 'Scalar Operator' term surrounding the function on [Expr1003] - can we infer anything useful about expression reuse from that in general? I think I already know what your answer is going to be on that, but I have to ask :)

Thanks for the fast and interesting response!

Posted by Microsoft on 1/28/2011 at 3:24 PM
Thanks for your feedback. I've tested this in an internal build of SQL 11 (Denali) and it is fixed. I get this plan:

|--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
     |--Stream Aggregate(DEFINE:([Expr1009]=COUNT_BIG([Expr1003]), [Expr1010]=SUM([Expr1003])))
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT(bigint,checksum(reverse([tempdb].[dbo].[Test].[padding] as [T].[padding])),0)))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Test].[PK dbo.Test (id)] AS [T]))

Notice that the expression is evaluated once in the Compute Scalar operator, then referred to from the two different aggregates later. Both your "Natural query" and "Outer Apply Hack" take about the same time on my machine on my Denali build.

Best regards,
Eric Hanson
Program Manager, SQL Server Query Processing