Indexing SQL Server Queries For Performance: Windowing Functions
Thanks for watching!
Video Summary
In this video, I delve into improving the performance of windowing functions in SQL Server, focusing primarily on how effective indexing can be. I start by discussing my own experience with YouTube and how you can support my channel through a membership, emphasizing that all content is free but your engagement helps make it better. The bulk of the video then explores specific techniques for optimizing queries using windowing functions, particularly cross-apply and drive join methods. I explain in detail why inducing batch mode can significantly improve performance, especially on standard edition SQL Server, where DOP limitations can be a challenge. Throughout the discussion, I highlight common pitfalls such as mismatched indexes and the importance of attention to detail in index creation. By sharing these insights, my goal is to help you understand how to better leverage windowing functions for faster query execution.
Full Transcript
Erik Darling here with Erik Darling Data, just Darling Data. I think I took the Erik out for a reason. My name is naturally, since birth, this is not an affectation spelled with a K, and asking people to remember that is apparently difficult, even in the context of like, you know, like, my name is like, name being in an email right above them. It’s always like, hey Erik with a C, and I’m like, not a lot of attention to detail on your end, is there? Not a lot of giving a crap. So, uh, took the Erik out, kept the Darling Data. Darling is at least reasonably easy to spell as long as you’re not, uh, some, uh, AI powered, uh, transcription tool, which really loves calling the name of my, really loves calling my company Darlene Data. Like, Darlene, the, the, the, the, the daughter, the rebellious daughter from Roseanne, which, not a lot of attention to detail in the AI world, is there? Not a whole lot of that. Anyway, uh, today’s video, we’re going to talk about improving the performance of windowing functions in SQL Server, uh, mostly from the, um, the context of, uh, indexing. Uh, we’re gonna look at a couple different query forms, though. Uh, one, using cross-apply, the other using a drive join. So, there’s gonna be some interesting stuff in there, but before we talk about that, we need to talk about how Erik Darling derives value from YouTube, or from you, to, the, uh, if you would like to support my efforts to bring you, um, you know, all of this wonderful SQL Server content, you can click the link down in the video description to sign up for a channel membership. Uh, we’re up to, like, almost 40 people, or, I think, maybe 40 even.
I forget what the last number was, but, 40. Wow, 4-0. Just like my last birthday. Uh, uh, and, you can, you can, uh, support this channel for as little as $4 a month. Uh, all of this content is otherwise free, and if you would like to help me make this channel the wonderful, uh, you know, gigantic, thriving community that it should be, uh, liking, commenting, and subscribing are wonderful ways to also help me do that. Uh, if you, of course, see the things that I talk about, uh, in these videos, and think that, uh, some of them might just apply to your slow-ass SQL Server, you can hire me to do all sorts of consulting-y things. I will make your SQL Server faster in exchange for money. Uh, and, as always, and forever, my rates are reasonable. In the same reasonable vein, uh, is my, my consulting rates, uh, my, my training rates are also, uh, equally reasonable. Uh, if you would like to get all of my, uh, training for, uh, about 150 USD for the remainder of your, you know, life, uh, may, may, may you live forever, uh, you can do that at that URL up there using that discount code over there.
There is also a fully, uh, formed, uh, URL down in the video description if you are more keen on just clicking on one thing than clicking on a thing or typing a thing and typing another thing. I know how the attention to detail goes. Uh, SQL Saturday, 2025, coming your way, May 10th, uh, it will be at the Microsoft offices around Times Square.
So, not only can you get a slice of, slice of Sbarro pizza, you can also get your picture taken with, uh, Elmo wearing a dirty backpack or something. And, um, you know, uh, hopefully, hopefully not offend Elmo or, or dirty backpack Elmo or dirty backpack Spider-Man or, uh, any of the, the dirty backpack Sesame Street characters that, that, that, that, that, that roam around and, uh, will, I mean, for a reasonable rate, take a photo with you. So, uh, yeah, you, you should, you should do that.
Um, I, I would encourage you to do it. It’s, it’s a fun event. We, we don’t invite the dirty backpack Sesame Street characters into the event, so it’s usually pretty safe in there. But with that out of the way, let’s talk about windowing functions in SQL Server.
Now, uh, I am not going to sugarcoat this one bit as soon as Zoomit decides to become a responsive, responsive and responsible human being. Uh, most of the time when I’m working on a query that has a windowing function in it and it is having performance problems, my, the first thing I go to is to try to induce batch mode in some way. Uh, either by encourage, by, uh, using, uh, like, you know, just some sort of column story object in the database.
It doesn’t even have to have any rows in it. It can be a temp table if, you know, if you’re into that sort of thing. Uh, it can be encouraging a higher database compatibility level for the query, um, and maintaining the legacy cardinality estimator for a query.
But most of the time, windowing functions just work better under batch mode. They just do. Um, the window aggregate operator is highly optimized or something.
And it is just, like, you, you just take all the work out of it. You don’t really even have to, like, in most circumstances, you don’t even have to change the indexing because you’ve already got good enough performance just by getting batch mode out of the way. Uh, getting batch mode, uh, involved in the query and getting row mode out of the way.
Sorry, it’s the opposite there. Um, sorry, Zuma is being a real weirdo today. Uh, but, uh, if you’re on standard edition and Microsoft hates you because you did not pay the friendship tax, the enterprise friendship tax, then, uh, batch mode is not going to be as favorable of a proposition because you are limited to a DOP of two for any parallel queries that involve batch, that involve batch mode on standard edition. Uh, it’s a real unfortunate situation.
Um, I don’t understand quite why Microsoft needs to stick the thumb in the eye of standard edition users so badly. It’s almost like they’re daring you to go use Postgres. Uh, it’s a, it’s a real, real stinking shame how much accountants can ruin.
Perfectly good software. But there are lots of windowing functions out there in the world. There are, they generally fall into three categories.
There are ranking windowing functions like row number, rank, dense rank, and end tile. There are aggregate windowing functions like sum, average, count, big, min, and max. And then there are all sorts of analytic windowing functions like first value, last value, lead, and lag.
There are a whole bunch more, uh, in the boat, but I didn’t feel like listing them all out. So, um, anyway, uh, let’s pretend that I have a couple indexes, uh, on a couple of tables in the Stack Overflow database, namely the Post table and the Votes table.
And I’m using the bigger tables because I really want to drive performance crazy. Now, uh, we’re going to start with this query and those indexes. And this is the execution plan for that.
Um, it takes about 20 seconds, uh, and we use a loop join here. And on this side, the inner side of the loop join, we already have a good index on the Votes table to seek into. We can already get that done pretty quickly.
But what we don’t have is a good index to help with our windowing function otherwise. Now, inside here on the Votes table, what we’re cross-applying to is all this wonderful stuff, uh, where the, uh, Post ID column in the Votes table equals the ID column from the Post table, where VoteType ID is in these VoteTypes and where the creation date of the votes is greater than 2008.
So, uh, if we go back to the indexes a little bit on the Votes table, we do have this seekable index on Post ID, but none of the, um, none of the, uh, columns either from the other part of our where clause or from the specification of our windowing function are in the key of the index.
Now, if you’ve been paying attention to my other videos, even specifically the last video I recorded where I was like, hey, indexes put data in order, uh, you might have come across some stuff in your head like, oh, windowing functions put data in order too. Indexes can help windowing functions.
So, uh, let’s go and change the index that we have on the Votes table. But what I want you to pay attention to here is that even though we’ve put Post ID, VoteType ID, and CreationDate into the key of the index now, Post ID was there before, we added VoteType ID and CreationDate, and that should technically help with this.
It doesn’t go as far as we want because we messed up this part. Again, the whole attention to detail thing. This is the Eric with a C of index creation, and you’ll see, notice in here that we still, even though we can seek into everything we care about here, we still have to sort that data. If we hover over the sort, we will see that we have OrderBy, VoteTypeID ascending, and then CreationDate descending. So, our index really has to closely match the specification of our windowing function for it to work out. If we change our index definition, like we have in this window, to be on PostTypeID, VoteTypeID, and then CreationDate, things will work out a lot better for this query. Right? Now, we have, we still have our PlentySeekable index over here, and we do not, we no longer have a big spilling, expensive, well, not expensive, time-consuming spilling sort on the inner side of this nested loops joint. So, we’ve improved this query in a couple ways. Not necessarily from a timing perspective so much. We are a little bit faster than this query, and we are a little bit faster, well, actually, we’re about the same speed as this query, but this query over here will have asked for a memory grant of 4300 KB, and this query over here will have asked for a memory grant of 136 KB.
So, we did not improve the speed of this query so much, but we did improve some other, that metric, specifically the memory grant. Now, one thing that, you know, sometimes works, like trying to get batch mode involved, is to do like a windowing function thing in sort of an inner context, like in here, and then, you know, like select from that and generate the whole windowing function, and then apply some predicates outside of where you generate the windowing function. But in this case, this doesn’t get us anywhere. This doesn’t actually fix the query in any meaningful way.
So, one thing that you like, you might try, or rather the way that this, these queries like this tend to naturally get written is not with cross-apply, but with a join, right? And you can totally do that, and you can totally put a derived join into your query, and you can generate the whole row number in here, and you can join to your derived join on the outside of that. Where that differs from the cross-apply thing is with cross-apply, you can push that correlation inside of the cross-apply.
So, you don’t correlate the V thing to the post thing out here, you correlate the V thing to the post thing inside of the cross-apply. So, in this query, where I was trying to get that to change a little bit, was generating the row number in here, and then sort of applying the filter after that.
This didn’t quite work out for me. But the way most people write queries like this is just with a straight join, but of course you can’t put this thing in here, right? You can’t do that. This doesn’t work with just a straight join. You have to do the join and then say on out here.
So, in this case, we will have generated this whole result set, and then we will have filtered it to, then we’ll join the results of this thing to the post table outside of generating this result set, and then apply some filters to the stuff out here.
This does okay, but we’re back to a memory grant and we’re back to a spilling sort. Now, if we wanted to get this to be better, we would have to change the index that we have on the votes table to better suit writing this as a join rather than an apply. So, we would need something that helps the windowing function first, and then helps us join after we have developed that result for the windowing function. If we do this, this actually brings our query down to about 16 seconds. One of the big things with, there are two big things with this. One is the type of join changes, right? If we look at all of the windowing function, sorry, the windowing function versions of this, oops, that’s not the right one. We did change the join type for this one with where we did this, but we did not change the much else good about it. But for all of the cross applies, SQL Server has optimized that those cross applies as a nested loops join. This can be good or bad. This is not necessarily a death knell, but this is a significant amount of rows coming out of here. Generally, with apply, you want like a small result to like drive hitting a big result in here. So, if we were, if we had fewer rows coming out of the post table, this nested loops join may have been a bit more, a bit less painful. But the bigger deal with these joins is that when we just, rather with these queries, is that writing this is just a drive join. SQL Server opens up a little bit to other join types, which can be useful. So, in this one, so like this one, in the last couple of queries, we used a hash join. And the hash join is just kind of one big seek. And since we have for this particular query form, we’ve redone our index for vote type ID, creation date descending, and then post ID last. So, we can generate our whole row number and do all this stuff in here, and then join to the result of that out here. This helps SQL, like SQL Server does just one big seek, generates one big row number, and then filters that row number out down in here. Now, part of the way that I’ve engineered these queries is to not, is to have to fully do all the work inside of this, but filter out any work before we like do anything. So, really the, the, this to show you that, that inner part of the query where we do all of this stuff is where we need sort of the most help. Now, you don’t necessarily have to use row number or a windowing function in order to find the most, or like the highest number thing, or the most recent thing, or the least recent thing, or any of that stuff. You know, something that I try to get into everyone’s head when they’re writing queries is that, you know, there are, there are many ways to, many, many ways to write queries, and you can, you, and like, you don’t necessarily have to do the same thing over and over again. You should test different forms of different queries in order to make sure that you are using the fastest one available. Just for some situations like this, your options are, of course, windowing functions. You could use a top one with an order by, and you can use aggregate functions like min and max and whatever else. So, you don’t necessarily have to do windowing functions for all this. You could use top one, you could use min and max. You should test all of those things and see which one gives you the best performance. There might even be performance differences in different situations based on the indexing you have available for all these different query forms. So, let’s just take a look at this one real quick, where rather than generate a whole row number and do a whole bunch of filtering on row numbers, we just say, give me the max creation date for all this stuff, join to that out here, and now we’re going to make sure that batch mode stays involved in here by using this columnstore helper table. And now if you look at this query, it finishes in about three seconds. So, I’m all for windowing functions. I’m all for using them when they are useful and appropriate and when they, you know, help you write a query that gets you the right results.
But as soon as you start performance tuning things, you do need to keep in mind that there are other query forms that might be faster. Sometimes it might be the windowing function. Sometimes it might be top one with an order by or top whatever you need with an order by. Sometimes it might just be using a straight up max or min or max aggregate function, crunching your data down and then using it from there. So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I hope that you will take my advice about trying many different query forms seriously because most of the time when I’m tuning queries, I am, I mean, I am definitely looking at indexes, but I am also looking at how that query is written because if there are alternative syntaxes available that might help, might suit the indexes that I already have better, I want to try those before I go trying to mess around with stuff in the database. A lot of the databases that I work with are really big and creating indexes is pretty, is a, can be a pretty painful experience. So, you know, if I can avoid that, I will do that by getting other things involved, like different query formats, batch mode, blah, blah, blah, blah, blah. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I will see you in the next video about computed columns.
So, cool. Thank you. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.