A Little About Sorts In SQL Server Query Plans
Video Summary
In this video, I delve into the fascinating world of SQL Server’s cost-based optimizer and explore where it decides to stick sorts in query plans when you might not expect them. We discuss various reasons for these sorts, such as key lookups, stream aggregates, merge joins, distinct operations, and windowing functions. Each scenario highlights how SQL Server optimizes data retrieval but also introduces the challenge of memory grants, which can lead to a tug-of-war between buffer pool space and query execution efficiency. By understanding these intricacies, you’ll be better equipped to write more efficient queries and manage your database resources effectively.
Full Transcript
E-Darling here with Darling Data on sort of a sleepy Brooklyn Friday. And today we’re going to talk about places where SQL Server sticks sorts and query plans during cost-based optimization, even if you don’t necessarily ask for data in a particular order. Now, there are all sorts of reasons for this that we’ll talk about as we go through things. Some of them are requirements. from certain query plan operators like stream aggregates and merge joins. And the reason why we care about them is because sorts require memory. That memory is often called a query memory grant. And that query memory grant has to come from somewhere. So unless you have a perfectly honed, artisanally crafted set of hardware for your SQL Server, there’s going to be a bit of a tug of war between the buffer pool and query memory grants. in order to get enough memory for both of them to do what they got to do.
We’re going to talk more about memory grants in a separate video. There isn’t going to be a deep dive into that at all. This is just to kind of talk about where you might, why you might see a sort in a query plan when you didn’t say, order by some data. Now, before we do that, I want to talk about something related but not quite on topic just because there’s not enough to talk about with the other thing. to justify an entire video. And that is when you’re ordering data. Now, there’s no such thing as guaranteed ordering from a query unless you order by a column. And not unless you have a unique column in the mix to order by. Now, by that I mean, if we look at the results of this query, you can see that the first line up there is the only other options I see, blah, blah, blah, some other stuff that probably sounds really smart.
If you read the, if you were to read the whole thing. But if we look at the execution plan, we have a parallel execution plan for this query. Alright, takes about two seconds to finish running. And if we rerun this, we’re just ordering by this score column right now. If we’re just ordering by this score column, there are a lot of duplicate values in this score column. So if we run this query again, we get the same result back that time. But if we keep going and say, ah, what sorry, regex engine doesn’t support plus?
It’s a fair question. And if we just keep running this, well, we’re back to the only other options they see being something. And now we have to clarify the cigar API does something else. And oh, well, we got the cigar API again. And let’s see if we get lucky on this last run. Nope, we got the cigar API again. So we ran this a few different several times, we got a few different results back in a few different orders. Now, you might be tempted to stick a max stop one hint on your queries thinking that you’ll get reliable results back that way. The thing is that you won’t.
That they’re not guaranteed in this one either. And on top of them not being guaranteed, you also can end up with a much slower query. That last query that we were running took about two seconds to finish. This one is just about at the seven second mark to complete. And also, you still don’t get the guaranteed ordering from this. So it’s probably a little bit more rare that you get mixed results back just because you have a single thread working rather than dot threads working on the query. And the, you know, just various timing differences will have more effect on multiple CPU threads than on a single CPU thread.
But you can also end up with a query that takes a lot longer. The only way to get consistent results back is to also order by a unique column. Most, well, actually, all of the tables in the Stack Overflow database have a clustered primary key on a column called ID. It’s also an identity column. It’s a pretty popular choice for a clustered primary key these days. But this is the only way to get back truly guaranteed results. And if we look at this, we also get different results back than we did just ordering by that score column. So we need to think very carefully about how we’re ordering things and what we’re actually looking for when we’re dealing with data from a column that is not completely distinct across.
Now, to illustrate the sorting stuff from some of these other queries, I’ve created an index on here. And I did that ahead of time, you can tell, because IntelliSense has inserted this really helpful Excite Byte track under the index name. So lucky for us, we know that that index already exists, we don’t haul off and try to recreate that index, do something foolish. But one place where you might see SQL Server decide to stick a sort in your query plan for what might appear to be no reason whatsoever, is to do a key lookup. So in this query plan, we have this big thick line of stuff coming out of our clustered index. And then you can see a key lookup. And SQL Server has decided to sort that data prior to doing a key lookup via a nested loops join back to the clustered index on this table. I recorded a video about key lookups recently. So if you need to refresh your memory, go look at the list of videos in this channel and you’ll see that one in there.
Now what are we ordering by? Well, since key lookups are essentially a join between a nonclustered index and the clustered index on the table, when we go and touch that clustered index, we need to go get the ID column, which is again the clustered primary key. All right. All on board there. And because we don’t have that column in order in our index, well, we sort of do, but we also have an inequality predicate on the creation date column. So we don’t, we’re going to cross all sorts of boundaries there. And our data is not going to be in a helpful order leaving the index seat with the ID column.
And so SQL Server sticks an order by the ID column into our query plan in order to optimize IO when we go and do our lookup back to the clustered index when we have to find all those ID columns. Now the next two are single query plan operators that expect sorted data. First one is a stream aggregate. So if we look at this query plan, we have a sort and then we have a stream aggregate operator. That’s because a stream aggregate operator requires sorted data coming in.
If this were a hash aggregate, we wouldn’t have that requirement. We could just bring data in however we wanted. But here we are with the stream aggregate and needing to sort our data. Merge join is another one. Now I’m just joining the post table to itself on this owner user ID column.
But since I don’t have an index on the owner ID, owner user ID column to support that merge join, I have two sort operators in here going into the merge join here. I just want to take a quick moment to say that parallel merge joins are one of the most offensive things that you can ever see in a query plan.
If you see a parallel merge join in your execution plan, you should question the foundations of your existence. Or just fix it. I don’t know. You can do both. I find that questioning the foundations of your existence is often a pretty good thing to do.
You can answer a lot of questions about yourself that way. Now another place where we might see a sort operator get stuck in our query plan is if we ask for a distinct set of data. So if we run this, or we just get the estimated execution plan for this, we will see a distinct sort down here.
This is how SQL Server has chosen to figure out a unique set of data for this query is via the distinct sort operator. Now you’ll notice that this query and the one below it, both are using a semi-undocumented hint called disallow batch mode. We just get a different query plan with batch mode involved.
If we run this query without the disallowed batch mode hint, we get a hash aggregate operator. That hash aggregate doesn’t expect sorted data, and so we don’t get a distinct sort. And you can see that this does happen in batch mode, and you know, wouldn’t that just ruin the whole demo?
That’s that old CAD batch mode. Now another place where you might see SQL Server stick a sort in your query plan is when you use a windowing function. Now every windowing function is going to have some kind of order by in it.
Unless you do like a count over or something that doesn’t really require, have an order by requirement in there, but a lot of them do. But the point here is more that SQL Server needs to order not only by the order by column, but also the partition by column. That’s also something that SQL Server will need to sort if you don’t have an index that handles that sorting for you.
Now again, we are disallowing batch mode here just to get a couple more query operators in the plan. Now just so you have a sense of what changes, under batch mode we still have a sort. That doesn’t change.
We still sort data here, but now we just have a window aggregate here. Right? So this window aggregate doesn’t really tell us all of the stuff that we would see, that we would like to see from the row mode execution plan. But the sort operator is still going to be, oh, let’s come back to that.
No, let’s get that tooltip back. There we are. That’s what we wanted. This order by is still going to be on owner user ID and creation date in ascending order. But if we run this query again without batch mode involved, we disallow batch mode, we are still going to have a sort operator.
All right. Come on, come on, come on, bring it on back. Zoom it quit early on me.
Started drinking. We still have the order by on owner user ID and creation date. But now we have a couple additional things here. Instead of having that single window aggregate operator, now we have these three things. And these three things are all happening in a serial zone.
Right? This parallelism gather streams here gathered all our data up, stuck it into a serial zone, a single threaded zone in the execution plan. And now we have to just cope with that somehow.
Anyway, that’s not really the point. The point is that we have, I mean, we have the initial sort on owner user ID and creation date. We already looked at that. But now we have this segment operator.
And this segment operator first does a group by on owner user ID. All right. That’s our partition by element. And then we have another segment over here. And that tool tip just did a good job of disappearing on me.
Come on, pal. You can do it. I believe in you. You’re the best SSMS and the best SSMS that has ever existed. We also have a group by on owner user ID and creation date.
And then our sequence project, which, again, sort of like the window aggregate, not really a helpful set of details there. So, again, these are places where SQL Server’s wonderful cost-based optimizer might decide that your query plan needs to sort data for various reasons, key lookups, operators that expect it, like stream aggregates and merge joins, or because you don’t have an index that supports the ordering that you’re asking for.
So these are all reasons why SQL Server will need to sort data. And, again, we care about sorts because sorts require memory grants. And memory grants can steal data from the buffer pool.
And the more data that we steal from the buffer pool, the less buffer pool space we have. Then we have to retake this back into it. Then we have to touch disk. And it’s all just a horrible, horrible cycle. Gas station sushi.
Just round and round we go. So easy in, easy come, easy go, I guess. Anyway, I’m going to go, I don’t know what I’m going to go do, actually. I guess I’m going to go work on that video about memory grants so I can record that one.
Anyway, thanks for watching. I hope you learned something. I hope you enjoyed yourselves. I hope you have a great weekend since it’s Friday here. I guess that’s a little bit non-deterministic.
Just like ordering by a non-unique column is a bit non-deterministic what day it is when you’ll see this. So happy whatever day it is to you. All right.
Thank you for watching. Thank you. Thank you. Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.