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

Posted on by Erik Darling

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

Chapters

Full Transcript

Erik Darling here with Darling Data, and we’re going to continue our endeavors to learn T-SQL, and in our T-SQL learning endeavors, we are going to continue looking at how we can better align our queries and our indexes. This video is actually one of my favorite demos. It’s a fun one, and it’s a good sort of mental exercise when you’re tuning your own queries and perhaps wondering why SQL Server sometimes uses your indexes and sometimes it doesn’t. So, down in the video description, you will see all sorts of helpful links. Of course, because I am a helpful human being, arguably one of the most helpful human beings to have ever walked the face of the planet.

And humble to boot. And down in the links, you will find a way to buy the full course content. All of the material that you’re seeing around the index align and design stuff is part of my Learn T-SQL with Erik course. There’s a link down in the video description where you can save a hundred bucks off the course if you buy it from there.

You can also do other things that would make me happy, like you could hire me for consulting. I can bring that up. I can bring the same query tuning magic, magic energy to your SQL Servers.

You can choose to support this YouTube channel if you feel like the content is helping you in some way that is worth money. A few is four smackaroos a month. It’s USD.

That’s American for dollars. You can do that. And you can, of course, ask me office hours questions. And if you happen to just like what I’m doing, like what I get up to over here, please do like, subscribe.

And tell every single one of your friends, whether they use SQL Server or not. Perhaps they will find other reasons to come to this channel and watch me. Maybe they’ll just be casually entertained by the shape of my head.

You never can tell. If you are in the market for free SQL Server performance monitoring, my free monitoring tool, totally open source, no sign up, no weird telemetry. Just all the stuff that you would care about monitoring if you want to figure out why SQL Server performance maybe is good, bad, ugly, somewhere in between.

Got a cool knock style dashboard. If you want to just make like just do a little sanity check, make sure all your servers are up and running and all that good stuff. And if you’re a fan of today’s robot companions, I don’t know, maybe before the prices go up, you can also use those things to do some built in MCP stuff.

And you can do some MCP server analysis of your performance data. And it’s isolated to just your performance data. It’s not going out and running weird queries on your SQL Server to look at stuff.

So, coming up in, wow, that is like, it’s like, I mean like 10 days away. I will be at Data Saturday Croatia, June 12th and 13th. And I have an advanced T-SQL pre-con there.

You can, you know, jump in there. Learn about that. Learn about T-SQL for me live and in person. And if you show up to that, you get free access to the Learn T-SQL material that we’re covering today in this video.

I will also be at PaaS Data Community Summit in Seattle, Washington, November 9th through 11th. So, you know, not quite on my birthday, but pretty close there. And, but for now, it is June and we are Juning about going crazy from the heat.

I mean, I’m not going crazy from the heat. It was like 60 degrees in New York today or something. I don’t know, whatever.

Anyway, let’s go learn something about SQL over here. Ah, that’s Management Studio. Now we got it. So, like I said, this is one of my favorite demos. I’ve got a few different things that I build off of this one-store procedure.

But right now, we have got this computed column that I’ve added to the post table. And we’ve got this index that I’ve added, which I know there’s a little red squiggle here, but I promise you this index has been created and the active period column is there.

So, anyway, I don’t know why that was highlighted. We’ve got this store procedure called top lookup. And this store procedure is doing something.

I mean, okay, so like, look, we’re doing select star here. I’ve talked in other videos about how select star is just a convenient shortcut. For a lot of this stuff.

You could list out all the columns if you wanted, and it would still be the same thing. Or you could even just put a few columns that aren’t in your index in here. And you would run into the same potential plan switcheroo.

Because no matter how many columns your nonclustered index is missing, SQL Server has to do lookups to find those columns from the clustered index. And it costs one column the same way that it costs 150.

1,024 columns. It doesn’t matter how many columns are outside of your index. The key lookup costs the same.

Regardless of the number of columns or the data types of those columns. So, I’ve got a hint on this query. And that hint is to set it to compat level 140. It’s not because, I’ll explain why.

The parameter sensitive plan optimization that came around in compat level 150 just makes this demo really confusing. And I’ll talk about why in a moment.

