How SQL Server’s Missing Index Requests Can Hurt Performance

Posted on by Erik Darling

DON’T THROW EGGS


Thanks for watching!

Video Summary

In this video, I delve into a peculiar performance issue that arose from a stored procedure and a missing index request. The scenario began when a user passed an unusual negative value to the stored procedure, causing significant performance degradation. To mitigate the problem, developers implemented a catch to convert any negative values to zero, ensuring the execution plan remained stable. However, this fix led to further complications as another query against the same table exhibited poor performance due to a missing index request. The video explores how adding an index suggested by SQL Server’s missing index feature improved one query but significantly slowed down another, highlighting the importance of carefully testing database changes in development environments before deployment.

Full Transcript

Hello, Erik Darling here with Erik Darling Data. And I wanted to talk about sort of a funny situation that I was recently asked to remedy. And that funny situation, well, the funny situation started with a stored procedure, sort of was escalated by a missing index request. And all came tumbling down on top of that stored procedure. So the stored procedure, it didn’t look like this because it was not, it was not, it was not an issue with Stack Overflow. I’m going to level with you. To be very honest, Stack Overflow has never asked me to fix a performance problem. I just keep having to find these random performance problems in the, when I do demos in the database. It’s the damnedest thing, isn’t it? Anyway. In real life, it was a slightly different scenario, but it was a stored procedure. And at some point, at some point, some user, we should call them a loser probably, had passed in a bad piece of data to the stored procedure. In fact, it was someone had passed a negative value into the stored procedure once. And that caused everything to fly off the rails.

Everything went really, really, really badly with this stored procedure. So the developers in charge of it put this catch in to fix things. Where if someone passed in a negative value for their stored procedure, they would, they would revert the value to zero to a positive number so that they did not get their big, golly gosh, awful plan. So this is what the stored procedure looks like. And, you know, there’s a pretty simple select top one query after that. Not a big deal. And in fact, if we go and we look, we can see that ever since we converted this database from access to SQL Server 2000, we have had this index in place, the single key column index in place. And with that index in place, this stored procedure runs relatively quickly. And by relatively quickly, I mean, instantly. If we look at the execution plan, this thing finishes in 87 milliseconds.

There’s a missing index request. But if I have a query that’s ending, that’s finishing in 87 milliseconds, I’m not like jumping up and down and saying, hey, we really, really need to add this missing index request for reasons. Right. Like I can run this a million times every time I run this. It’s quick. And I’m not running this for like a small value. I’m running this for John Skeet and John Skeet’s got all the values. John Skeet has a lot of the most posts in the posts table. So you can, this is not like just like some wimpy value that we’re searching for. Every time we run this, it is reliably under the 90 millisecond mark.

Right. So that’s a very fast stored procedure. At least I think it’s fast. You might, you might not. You, you might be a much better query tuner than I am and have a much faster, have a much different view of what’s a fast stored procedure. Now where things got kind of weird is, and this is even with this like funny catch in place, everyone would, normally you’d see this and chop someone’s head off for declaring a variable inside a stored procedure and then feeding it into a where clause. But, but, but everything’s okay here. Where things got bad was, there was this other query. And this other query was also against the post table, but it had a different where clause.

Now what I’m going to do is I’m going to run this store, I’m going to, not store procedure, this piece of code. And this piece of code is sort of going to look at, look, is going to look a lot like what the other query was doing. And this one takes about two seconds to finish, to get a top one. And it happened because we didn’t have another useful index for this, for this query to use. So we scanned the whole clustered index and the whole thing. Well, I mean, I guess that’s closer to about two and a half seconds there. 2.381 by 120 milliseconds.

We’re under. Now there’s a missing index request for this. I’m going to show you the missing index. Missing index details. Now if we zoom in here, zoom in and look at, look at what SQL Server thinks a helpful index is. It’s, I mean, it’s, it’s on the post table, obviously, because that’s where we’re selecting data from. And it’s on parent ID and then creation date and then last activity date. And we’re including post type ID.

Now, what you might notice at this point is that there’s some overlap between this missing index request and the query we have that’s fast. So A, they’re both on the post table. And B, the where clause for our query inside the store procedure also has parent ID and post type ID in the where clause on top of owner user ID. Right now, we have a single column index on owner user ID. When we seek to that and do a key lookup for everything else, we’re cool. We’re in great shape.

