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

Posted on by Erik Darling

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

Chapters

Full Transcript

Erik Darling here with Darling Data, the one and only, the original monitoring tool mogul, just kidding, that doesn’t make any sense. In today’s video, we are going to finish up talking about the query align and design thing series of videos by talking about missing index requests. Before we get into anything else, this is where I get to share a funny story with you.

In one of my consulting engagements many years ago, I was talking to a developer about indexes. And they said, Erik, I’m going to be honest with you. And I said, this is the first time for everything, isn’t there?

And they said, I don’t really know anything about indexing. I just follow whatever the missing index requests say. And I was like, back then I thought, oh, so you just follow whatever the computer says, and now you don’t know anything about this important thing. And I was like, wow, that’s just like today.

Anyway, it’s a good thing you all got AI ready with me, so now you don’t have that problem. But in today’s video, we’re going to talk about how missing index requests will often leave much to be desired. We’re not going to do all of the content because this is, of course, a video.

It’s a small snippet from the larger Learn T-SQL with Erik course, which if you would like to buy that course, if you think this material might be worth your time and money, aside from these free passing moments on YouTube, you can purchase the course down in the video description.

There’s a coupon code on there for $100 off. I don’t know. Saving $100 is kind of cool. But there are also other links. You can hire me for consulting.

Perhaps you would like me to look at your missing index requests and make fun of them. Or give you better missing index requests, which I can do. I am capable of that. But instead of offloading that to the robots, you would be offloading that to me.

But at least I’m happy to teach you about these things. You can also buy my other training, become a supporting member of the channel for as low as $4 a month. If you just like this material enough to say thanks here and there for $4, you can do that.

You can also ask me office hours questions. And of course, as always, please do like, subscribe, tell a friend. Tell a family member.

I don’t know. If you think this content just absolutely sucks, tell your worst enemy and drag them down into the abyss with you. If you would like a free SQL Server performance monitoring tool, I have one.

Offer one. It is at my GitHub repo. The link is down again in the video description. Absolutely free, open source.

All of the performance monitoring metrics that you would ever care to have in front of you. And give your robot friends. A way to look at, because again, we are offloading everything to the machine. So let the robots figure it out.

And then you, I don’t know, I guess go do stuff. I mean, when you’re watching this, I will be at Data Saturday Croatia like tomorrow basically. So that’s fun.

I think anyway. But I’m not there now. I’m at home now recording this ahead of time so that the content flows while I’m traveling. But after that, I will be home for a bit and then I will be in Seattle in November.

So yeah, again, up in the air on how we’re going to fill that time. We might need to think of some things to do. But now it is time to continue losing our summer minds in this summer heat and talk about missing indexes.

So on missing indexes, generally, if you’re looking just at a query plan, you only see one up at the top. But there might be many. The impact number that you see is an estimate in how much it will reduce another estimate.

And that is a cost. So the impact number is really an estimate of an estimate. It’s a very, very fuzzy number.

Uses is more based on the plan cache than anything else, like actual queries using something. And the thing to keep in mind here is that they are quite opportunistic and they are not very thoughtful. Missing index requests occur as part of the process.

They are part of query optimization. And if you let the optimizer spend a long time thinking about indexes during query optimization, you would probably be unhappy at how long it takes for the optimizer to produce a query plan for you. So the missing index requests are a bit like a dating game where the optimizer is like, well, I really want an index that’s six feet, makes six figures, and has a six pack of Miller Lite Natty Ice.

Steel Reserve. But I don’t know. So it just figures out, well, I have this index, but I would prefer this index.

So it’s almost like a little bit of infidelity there, if you ask me. So you just really don’t want it to take a long time to do that. And if you see a missing index request, the best way to validate it is to run the query that is requesting it, get an actual execution plan, and then look at, if the slow part of the query plan lines up with the index that SQL Server is asking for.

In a lot of examples that I go through in my Stack Overflow database, SQL Server will ask for a missing index on like a scan of the users table, which is only a couple million rows.

And you would go from like, let’s say, 100 milliseconds to like 20 milliseconds. It’s not a big meaningful difference. You want to make sure that that index takes some meaningful chunk of time out of your query by being there.

Because indexes, they need to pay for themselves a little bit. Because once you create an index, now that index is costing you in transaction log space, in space in the buffer pool, in writes, in locking.

So there’s all sorts of costs to having that index, and you want to make sure that that index pays for itself by making queries faster. But there are some issues with missing index requests.

And one of them is that they don’t really understand selectivity at all. What you get from them is key columns based on the WHERE clause. And to some extent, like the other things, but really the key of the missing index request is just like the WHERE clause stuff that you see.

And then like anything else just goes in the includes as a mishmash. It doesn’t matter if you’re joining, sorting by it, grouping by it. It does not, SQL Server does not pay much attention to that.

Equality predicates will always go first in the missing index request, and inequality predicates will always go second. Anything else ends up in the includes. The order of columns within the equality and inequality predicate chunks, or groups, some people might call them, has nothing to do with the current predicates at all.

