Learn T-SQL With Erik: Aligning Queries and Indexes Part 1

Posted on by Erik Darling

Learn T-SQL With Erik: Aligning Queries and Indexes Part 1

Chapters

Full Transcript

Erik Darling here with Darling Data and today’s video we are going to continue our learning T-SQL journey here and we are going to spend we’re gonna spend a few videos talking about how you can align your queries and your indexes for better performance some videos will change indexes some videos will change queries but at the end of the day we will have a fucking good time I think and we’ll all learn a lot and I’ll convince all of you to buy the full Learn T-SQL with Erik course that’s the plan anyway right I’m just gonna fight you off in droves all the all the sales we’re gonna make here down in the video description is where you can find a link to purchase the very course that this this material is a very small portion of a very tiny little itty-bitty part of the world you can also find other really helpful links in there to help you achieve all of your goals and dreams in life you can hire me for consulting you can buy this training and other training too I’ve got other stuff if you don’t like you know you don’t like these more whatever you can also become a supporting member of the channel if you would if you would feel that generous and you would like to share four dollars a month with me you can ask me office hours questions that is absolutely free every Tuesday I answer five of them from whoever sends him in and of course I do appreciate this channel’s stunning massive unprecedented growth someday I’m gonna have twice as many members as that amiga repair channel I think but if you would be so kind as to Like subscribe and of course tell a friend about the wonders and glories that take place here on the darling data channel that would be cool too I’m If you’re in the market for something free, and that free something happens to be a SQL Server performance monitoring tool, I’ve got one. Isn’t that crazy?

Totally free, totally open source, no email to sign up, no phoning home with any weird data about what it’s doing, what’s going on in your servers. It’s just a bunch of T-SQL collectors that run, grab the right stuff, all this important stuff for monitoring SQL Server, all this stuff that I care about and dig deeply into when I’m doing a performance analysis on a SQL Server.

And it’s got built-in MCP tooling as well. So if you choose to enable the MCP servers and the MCP tooling, you can have your robot companion friends look at your monitoring data, and just your monitoring data, in order to see how things, how things look, and give you some analysis of what has been collected.

Coming up in the very near future, I will be at Data Saturday Croatia, June 12th and 13th. And then, unless something magnificent happens, I will be home making these videos for the masses until November 9th through 11th, when I will be at PASS Data Community Summit in Seattle, Washington.

But with that out of the way, let us continue our romp through the month, the month of May here, and let’s talk about how we can change queries to better fit the indexes that we have in place.

And how that will, of course, make them go faster. Ah, crazy! Look at that. You can do all sorts of fun stuff. So these are the two indexes that we currently have.

This index is less important. The index that we are going to focus on aligning our query better with is this index right here. It’s on the left side.

on the post table and it is keyed on owner user ID, post type ID, and then the score column in descending order. So we can pretend that these indexes exist for any of the number, any variety of reasons that indexes often exist in databases.

Perhaps they were there for some other query and a new query that we wrote just happened to be picking up on them. So what we’re gonna do first is run this query. And the part of this query that we wanna focus on is kind of what’s going on here first.

So this is a derived join. And one of the limitations of derived joins is that they can’t, they are not aware of anything going on outside of them.

Just as an example, if I tried to tell this thing anything about the users table, like if I tried to say p.ownerUserId equals u.id, SQL Server rather, we would get this little squiggle over here and this thing would say, I have no idea where this ID column is.

I don’t know what you’re talking about. Stranger danger. That thing doesn’t exist. We have to wait until we get out here for that to get picked up. So what happens quite often when we do this is that, well, SQL Server, like, you know, even though we have this wonderful index leaning on ownerUserId, SQL Server does not find a way to seek into the ownerUserId column when the query is written like this.

And so we end up with a query. And this is the compatibility level hint here. In the full training, of course, there are some comparisons that are not, did not make the cut for this video, but that’s what that’s there for.

But also to just make sure that we get something relatively batch modey. And because this query executes in batch mode, it did not run for one millisecond. That is just how long the sort ran for.

If we come all the way over here, we will see that we spent three seconds scanning the post table with a single thread, right? There’s no parallelism going on in this plan, right? 3.051 seconds.

I can move that over a little bit so that my big head isn’t in the way so much. So obviously, you know, this is a query. We want it to go faster. How are we gonna make it go faster?