But for now, just understand that this procedure takes two parameters. One of them is post type ID and the other one is gap. The gap parameter works off the computed column.

This date diff year, creation date, last activity date. That works off the computed column and index that I created up here. So, let’s turn on actual execution plans.

Let’s feel fully actualized here. And the reason why I have the compat level set is because it sort of makes this demo it just muddies it up a bunch.

What happens is the parameter sensitive plan optimization kicks in. And you get one plan for the most common value. One plan for the least common value.

And then all of the post type IDs in the middle share the same plan. And it’s just really a bad situation. And it just makes it harder to explain the point of what I’m doing here.

So, to just sort of visualize that breakout if I run this query and we get these Oh, I scrolled the wrong way. We get these results back. Go away SQL prompt.

Come on. Be a pal here. We look in here. So, just to sort of explain a little bit. You get the three plan variants. Post type ID 2 at 11 million rows gets plan variant 3.

Post type ID 8 at 2 rows gets plan variant 1. And all of the post type IDs in the middle ranging from 4 rows to 6 million rows get the second plan variant.

This is not a good situation. And looking at these numbers you may start to understand why that particular feature muddies this demo up quite a bit. So, we’re not going to do that.

Now, the first thing we’re going to look at is the 9-year gap. So, the 9-year gap is very uncommon. Gap ID 9, post type ID 1. And we run this.

This returns very quickly. We get this plan here. It is a parallel nested loops plan with a key lookup to fetch all of the columns that we care about from the post table after we seek both to the post type ID that we care about and the gap years parameter that we care about.

The key lookup down here, there’s no predicate on that. If I move my big head out of the way, there’s no predicate on that up here. It is just outputting columns, right? But it’s outputting all the columns in the post table, which maybe isn’t a lot of fun.

But this takes 28 milliseconds to run and it gets 500 rows and everyone’s pretty happy. If we run this for a gap of 0 years, most posts do not have 9 years of time between when they were first posted and when they were last edited.

A gap of 0 years is much, much more common. And this query runs for about 4 1⁄2 seconds here. Excuse me.

Very dry. 4 1⁄2 seconds. And a lot of the time is spent in this sort that spills because the memory grant that we assign to the initial execution of this procedure maintains for this execution.

So that takes some time. Now, if we recompile this thing and we run this in reverse and we ask for a gap of 0 years first, this does a bit better, right?

About twice as fast plus another 500 milliseconds faster for a gap of 0 years. The plan changes quite substantially, though.

I mean, we still have a plan for a parallel nested loops join plan. But notice that we fully scan the clustered index over here.

And then we have this strange filter operator over here. And the filter operator is on both the post type ID and the gap parameter. Part of the reason for this is because the computed column that I added to the post table was not persisted.

SQL Server expands the definition of the persisted computed column. And if I were to change that to a persisted computed column, we could avoid this part.

But we would still get the same basic plan shape. Now, I think probably the biggest downside of this late filter operator, and if I’ve said it once, I’ve said it a million times, always be suspicious of a filter operator in a query plan, is that we have to fully scan all 17 million rows from the post table, drag them across the couple compute scalars, and then filter stuff out over here.

Okay? What really sucks is that reusing that plan for the gap of nine years, in other words, the very uncommon one, this used to take 28 milliseconds, and now this thing takes almost a second.

Right? And it’s obviously the same plan gets reused. Whenever I’m talking about parameter sensitivity problems, a lot of people get this big idea in their head. It’s like, well, why not just use the big plan for everything?

The big plan for small amounts of data is often somewhat, I mean, not surprising to me, but surprising, not surprising to other people who see this stuff, not a good sort of trade-off there.

It’s not a good fit. So we have, you know, essentially two execution plans. Neither one is a particularly good fit for the amount of data that we’re dealing with. So the mental exercise that I like to put people through when we’re doing this is to mentally in your head separate informational columns from relational columns.

Right? And what we’re going to do in the query below is we’re going to write a self join between the post table and itself. Right?

One alias will be responsible for the relational activities in our query, the join, the where clause, the order by, stuff like that. And the other alias will just be responsible for providing the select list.