It just orders them by each column’s ordinal position in the table. There is a great question by a fellow named Brian Reebok, who I haven’t spoken to in a while, but I hope he’s doing well, over here.

If you ever feel like reading it, just click that link on your screen with your forehead. It’s really hard. Just click it with your nose or something, right?

You can do it. But by ordinal position, I mean this. When you create a table, the order that you create, the order that you list off your columns in, SQL Server, that is the ordinal position in the table.

So like in the POST table, these are the ordinal positions. It would make a lot of sense if I just said c.columnId. I typed it right, there we go.

If I did this, then you would see this is the ordinal position. So SQL Server will order the columns in your equality and inequality predicates by this, not by how selective anything is.

So sometimes that might work out in your favor. Because if we run this, answer count of 518, one row has that. POST type ID 1 has 6 million rows.

So if we look at the execution plan, there is indeed a missing index request. And answer count comes first, qualifies for one row. And if we created this index, we would be able to seek right to that answer count and then find the POST type ID associated with it.

So that would be cool. That would be a very efficient seek. Seeking through 6 million rows, maybe a little bit less so. You don’t know, right?

You never can tell. Other times, that might not pay off. For example, if we asked for a POST type, a parent ID of 0, which has 6 million rows, and a POST type ID of 8, which has 8 rows, SQL Server will say, Hey, give me a missing index on parent ID and then POST type ID.

Which is, again, not what we would want, right? Because if we were designing indexes, we would most likely want to have the more selective predicate out in front.

Now, this missing index request, I believe, because we have, again, we have two inequality predicates here, one on creation date and one on score.

So these are going to be grouped together. Before, we had inequality predicates, so those were grouped together. But now, these inequality predicates, this is obviously nonsense. And the reason why I say that is because not a whole lot of POSTs have a score greater than or equal to 25,000.

But every single POST in the POST table is between 2,000, 7,000, 1,000, 1,000, and 2,013, 1,231. At least in my copy of the Stack Overflow database, it is where the world ended on January 1st of 2014.

There are no POSTs beyond that date. So every single POST in this table is between those dates. Almost no POSTs have a score of 25,000 or greater than or equal to, right?

So if we look at the execution plan, we again have a missing index request. But SQL Server has said, No, give it to me on creation date and then score. Well, okay.

Let’s do what SQL Server asks. Let’s add this index on creation date and then score. So that’s what we’re doing. Here we’re also going to include owner user ID so we don’t have to deal with anything else.

I can’t remember if owner user ID was in the includes up there. I didn’t pay that much attention. But with that index in place, if we run these three queries, what we will see are three execution plans.

And none of these take terribly long. But notice that we scan through all of these. They are parallel plans.

And each one, let’s just say, they take close enough to 500 milliseconds across the board. Is this a particularly good strategy? I don’t know. Because we read 17 million rows from the table.

Because our leading key of the index, our initial seek predicate, was everything. And then we have this residual predicate come back to me, my love, where score is greater than or equal to either 5,000 or 10,000 or 25,000.

And all of those numbers are more selective than the entire creation date range in the table. But again, this is stuff we don’t want the optimizer thinking about at run time.

This is not something we’re like, optimizer, but this is a huge date range. We don’t want it thinking about those things. We want it thinking, come up with a good query plan. All right.

So, but that’s where we come in, right? Where we’re going to create, we’re going to reverse that index and we’re going to make one on score and then creation date, right? Because we are going to evaluate our data and our query.

And we’re going to say, I think our predicate on score is way more selective. And we’re going to be right, aren’t we? Are we ever wrong?

No. If we were ever wrong, it would be a terrible demo. Or I don’t know, maybe it’d be a very amusing demo for you. But now when we run these three queries, which are identical to the previous three queries that we ran, we will get much tidier, much more efficient query plans that seek and seek and seek.

And these all take zero milliseconds. Why? Because we sock to a smaller range of data first, and then we applied our range predicate, our larger range predicate second.

So when it comes to missing index requests, there are a lot of things for you to watch out for and be careful for. If anything, don’t take them literally. There are many other, there are many things where, you know, you should maybe not take them literally, but maybe take them as sort of like an instruction or something where you might say, hey, SQL Server is throwing a missing index request.

Perhaps there is something I should pay attention to here. Perhaps it is trying to hint me towards something. What’s that, Lassie? Timmy’s in the well? You go over there and like, you know, I don’t know, maybe Timmy’s in the well.

Maybe he’s, maybe Lassie’s just messing with you because Lassie knows every time she, she barks, you go and look in the well and she’s like, it’s funny to me. Dogs, you know, what are you going to do? Anyway, lots of things to take as a grain of salt with missing index requests.

Again, you may use them as a hint or an indicator that something about your query, the indexing for your query could be improved, but there are many things that you need to be careful of and many things that you should know about indexing before you go and create those missing index requests.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I will see you next Tuesday. I should stop saying that for office hours.

Hopefully I make it home alive from Croatia and all that stuff, you know, air travels. It’s been a little weird lately. But 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

Leave a Reply

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