Performance Tuning TOP PERCENT Queries In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into performance tuning top and percent queries—a topic that might not come up every day but is crucial when it does. We explore how to make these queries run much faster by using Common Table Expressions (CTEs) or derived tables instead of parameterized queries. By doing so, we eliminate the need for sorts in parallel plans, significantly reducing execution time. I walk through a stored procedure example where I demonstrate this technique step-by-step, showing how it can drastically improve performance without complicating your code too much. Additionally, I share some insights on upcoming events like Data Saturday Dallas and Past Data Summit, where you might catch me live. If you’re interested in learning more about SQL Server performance tuning or need help with health checks, performance analysis, or training, feel free to reach out; my channel is here for you.
Full Transcript
Erik Darling here with Darling Data. Look at that handsome logo. Look at that handsome head. Look at all that handsome floating around in here. We’re going to try something a little bit different in these videos because I did notice that when I told people to do stuff in the beginning of the video, they did stuff. So we’re going to start off with my appeals. at the beginning of the videos rather than just at the very end of the videos where most people have stopped watching because they’ve got what they needed from me. And usually those transactions don’t end quite as well. And also, it’s my channel and I can do what I want. When you get your channel, you can do what you want. In today’s video, we’re going to be talking about performance tuning top and percent queries, which is something you might not have to do very often in your career. But when you do have to do it, it can be quite an excruciating process. And we’re going to talk about how you can make it much easier on yourself. So about my channel, all of this content is free. And I’d like to keep it that way without commercials and other crap. So thank you, Intel for chiming in about the driver timeout. Great. Intel’s not doing so great lately. Their CEO was was praying on Twitter the other day as stocks are melting.
So we’re going to leave Intel be for a little bit. But yeah, all of this content is free. I like likes, I like comments, and I like subscribers. It’s a good way for me to know if I’m doing a good job or not. If you feel like supporting my channel, there are like low cost memberships where it’s like a few bucks a month if you just feel like saying thank you. You don’t have to, but if you’re feeling generous, go for it. I haven’t really promoted this much because I don’t really know what I want to do with it just yet. I think that ideally I would like to turn it into some more sort of like private one-on-one, well, maybe not one-on-one, but like private group stuff for people who donate at higher levels. We’ll see where it goes. I haven’t quite fleshed it all out yet, and I don’t know when I’m going to. So we’ll get to that. We’ll cross that bridge when we come to it. Hopefully, hopefully, and hopefully in a nice car. I’m a consultant. I do SQL Server performance tuning. If you need health checks, performance analysis, hands-on tuning, you have a SQL Server emergency, or you want to get your developers trained so that they don’t stink at SQL Server anymore, you can hire me. I do those things for money. That’s a good transaction for most people. I also sell training. If you go to training.erikdarling.com, there’s also a link in the show notes for this stuff.
You can get over 24 hours of streaming SQL Server performance tuning content at the beginner, intermediate, and advanced levels. And if you pay enough attention to this video or click the link in the show notes, you can get 75% off everything with the discount code SPRINGCLEANING. As far as upcoming events goes, where you can catch me live and in person, Friday, September 6th of this current year, 2024, I will be at Data Saturday Dallas. I have a pre-con on the Friday, and then I’m speaking at the regular event on Saturday. And of course, November 4th and 5th, I will be at Past Data Summit. This slide has a lot of white space on it, and I haven’t quite figured out how I want to fill that yet. It’s also possible that after September 6th, this slide will have a lot more white space on it, because I will be purely focused on being prepared for Past Data Summit.
So if you feel like seeing me in the flesh, as they say, those are two good ways to catch me there. And now we have gone black, which is a lot of the opposite problem of white space. And we’re going to move on and cover the subject of our video.
And well, let me actually close this. The query plans, we’re going to get to the query plans. But I have a couple indexes on the post table. I have one that supports what we’re searching for and what we’re ordering by.
And I have one that just supports what we’re searching for, because I want to show you two sort of different query plans related to that. Now, here’s the store procedure that I have that is looking for the top percent, top whatever percent we plug in rows for a specific post ID. You know, this probably is not, again, this is not the most common routine for returning rows out to a client.
But I do see a lot of people use it just because, you know, sometimes, you know, it becomes kind of wonky if you’re like, yeah, give me the top thousand rows, but a thousand rows don’t exist. So some people prefer to return a non-hard-coded number or a non-default number. They want to return a number of rows based on the population of data that they’ve got.
So, you know, it’s a somewhat, you know, somewhat less common thing to do, but it can also be a little bit less confusing when you’re like, yeah, give me the top thousand rows. But again, the thousand rows don’t come back. So I’ve got this query set up in a few different ways.
Four to be precise. Four. We’ll count them. Number one, just a regular top end percent query to start things off right here, where we’re just doing what we’re doing with no interference. And then below it, I have a query hinted to use the index that supports both the searching and the sorting here.
Then the third query is hinted to use the index that just supports the searching. And then finally, I have a query at the bottom where this is all expressed without any parameters. This is expressed with literal values, because what I wanted to show you is that this is not an effect of the top percent being parameterized.
This is just what top percent query plans tend to look like. Below this, I’m going to show you a way to make these go way faster. Okay.
So coming back to the query plans, I think potentially my new least favorite trail of query plan operators is a sort, which of course is parallel, because right next to it, we have a gather streams. And then that goes into an eager table spool. And the reason why I pre-ran all of these is you can probably guess by looking at some of the numbers here.
These are not the fastest boys in the world. No, these are very slow boys. This one takes nearly a full minute.
This one down here takes 30 seconds, which is an improvement by nearly half. But still not great. Still not what I would call fast.
And then, you know, down here, this is… I think the reason why I wanted to have the parallel… Rather, I wanted to have an index where both search and ordering is supported and where only search is supported is because I think the query plan difference here is interesting.
And the reason why I think the query plan difference here is interesting is, of course, because when you have a nested loops join, SQL Server only considers the stuff over here for, you know, how much faster it would be to execute the nested loops in parallel. Because parallel nested loops don’t operate the way…
Don’t always operate cooperatively the way that, like, parallel merge or hash joins do. Parallel merge or hash joins, you’re probably very used to seeing those where on the inner side of the join, the number of rows in the table is split up equally or split up hopefully as close to equally as possible amongst the dot threads in the query.
With a parallel nested loops join, you’re really running dot copies of the nested loops on the inner side. So every thread is going to have every row on it. There are a couple caveats to that that are too involved to cover here.
But just in general, when you see parallel nested loops, you should know that you’re dealing probably with a lot more rows than you think you are per thread. So this one is interesting because this query where we have the search and the sorting supported does not get a parallel plan. This gets a serial plan which runs for about 30 seconds, which again is twice as fast as any parallel plan that we have in here.
The one up top, fully parallel plan. Granted, we scanned the clustered index. We have kind of a nasty sort here.
But even going from the sort to the gather streams, that goes from 18 seconds, jumps up to 35 seconds. And then going into the eager table spool, that jumps up to 58 seconds. So really awful sort of like chunks of time spent waiting for those operators to process rows around.
The serial plan where we lose the sort, things do improve, but not, I mean, they improve and they improve dramatically. They improve their, this query is twice as fast as the other one, but it’s still slow as hell. Right.
And a lot of the time, you know, we have a full 18 seconds getting into there. And then, you know, this seek to key lookup thing is not a great scenario. We spend about almost 11 seconds just in these, these three operators alone. So that’s, that’s also unfortunate.
But what I thought what was really interesting was when we flip back to the parallel plan with this sort in it, we go from about three seconds here. This jumps up to 11 seconds here. So we spend, you know, just about eight seconds, you know, in the sort.
And then from there, it’s another like 15 seconds in the gather streams. And then a whole bunch of time in the eager index. So this, that series of operators is just real unfortunate in these plans.
And then, of course, the one way down at the bottom is, this is just the one with the literal values, which is pretty much an exact duplicate of the one up at top of the parameter. It’s a few seconds faster.
It’s about seven seconds faster, I think, for, you know, I don’t know, whatever reason. I didn’t really dig into why this one’s seven seconds faster. It’s not really pertinent. But in the moment, mainly what I wanted to show you here was just that you get the same plan shape with using literal values as parameterized values. So one way to tune these queries and to get much faster performance out of them is to use, you can use either a CTE or a derived table, whatever you want to do.
So I use the CTE here because I like to, you know, I like to give equal opportunity to queries where it doesn’t make a difference. And inside of the CTE, what I’m doing is I’m calculating the percentage using the top parameter and multiplying that by the count of records that we get from when we look at that. And then you divide that by 100 to get the percentage that you’re looking for, right?
So this piece of math right here will get us the top percent that we care about. And then what I’m doing down here is I am saying I am selecting. So that PCT table where I get the percent, that’s a pretty short hand abbreviation for percent.
I’m cross applying to the post table and I’m getting the top calculation from up from up here. So this, this records column, this is the one that this is the percentage. This is the number of rows that we need to get as that percentage.
And I’m passing that percentage in here. And I’m doing the same thing in here with the where clause. And I’m doing the same thing here with the ordering. And then one thing that, you know, you always need to be thinking about is that SQL Server does not guarantee ordering unless you tell it what to order by. So we’re even so just getting the select top percent in here ordered by creation date descending is not enough to guarantee that the external provided results, the presentation, the presented results will also be ordered correctly.
So we have another order by out here to take care of that. And the nice thing is that when we create this store procedure or rather when we alter that store procedure to use the new one, and I’m going to execute this the exact same way. Top one, post type ID equals one.
I’m going to execute this the exact same way. And this is going to be a lot faster. You’ll notice that, I mean, a lot of the time was spent returning the 60,000 rows out. The query itself actually finishes in about half a second.
So it’s very, very easy and convenient for us to seek to the rows we care about in here to aggregate, you know, do our count to get generate a number. And then inside the top here to get that 60,003 rows, which is the top 1%. All sounds great.
Everything’s good here. Where this pattern will generally apply well to most executions. As the percentage gets higher for a lot of rows, performance is going to suffer.
So if we crank this up to, let’s say, the top 20%, I mean, on top of the fact that, you know, we’re going to spend more time sending rows out to SSMS because, you know, 20% of 6 million is a, you know, fairly high number. Like, this is going to slow things down. But most of the slowdown is returning the results to SQL Server Management Studio.
You know, of course, I could dump it into a temp table and, you know, you know, return, maybe ignore it or return the results from there. But a lot of the time in here is just time spent returning the results. If you look at the actual query itself, the actual query itself finished executing in about three seconds flat.
We spent a whole bunch of extra time returning 1.2 million rows out. Now, there is a slight downside to this in that the top is always going to estimate 100 rows. But as long as your indexes are set up to support the seeking and ordering that you care about for the top end percent, that’s not going to hurt you too much.
Where it would hurt you is if you didn’t have the sorting element assigned in the index and you had a sort operator that had very variable, like, memory grant requirements. You know, obviously sorting 60,000 rows is a lot different than sorting 1.2 million rows as far as how much memory you’d need. Maybe if you’d want a parallel plan for that, stuff, things along those lines.
But generally, this pattern works out way better, like, up until a very, very high row count. And even then, so, like, let’s be a little bit honest about, like, high row count return queries. This thing dumped out 1.2 million rows, right?
That’s 20% of the 6 million something rows that have a post type ID of 1 in the post table. Cool. If you are returning 1.2 million rows to anybody with the exception of, like, exporting to a Excel file or some other kind of file format, ain’t no one looking at 1.2 million rows.
No one is going to go through all 1.2 million rows and do something with them aside from, like, copy and paste them to another thing to make those results more portable. Most likely an Excel file. Most, you know, if you dump out 1.2 million rows to an end user, they’re not going to do anything with 1.2 million rows in your application results, most likely.
They’re going to take those 1.2 million rows, paste them into Excel, do whatever, you know, goofy Excel stuff people do in Excel, and then, you know, use that for whatever they’re building, right? Whether it’s a chart, graph, something, VLOOKUP, I don’t know. Whatever people do in Excel.
It’s crazy. So, returning 1.2 million rows out to SQL Server Management Studio or an application is generally not something. Like, if you’re at the point where you’re doing that, I have questions that you can pay me to ask you about just what the hell you’re doing or what the hell an end user would be doing with that much data getting returned to them. Because most of the time, they’re not going to get to the end of 1.2 million results and be like, hmm, I’m satisfied now.
They’re just not. That’s generally not the way most human brains work, especially in this day and age where everyone is in therapy and medicated and has some sort of neurodivergency that prevents them from paying attention to 1.2 million rows of anything. Right?
Or that’s like, maybe that’s just basic human sanity. Like, 1.2 million rows? I’m not looking at all that. You would forget everything you saw. Right?
There’s generally no point to that. So, you know, I think for most people doing top and percent things, you’re going to be pretty safe with this setup because you will hopefully never be returning, you know, millions and millions of rows, especially to SQL Server Management Studio, which has a notoriously difficult time of ingesting, displaying, formatting, all that stuff quickly. It’s not a fast boy for that.
So, anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. I hope that you paid attention earlier in the video where I talked about liking and subscribing and hiring me and buying training and all the other stuff. Because, you know, I like to have friends when I record these and I don’t like to feel lonely.
So, anyway, thank you for watching. I’m going to prepare one of these other demos. You see I have many tabs open up at the top there.
I’m going to go prepare one of those to record. And then I’m going to change one slide in the deck and I’m going to do that all again. So, anyway, it’s my channel. Remember?
Do what I want. Okay, cool. Thank you.
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.