This is part twenty-five of the plansplaining series, and the third part that covers window functions.

I have already explained how basic window functions work, with or without a window frame. When a window frame is present, a Window Spool operator will, for each row, return first that row, and then all rows that are visible in its frame. A Stream Aggregate then computes the requested aggregations on those rows.

In the previous part, I then explained how “fast-track optimization” severely reduces the amount of work in cases where the window frame starts at UNBOUNDED PRECEDING. I also showed that the optimizer sometimes rewrites a single window aggregate that does not qualify for fast-track optimization, into two separate new window aggregates that do qualify.

UNBOUNDED FOLLOWING

The examples so far have all been for either running aggregations (any window frame that starts at the start of the partition, as specified by UNBOUNDED PRECEDING; and that ends at or near the current row, as specified by one of CURRENT ROW, number PRECEDING, or number FOLLOWING); or for moving aggregations (any window frame that both starts and ends at or near the current row).

However, we have not yet looked at any example that uses UNBOUNDED FOLLOWING as the end point of the window frame. And the reason for this is that there is in fact no support for UNBOUNDED FOLLOWING in the execution plan. And yet, this is perfectly valid syntax in your T-SQL queries. When you use it, the optimizer does some extra work, so that you get correct results for UNBOUDED FOLLOWING, even though the execution plan does not support it directly.

The great reversal

Let’s look at a simple example that uses UNBOUNDED FOLLOWING as the end point:

SELECT p.ProductID,
       p.Name,
       p.ListPrice,
       p.ProductLine,
       SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                               ORDER BY p.ListPrice
                               ROWS BETWEEN 3 PRECEDING
                                        AND UNBOUNDED FOLLOWING) AS WindowSum
FROM   Production.Product AS p;

If you execute this query, you will notice that the results are not presented in order of ascending ProductLine and ListPrice, as was the case with previous examples. The results now appear in order of ascending ProductLine, and then descending ListPrice. A strange change, but since the query does not have any ORDER BY at the end, SQL Server is indeed free to return the data in any order it sees fit, as long as the results are correct. And they are.

Perhaps we should look at the execution plan:

(Click on the picture to enlarge)

At first sight, this looks exactly the same as the execution plans we saw in the previous parts. But as always, you cannot just look at the pretty picture. You need to dive into the properties to see where and how this execution plan differs from the previous ones.

I will not bore you with all the details. So instead, I will instantly zoom in on the operators that show a relevant difference with the other execution plans we have seen for framed windows.

Let’s start with the Sort operator. In the properties, you see confirmed what we already noticed when we looked at the results: the sort order in this execution plan is different from the sort order in all previous execution plans. The Order By property reads (simplified): “ProductLine Ascending, ListPrice Descending”. So this operators sorts by ProductLine, and within each ProductLine rows are sorted with the highest ListPrice first.

Another interesting operator to look at is the Compute Scalar. It computes just a single new column, BottomRowNumber1005, defined as RowNumber1004 + 3. And this is weird, in two ways. First, we saw in previous posts that the “bottom” row number is always used to hold the last row of the frame, which in this case would be UNBOUNDED PRECEDING and not the current row number + 3. And second, even if we accept that it now somehow suddenly does represents the start instead of the end of the window frame, then it is still weird, because current row + 3 would be 3 FOLLOWING, not the 3 PRECEDING that we have specified in the query.

Finally, there is no computation for a TopRowNumber column. You can check the rest of the execution plan: no such column is computed anywhere, and no such column is passed to the Window Spool operator. As I already explained in the previous post, if no TopRowNumber is passed, then Window Spool assumes it is 1 for every row, which effectively means it uses UNBOUDED PRECEDING.

All these changes seem weird in isolation, but they make sense when you look at them in combination. We ask to sort by ascending ListPrice within ProductLine, and then use a window frame that ends at the end of the partition, so at the highest ListPrice. Instead, the execution plan orders by descending ListPrice within ProductLine, and then starts the frame at the first row … which due to the reversed sort order is that same highest ListPrice! And in the same way, the row that is 3 rows before the current row in the requested sort order, is actually at 3 rows after the current rows in the reversed sort order.

So in short, the optimizer changed the sort order from what we requested to the reverse. And it then had to modify the window frame specification, so that it would still return the same desired results. In effect, the query that was actually executed was this one:

SELECT p.ProductID,
       p.Name,
       p.ListPrice,
       p.ProductLine,
       SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                               ORDER BY p.ListPrice DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND 3 FOLLOWING) AS WindowSum
FROM   Production.Product AS p;

And because of this reversal of the sort order, with the accompanying change to the window frame specification, the execution plan no longer has to do an UNBOUNDED FOLLOWING (which, as I mentioned, is not supported). The changed version uses UNBOUDED PRECEDING, which is not only supported, but even qualifies for fast-track optimization!

Performance impact

It is of course great that, after reversing the sort order of the window frame specification, the aggregation can even use fast-track optimization. But you still need to be aware of a potential performance impact that might not be obvious.

What if we add ORDER BY ProductLine, ListPrice to the query?

