Exploring Window Functions Execution Plans

There are quite a few different ways that you’re likely to see window functions evidence themselves within your execution plan. Let’s take a look at one example.

Window Functions

For our example, I’ve got a pretty simple query:

SELECT soh.CustomerID,
       soh.SubTotal,
       ROW_NUMBER() OVER (PARTITION BY soh.CustomerID ORDER BY soh.OrderDate ASC) AS RowNum,
       Soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
WHERE soh.OrderDate
BETWEEN '1/1/2013' AND '7/1/2013'
ORDER BY RowNum DESC, soh.OrderDate;

Nothing to it really. What kind of execution plan does this generate? Here’s the plan with runtime metrics (aka, an actual plan):

I showed the missing index suggestion (and let’s remember, they’re just suggestions) just for completion and to show I’m not hiding anything. Potentially, adding an index could speed up the query. However, that doesn’t affect what we’re going to talk about within this plan.

Logically, what’s happening here? Well, the first Sort operator, and we know it’s the first logical operator because it has a Node Id equal to “0”. The SELECT operator, well, it isn’t. An operator. Not really. It’s a description of what’s happening in the plan, AND, a repository for meta data about the plan (Reason for Early Termination, Compile Time, Waits, etc.). We’re not talking about that today. Nope, the first Sort operator, looking at the properties, we see how the operator is sorting based on the Order By property:

With such a simple query, it’s easy to look at this and know what part of the query it references, but for bigger, more complex queries, it might not be obvious what that Expr1001 is referring to. But we’ll find it.

The next operator is the Sequence Project operator. This, according to the description, “Adds columns to perform computations over an ordered set.” Cool. And what does it do? It’s in the properties:

Hey! There’s Expr1001. So, what is it? Well, it’s the ROW_NUMBER() function that we’re generating through the partition window function. But where’s the partition happening? Let’s look at the description of the next operator, Segment:

Yeah, that’s helpful.

What does the Segment operator do? Segment.

What does it really do? Basically, it separates the inputs into groups and then outputs those groups, one at a time. We can see it in action, again, in the properties:

In short, the PARTITION command we gave SQL Server is partitioning, or grouping, on the CustomerID. Creating segments. That then goes to the Sequence Project operator which gives it the count of rows for that segment, and then it’s all sorted by the values we defined the ORDER BY statement at the end of the query.

Cool, but there’s one more thing to note, we have a second sort operator. That’s sorting the values by date before they get to be segmented though the segment operator.

And that, my friends, is a simple run through of how a window function shows up in an execution plan. Obviously, different functions will have different plans. This is just one simple example.

Oh, and if you want to follow the data flow, from right to left, we’re scanning the index, sorting the data by date, grouping it up CustomerID, then getting a count of those rows, then sorting by the count and date again.

Conclusion

This is a simple one. I mainly wanted to show how you logically follow a plan, especially how you track down the creating of things like Expr1001. As your window functions become more complex, the plans will too. So knowing how to walk through and understand how your query is resolved into a plan is useful.