But we have this missing index request. And this missing index request was, was super, was everywhere. It was endemic. It was, I mean, it’s a big missing index request. I’m probably using all sorts of wrong words here. Let me turn off execution plans. And let’s run SP Blitz index and look at the post table. So over on the post table, the second thing we’re going to have down here is missing index requests.

Now, this one up top is the one that’s on the table for that query that we ran. And over in this window, I have run, I have run that query, as they say in the south, a whole mess of times. So this is printed out this phrase every time this, this has been running for doing a lot of stuff. But we have this missing index request. And this missing index request was showing, well, I mean, quite a bit of use, 14, almost 1,500 uses.

It would bring the query cost to zero. Impact is 100%. And the average query cost is absolutely astronomical. It is 3,474.1810 query box on that thing. So when we look at the estimated benefit of adding, I can hear my kids screaming in the background maybe.

If we look at the estimated benefit of adding this, it’s 515 million query box that we would have created or saved by adding this index to our workload. All right. You’ve talked me into its SQL Server. You have shown me that if I add this index, it would have been used 1,500 times by this very expensive query. Now, if I run that very expensive query, oh, wait, I did that. Two and a half seconds. Cool. We got that.

We have a benchmark there, right? This is two and a half seconds to do this. All right. Two and a half. Cool. Now, this is the index that someone came along and added. Someone charged a lot of money to add this index on parent ID, creation date and last activity date, and include post type ID.

Because that’s what SQL Server asked for. SP Blitz Index didn’t make this up. It didn’t conjure this out of nowhere. It showed us what SQL Server’s own DMVs have told us.

What they don’t tell us, though, is what if this index goes and screws up some other query? So let’s create this index. And this will take a moment. This will take a moment here. Create this index. This great index on parent ID and creation date and last activity date, including post type ID. Get that whole post table in there.

That took 12 seconds. But let’s look. Because now I want to make sure that this helped this query. And by God, it does. This thing finishes instantly. Now, if we turn query plans back on and look at this query, holy smokes, that is zeros.

Look it. Zeros. All zeros. We didn’t spend anything doing this. What an amazing index. What a fantastic index. That’s the best index that’s ever been created. Except now, this query slows down. This query is not as fast as it used to be.

This query has taken some extra time. This query now takes 14 seconds to run. Remember, this one was running reliably in under 90 milliseconds. And now, it just took 14 seconds to run. If we go look at the execution plan, we can see that was the entire time this thing ran.

We spent a second here. And we spent 9 seconds here. So that’s 10 seconds. Then we spent 4 seconds doing a nutty loops join. And then we spent, well, we spent to spend any time doing this sort. But, whew.

I mean, that’s bad enough. Imagine if you were tuning queries and indexes and you added that index. It was like, yeah, this is going to make everything much better. And then it made a vital query go much slower. Well, this happened for a pretty funny reason.

And a pretty funny reason is that when we declare a local variable for, what do you call it up there? Where is it going? For a parent ID.

We get a very, very bad guess in here for what’s equal, how many rows this equals several thinks are going to happen. Things are going to evaluate to true for any given predicate on parent ID. In fact, if we go look at the query plan, we go look at this seek, we can see that the estimated number of rows is 1.87.

But the actual number of rows is 6, 0, 0, 0, 2, 2, 3. 6, 0, 0, 0, 2, 2, 3. Yeah, that’s a seven finger number.

That’s a big number. We were off by a lot there. We made a pretty big mistake. If we look at the key lookup, this thing will have executed once for every row that came out of there. And that’s no good either. So we had kind of a funny, perfect storm of things go wrong here.

And if there’s a lesson, it’s that, you know, while SQL Server’s missing index requests are a lot better than nothing, they’re a good sign that we need to do some work. They are like a crying baby.

On your tables and DMBs. Whereas SQL Server says, hey, we could be doing something better over here. We have to be very careful how those indexes change other queries in the workload. And this is why, you know, we must, as responsible data peoples, tell people to test things carefully in a development environment before just releasing these changes into prod. Because you can introduce all sorts of funny regressions here where you might make one query much better.

But you can make another query much worse. Now, granted, this is not the best thing to do here. This is not a good practice. I’m not condoning doing this. But this is what made sort of the perfect storm of weird stuff happen. We’re adding that other index made this query much, much worse.

Anyway, that’s it for me. Thanks for watching. I hope you learned something. I hope you were shocked and horrified by what I showed you. And I will see you in some other video. 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.

Related Posts