Right? And if you can start mentally separating the duties of your queries and the columns in your queries between purely informational stuff that’s only in the select list and columns that are used for relational activities in your queries, you can do a lot of cool query tuning stuff.

This is just one of them. Right? So let me create this and then we will talk through the code just a little bit up here.

So I have the post table joined to itself. ID is the clustered primary key in the post table so we can get away with this. It is a unique column so doing this is very, very easy.

And from P1, right, that’s this one, this is all of the relational stuff. Right? P1 is there.

P1 is there. P1 is the where clause. P1 is further down here in the where clause and in the order by clause down here. The only place we reference P2 is up here in the select list.

Right? This is our star. Right? So if we run this query now, both 9 and 0 will be fast doing this. Right?

The 1 for 9 got even faster. The 1 for 0, instead of taking 4.5 seconds, takes 1.2 seconds. Right? So we’ve kind of come to a little bit better of a situation here.

Now, we still have this sort over here and this sort still spills. Right? So, you know, it does slow this query down a bit but we’re not at 4.5 seconds of sort of crappy spilling.

We’re at one point, actually it’s about 900 milliseconds of crappy spilling there. So this is a lot more tolerable. If we reverse things, just like we did before and we do 0 first and then 9, the plan actually gets a little bit better.

So in this case, the first query, not only does the sort spill a bit less because we get the memory grant for the larger amount of data that comes out of the POST table, but we didn’t finish in about 200 milliseconds.

Now we also get this parallel nested loops plan. And the same thing goes for this gap down here. So this is a pretty reasonable rewrite to get better performance out of both queries.

You might start playing some other tricks with this query if you really were getting, if you really wanted to like optimize, optimize this, you could even say like option use hint optimize for gap equals 0.

So you keep getting that plan for the 0 value and the gap parameter. And talk through some of the important differences between the original and the rewrite.

I’m going to put both of them into the same store procedure. And then I’m going to run them for the gap of nine years. And the thing that I just want to talk about here a little bit is the plan shape.

So what happens in the original query where we do the lookup is we find 4,500 rows here and we do 4,500 nested loops to do the key lookup here.

Key lookup plan, key lookups and query plans are very, very tightly coupled operations. SQL Server cannot move these around, right? SQL Server has to do this stuff all in one place at the same time.

It can put a sort before the nested loops join, it just doesn’t here. And then after we find all that data, then we do our sort. And this is where we start to sort of narrow stuff down for the top, right?

Down here in the one that we rewrote in order to, what do you call it, do the self join.

We still have the same seek, but notice that the post table immediately joins to the users table here. And what’s nice is that this sort cuts down the results to just about all the ones that we need for the top very much earlier on, right?

And then after we figure out relationally what rows we care about maintaining for this query to get the top 500, we do our nested loop.

We do our nested loops for only those 500 rows back to the post table here. And again, there is about a 20 millisecond difference between these. This isn’t big night and day performance tuning stuff, but you do see a general improvement.

And what I think is nice too is you see that general 20 millisecond improvement with the serial execution plan. In other words, you don’t need to get a parallel plan here in order for this to be competitively fast, right?

This plan up here, it runs, goes parallel, gets a DOP eight query plan. And this is one of those like, oh, well, you’re using a bunch of extra resources, but your query’s not getting faster, right?

It’s kind of a weird thing. Anyway, when you’re working with queries, especially parameter sensitive ones, one of the biggest differences that you will see in those queries aside from stuff like the type of join and the size of the memory grants and all that other stuff is going to be the sort of like index usage, right?

And if you can’t get SQL Server to reliably use your narrower nonclustered indexes because you are selecting columns that are outside of them and SQL Server now has this clustered index for its key lookup choice, it might just totally be worth rewriting the query to do a self join so that you can get all of your relational work done that narrows the rows down to just the ones you care about.

And then do the self join back to get the stuff later because the key lookup, again, very tightly coupled. When you write a self join, that tight coupling is sort of removed a bit.

Anyway, 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 some more about very similar techniques. All right, 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

Leave a Reply

Your email address will not be published. Required fields are marked *