Learn T-SQL With Erik: Aligning Queries and Indexes Part 5
Chapters
- 00:00:00 – Introduction
- 00:02:15 – Actual Execution Plans
- 00:04:56 – Parallel Plan Analysis
- 00:07:09 – Memory Grant Reduction
- 00:08:41 – Query Rewriting Benefits
- 00:13:09 – Logical Units in Queries
- 00:14:20 – Parallelism Trade-offs
Full Transcript
Erik Darling here, with Darling Data, the best SQL Server consultancy outside of New Zealand, lest we forget, lest we forget the reasonable rates that Darling Data offers in exchange for making your SQL Server faster. Fair trade, believe in that. I think it’s a fair trade. Thankfully a lot of you do too, so that’s nice. But today we are going to get back to learning T-SQL, aligning queries and indexes, because these are good things to do, right?
Make queries faster, make your queries and your indexes line up better so that things happen for you that are good and fast and performance. You’ll get there someday. Anyway, if you want to purchase the full training, remember these are just little dribs and drabs and tidbits of the brilliance of the full course. There’s a link down below where you can purchase the course for a hundred bucks off.
It’s a nice coupon, right? Because I care about you and your well-being, your mental sanity and whatnot. You might have to learn this stuff so that you stop getting outwitted by the robots, right? You get smart enough to challenge the robots when they’re full of malarkey.
You can also hire me for consulting. If you’re interested, I can help you out. If you’re just not down with the robots and you just don’t have time to learn all this stuff on your own, I have crammed my head full of this stuff for like 20 years now.
So I’ve got a lot of things in there and I can do a lot of stuff pretty quickly, right? So that’s cool too. If you’d like to support this channel with money, like four bucks a month, you can do that.
Again, link down below, somewhere down there. I forget. It’s like a subscriber member button thing. If you want to ask me office hours questions.
Like I answered in yesterday’s video. At least I think it was yesterday. It’s hard to tell these days. Everything blurs and blends together. Or if you don’t want to do any of that stuff, maybe you could find it in your heart, your withered, sad, grinch of a heart, to like, subscribe, and tell a friend.
I wasn’t wishing like coronary failure on you. I just want you to like making some jokes. If you want free, this is how much I want you to like me.
This is how desperate I am for friendship and companion. If you want the free SQL Server monitoring, you can get it from your best friend, Erik Darling. You go to the GitHub.
The link is down there. You download it. You point it at SQL Server. It tells you everything you could possibly want to know about the performance of your SQL Server. It is fantastic.
It is a great time. And again, it’s free, right? You don’t need to talk to me. You don’t need to email me. It’s just there.
I will be, well, this week, yeah, in like a few days, magic, at Data Saturday Croatia, where I will be doing my advanced T-SQL pre-con. If they haven’t stopped selling tickets, you should buy a ticket, if not to my pre-con, then maybe to the Data Saturday event.
Maybe we could hang out there, and maybe we’ll be best friends in real life, too. Who knows? We don’t just have to be best friends on YouTube. And then, of course, Past Data Summit in Seattle, Washington, November 9th to 11th, which seems an impossibly far way off, but so far as I can tell, it keeps getting closer.
So that’s cool. But now it is June. We will suffer.
We will suffer summer. That’s also a good band name. Maybe not now. The reason why I’m not a professional band namer, I guess. Anyway, we have this index currently.
It is on the post table, on the columns, owner, user ID, and then creation date. And we’re going to write a query. First, we’re going to turn on actual execution plans because these are important things to have on in life.
When we’re attempting to talk about SQL Server performance, if anyone ever tries to talk to you about performance without turning on query plans, I would be very suspicious of them.
Right? That is a strange thing to do. But this is probably a reasonable way for most people to write a query. When we look at the execution plan, we’ll notice a few things.
One, we got a parallel plan. This thing went parallel, ran at DOP 8. It spilled a wee little bit on this sort.
We went from 72 milliseconds up to 119 milliseconds. 8, 20, 30, 40-ish milliseconds of time in the sort plus the spill. Not the end of the world.
This finishes in 120 milliseconds total. Is this good? Is this bad? Is this ugly? Well, I mean, to me, it depends a bit on a few things, not PowerShell. It does not depend on that.
But it depends on a few things. Right? We get a 406 megabyte memory grant, which in and of itself is not terrible. It’s not a big deal.
But we asked for 400 megs and we still spilled. Maybe I’m not in love with that. And this goes parallel to DOP 8. So in order for this query that returns 1,000 rows, SQL Server’s best idea for executing this was a parallel DOP 8 query with 400 megs of memory behind it.
I have two problems with that. One, it’s not that fast for 1,000 rows. And two, why do we need memory for this at all?
We have an index that has all the data that we care about in order. It’s on owner user ID and creation date descending. And we are looking for owner user IDs.
We are seeking the three owner user IDs and ordering by creation date descending. Why in God’s name does SQL Server need to sort this data? It is sorted for us. Cracky.
One kind of fancy way to rewrite this query is rather than an IN clause, we could put those values that we care about into a VALUES clause.
We could value our VALUES clause. And then we could take those values and we could cross-apply out and we could correlate to them. Remember our values are aliased up here as U and the column that we’re naming these values is ID.
So U.ID is really just those three values. And if we run this, we will get a query plan that is no longer parallel.
Right? We can see all of the visual indicators of parallelism have disappeared from this plan. And it has gotten faster. We went from 120 milliseconds down to 33 milliseconds.
But notice that we still, SQL Server is still like, ah, now we got to sort that data. That seems silly to me. A bit. And that sort takes, I don’t know, whatever 33 minus 9 is.
Right? Oh. Now we did okay here. We improved things. But, SQL Server should have better ideas for these things.
One way that we can rewrite this query, to again, give SQL Server a different way of thinking about this, would be to do this.
And we could say, where the owner user ID is in select union, select union, select. Right?
And if we run this query, remember that, we went from 120 milliseconds to 33 milliseconds. We still didn’t get rid of that sort. And before I run this maybe, we could just look at how much memory this thing takes.
1,024 KB of memory. So we went down from 400 megs to 1,024 KB. That’s pretty good. Right? And so, not only do we have like, you know, this query got, I don’t know, like 60 something percent faster.
But we still have this sort of, the memory grant went way down. Like granted, the memory grant, way down. So good job there. But, if you write the query like this, we get a very interesting execution plan.
If you’re sitting at home thinking, gosh, that returned 1,000 rows very quickly, you would be right. So we went from 33 milliseconds down to 3 milliseconds. And we get a very interesting execution plan.
Where for each of those union values, we now have one, two, three constant scans. And they each produce a singular seek through our indexes.
And we have this merge join. So, normally, I would, I crap all over merge joins. But remember, from the Office Hours video, I actually, there was, one of my, one of the things I said, is that sometimes SQL Server will choose merge in order to satisfy the ordering requirements of downstream operations.
And it has, my goodness. It’s almost like I know what I’m talking about sometimes. Right? So we don’t have a single sort in this query plan.
This query plan is still single threaded. We have no visual indicators that parallelism has. That parallelism has occurred here. If we look in the tooltip for the select, there is no memory grant anymore.
The memory grant is gone. And our degree of parallelism is one. So our query went from a 120 millisecond, 400 meg, DOP8 parallel plan at 120 milliseconds to a single threaded plan with a sort still at 33 milliseconds.
The sort, the memory grant went way down. So like, we’re still cool there, right? It spilled a little bit, but you know, it was still, still pretty decent to a query plan that now has no sorts, no memory grant, still run single threaded, and is now down to three milliseconds.
That is a pretty good deal. This is an admittedly funny rewrite. I get it. I maybe, you know, wouldn’t suggest you rewrite all of your in clauses to do this, but you know, sometimes it’s, you know, jibba jab there.
One thing that I think is missing from the optimizer is just the ability for it to spot these sort of patterns and apply these sort of patterns to our query plans. Because really, it would be identical to writing this query, right?
If we said select the top 1000, U dot star, and we could, I mean, the P dot stars in here don’t mean anything because we’re selecting U dot star out here, right?
So like we could, we could do this, right? And say, look at that. The single server comes up with the same basic execution plan with the, except we don’t have constant scans in these, right?
The plan is simplified a little bit. It still takes three milliseconds. There’s still not a single sort to be seen. And, and we’re still, no memory grant, dot one. So this is, but this is just one of the things that the optimizer is sort of not good at sort of spotting and working out.
It goes for like, like almost like the lowest common denominator plan sometimes. And that’s, it’s a little aggravating. We could also rewrite this query.
This is admittedly much, much more verbose, but it, it gives us the same basic execution plan that we’ve seen in the, the, the other rewrites that have given us this plan shape with the merge joint concatenations, the three separate index seeks and the no sort, the no parallelism, and again, finishing in three milliseconds.
So a lot of the, the query performance tuning that you see, performance tuning that you might have to do in your life is recognizing, recognize patterns that the optimizer is perhaps bad at dealing with, uh, and writing your queries in ways that make the optimizer not really have a choice in how they’re going to deal with those things because you have taken control and rewritten your queries in ways that the optimizer cannot ignore.
A lot of that stuff is going to come down to breaking your query up and, and, and sometimes, you know, uh, like obviously temp tables are a way to break a query up, but sometimes breaking your query up into, uh, even like logical units within the query itself is a pretty smart thing to do.
Coming back to the way that this query was first written, right? If we run this, we look at the query plan, we look at the seek, right?
We have three, three separate seek keys in here. This is a multi-seek or a dynamic seek, and this is something that I’ve talked about before, but it essentially takes away SQL Server’s ability to preserve the order of things, uh, as it, as it goes through because we’re, we’re, we’re not just doing one seek into the index and finding one range of data.
We’re doing three separate seeks into it and we don’t, it doesn’t maintain the order when, when we do that, which is why we needed to sort out here. So even getting rid of the, the spill on this sort, we are still at 107 milliseconds.
We still have the parallel plan. Now, I don’t want to crap on parallel plans because I use parallelism a lot. Um, all the time to make queries faster. It doesn’t make every query faster though.
This is unfortunate. Sometimes it, it does, it does have overhead and, uh, that overhead does, uh, you know, the, the, the, the assembling and reassembling of rows across exchanges and buffers and, you know, sending worker threads out and having them all work and do stuff.
It can’t, it doesn’t have overhead, but that overhead is, it amortizes itself in larger queries where parallelism is a more practical, um, is a more practical element.
And, and it makes the query faster because you’re spreading lots and lots of rows out across parallel threads. In a lot of cases, when SQL Server chooses a parallel plan, it’s just not getting so many rows that spreading them out across multiple CPUs is a really good strategy.
So we end up with sometimes when a query running at max.1 can be a lot faster for the small amount of rows than a parallel query, right?
There are times when that absolutely happens like the, the one I just showed you, right? But this thing really does, the way that this query is originally written, the two problems with it are one, the in clause doesn’t allow us to, with the multi-seq does not allow us to preserve the order of the, the nonclustered index that we have, whereas other query forms do.
Anyway, few different interesting ways to rewrite a query with an in clause. You thought, you thought it was just so, so simple. Uh, the, the optimizer is not good at sort of unraveling and being able to, uh, reason better query plans for, uh, and that I think you have just learned how to do.
And, uh, one, one thing that it probably is worth saying here is, uh, these are not query tuning strategies that our, our robot friends often come up with.
And, uh, the reason for that is because our robot friends, well, well, they are terribly, terribly logical and they, they exceed at, I think mostly at, at logical situations.
Uh, they, they are not clever. They are not clever like you and I. So, we still have hope out there. On top of the fact that, um, they’re, they’re getting very expensive and companies are like, wait a minute, now that this costs money, I don’t think I like it.
Uh, anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you next week on Tuesday for office hours. You’re great. And I hope your weekend is too.
All right. 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.