SELECT   p.ProductID,
         p.Name,
         p.ListPrice,
         p.ProductLine,
         SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                                 ORDER BY p.ListPrice
                                 ROWS BETWEEN 3 PRECEDING
                                          AND UNBOUNDED FOLLOWING) AS WindowSum
FROM     Production.Product AS p
ORDER BY p.ProductLine,
         p.ListPrice;

Just looking at the query, the window frame and the ORDER BY appear to use the same sort order. We expect this to be efficient. But in reality, SQL Server has to reverse the sort order for the window frame. And hence, it then later has to resort the results, so that the data is presented to the client in the specified order. The execution plan for the query above is the same as the one we saw before, but with one extra Sort operator added as the left-most operator. Which, for the record, increases the estimated cost of the execution plan by a little over 50%.

Even worse is that the optimizer is not very good at identifying the best order to compute framed window aggregates. Take a look at the two queries below. Both compute the exact same columns. The only difference is the order in which they appear in the SELECT clause. But there is no rule that SQL Server has to compute them in that order. So I would expect both queries to have the exact same execution plan. But they don’t.

SELECT   p.ProductID,
         p.Name,
         p.ListPrice,
         p.ProductLine,
         SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                                 ORDER BY p.ListPrice
                                 ROWS BETWEEN 3 PRECEDING
                                          AND UNBOUNDED FOLLOWING) AS WindowSum1,
         SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                                 ORDER BY p.ListPrice
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND CURRENT ROW) AS WindowSum2
FROM     Production.Product AS p
ORDER BY p.ProductLine,
         p.ListPrice;

SELECT   p.ProductID,
         p.Name,
         p.ListPrice,
         p.ProductLine,
         SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                                 ORDER BY p.ListPrice
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND CURRENT ROW) AS WindowSum2,
         SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                                 ORDER BY p.ListPrice
                                 ROWS BETWEEN 3 PRECEDING
                                          AND UNBOUNDED FOLLOWING) AS WindowSum1
FROM     Production.Product AS p
ORDER BY p.ProductLine,
         p.ListPrice;

I won’t show the full execution plans here, but you can compare the estimated cost of the two in the screenshot below:

(Click on the picture to enlarge)

And the reason for the difference is quite simple. For the first query, the execution plan first sorts by ProductLine ASC, ListPrice DESC (the reversed sort order, for WindowSum1), then resorts by ProductLine ASC, ListPrice ASC (the regular sort order, for WindowSum2), which is also the correct order to return the data in. In the second query, the first sort is by ProductLine ASC, ListPrice ASC (for WindowSum2), then by ProductLine ASC, ListPrice DESC (for WindowSum1), but now it needs to once more sort by ProductLine ASC, ListPrice ASC as the requested order to return the results to the client.

If you agree with me that the optimizer should be able to recognize these two queries as effectively the same, and to decide that for query 2, WindowSum1 should be computed first and not second, then please vote for my suggestion here.

When reversing doesn’t work

But, there is one edge case where reversing the sort order of the window frame does not have any effect. And that is when both the start and the end point are unbounded, as in the query below:

SELECT p.ProductID,
       p.Name,
       p.ListPrice,
       p.ProductLine,
       SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine
                               ORDER BY p.ListPrice
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND UNBOUNDED FOLLOWING) AS WindowSum
FROM   Production.Product AS p;

In this case, we can change the sort order … but then the boundaries will still be ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. So that doesn’t help. And yet, we do get correct results when we execute this query. And if you look at the execution plan, you will see that not even a single Window Spool operator is used in this case!

(Click on the picture to enlarge)

The optimizer has, in this case, realized that a window frame that always starts at the first row of the window (partition) and ends at the last row of the window, means that there effectively is no frame. We always see the entire window. The query is effectively exactly the same as a query that uses a window aggregate without a frame, so without both the ORDER BY and the ROWS BETWEEN specification:

SELECT p.ProductID,
       p.Name,
       p.ListPrice,
       p.ProductLine,
       SUM (p.ListPrice) OVER (PARTITION BY p.ProductLine) AS WindowSum
FROM   Production.Product AS p;

And indeed, both the results as well as the properties of the Sort operator in the execution plan prove that the optimizer fully ignored the request to order by ListPrice.

And if you have read all my Plansplaining posts, then you might even have recognized the execution plan as one I have already discussed at length here, and mentioned again here.

Summary

There is no support for UNBOUNDED FOLLOWING in execution plan operators. If a query asks for a framed window that starts on or near the current row and ends at UNBOUNDED FOLLOWING, then the optimizer reverses the sort order, so that now the window frame starts at UNBOUNDED PRECEDING and ends on or near the current row, which is the regular fast-track optimized running aggregate. This can cause unexpected performance effects if you submit a query where at first sight all sort orders are the same, but then they chance because the optimizer has to reverse a few.

The edge case of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is also supported in T-SQL, but is effectively the same as simply not specifying a window frame at all.

That concludes this post about execution plans for window functions. In the next post about framed windows, we’ll shift our focus to OVER clauses that use the RANGE instead of the ROWS keyword to specify the frame.

As always, please let me know if there is any execution plan related topic you want to see explained in a future plansplaining post and I’ll add it to my to-do list!