If you ask people who tune queries why batch mode is often much more efficient with windowing functions, they’ll tell you about the window aggregate operator.
That’s all well and good, but there’s another, often sneaky limitation of fully row mode execution plans with windowing functions.
Let’s go take a look!
One thing that causes an early serial zone in execution plans is if you use a windowing function that only has the order by
For example, let’s look at the plans for these two queries:
WITH Comments AS ( SELECT ROW_NUMBER() OVER(ORDER BY c.CreationDate) AS n FROM dbo.Comments AS c ) SELECT * FROM Comments AS c WHERE c.n = 0; WITH Comments AS ( SELECT ROW_NUMBER() OVER(PARTITION BY c.UserId ORDER BY c.CreationDate) AS n FROM dbo.Comments AS c ) SELECT * FROM Comments AS c WHERE c.n = 0;
The resulting estimated plans look like this, using the 140 compatibility level:
In the top plan, where the windowing function only has an order by, the serial zone happens immediately before the Segment operator. In the second plan, the parallel zone carries on until right before the select operator.
If you’re wondering why we’re only looking at estimated plans here, it’s because repartition streams ruins everything.
In The Year 2000
In compatibility level 150, things change a bit (yes, a window aggregate appears):
And the window aggregate appears within the parallel zone. The parallel zone does end before the filter operator, which may or may not be a disaster depending on how restrictive your filter is, and how many rows end up at it.
Also note the distinct lack of a repartition streams operator ruining everything. We’ll talk about that tomorrow.
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.