What are our chances? What are our options here? Well, one way to make this query go faster might be to change from using a derived join to using a cross supply.

Since we are, since the original query was written using an inner join, this will be using cross supply. Cross supply is like an inner join. Outer apply is more like an outer join, left outer join for being very specific.

But because cross supply is sort of like a wonderful mix of a for each loop and kind of like a sub query, inside the cross supply, we can actually reference that ID column from outside, right?

And when we do that, SQL Server is able to make better use of the index that we have. So I didn’t run this yet.

I just want to come back to this. So here notice like this, we are scanning this index, right? When we hit this index, the predicates that we’re applying are on post type ID. And over here we are filtering out to where, well, we have the row number function filtering out to where row number equals one.

But we also have a bitmap that got pushed down a little bit. Not all the way down here because it’s a mixed, you know, it’s batch mode on rowstore. so we can’t do all the nice stuff that we do with normal bitmaps.

But here we have a bitmap that’s also filtering out owner user IDs. Now, coming back to our cross-apply query, one thing that’s kind of nice and one thing that kind of shows us how SQL Server treats these differently is that we don’t need to have the partition by owner user ID here because it’s implied in here on the correlation, right?

Up here, we needed to partition by owner user ID and stuff, right? Down here, we don’t need to do that, right? So if we do this, we go from three seconds to about 1.5 seconds, right?

This query does not lie to us so much at the end. So and over here, we have a seek into our index, right? So over here, we’re able to apply that seek predicate not only to owner user ID but also to the post type ID stuff.

But this does bring up something that I kind of don’t like and this happens quite a bit with nested loops plans is we are getting a sort of a double seek in here.

We have one seek keys, one up here and two seek keys, one down here. So we have two seek predicates sort of separately going into this index and doing their seeks, which I don’t love.

We’re going to talk about what ways we can address that in a moment. But another way of rewriting this query, so like before with the derived, we were essentially ginning up a row number over this entire result set.

Down here in this one, we’re essentially generating a row number over only every owner user ID that comes in, right? So we generate a row number over these.

So instead of doing one big sort, we do lots of smaller sorts. That’s a pretty good start, right? We got a parallel plan out of it. We no longer had the single threaded plan. We improved our time by two.

But this isn’t always the best way to figure out like what the… what the high score is for something. A lot of times, you’ll want to test different ways of getting the same data because under different circumstances, different techniques might produce better performance results as long as they also give you correct logical results.

That’s a nice plus, right? But here, instead of generating a row number for every user ID and then filtering on it, we’re just going to ask for the max here.

Remember, we are at about 1.5 seconds with continuing with the row number technique. And if we run this one with the max, we get down to about 667 milliseconds.

So we improved there by like another half, right? We got twice as fast using the max technique, which is fine.

But still over here, we have sort of the same kind of thing that I was talking about with the one seat keys one and the two seat keys two. So we’re still doing this like multi-seq or dynamic seek into the index, which I still don’t like very much.

So what we’re going to do is change our query a little bit and we are going to run it like this instead. And I’m just going to execute that and then come back up here and show you that we got the max score for this here and the max score for this here.

One is for post type ID equals one. Well, the other is for post type ID equals one. So we’re doing two separate hits of the post table. And now this query finishes in about 15 milliseconds, right? Because we’re no longer doing that sort of that multi-seq within one seek operator.

We have broken this out and we’ve given SQL Server two very clean predicates to seek into and figure out what we want to get out of each one. We could even do that with top one.

And top one, of course, would give us the same result here. So we run this. And this gets down to, well, it’s just about the same execution plan. It also finishes in about 15 milliseconds.

So this is just another example of how we can better align queries to the indexes that we have. Whenever you’re query tuning, whenever you’re out there looking at execution plans, if you see something in there you don’t love, like it started off, we had an index scan on an index and we knew that that index led with the column that we’re, one of the columns that we’re correlating on owner user ID.

We just knew something wasn’t right. Something was amiss with that execution plan. And by rewriting the query, we got SQL Server to take better advantage of an index that we already had kicking around.

All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about something, I guess, rather similar to this, because I did promise we would spend some time talking about aligning queries and indexes.

Anyway, thank you for watching.

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.

Related Posts

One thought on “Learn T-SQL With Erik: Aligning Queries and Indexes Part 1”

  1. any chance of doing a search for supply and replacing it with apply in the transcript ? So it reads “cross apply”

Comments are closed.