More Of What Missing Index Requests Miss In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the world of missing index requests and why they often don’t provide the performance boost we might expect. Starting off by dropping all indexes in my database, I ran a query that SQL Server repeatedly suggested would benefit from various indexes, only to find it still running after 14 minutes despite adding these indexes. This led me to explore how certain query patterns, like distinct counts and batch mode operations, can actually run faster without any additional indexes at all. The video also touches on the importance of not always relying on missing index requests for performance tuning and highlights alternative strategies that might be more effective in specific scenarios. If you’re interested in diving deeper into SQL Server performance tuning or need help with your own database issues, I offer consulting services and training courses that can provide valuable insights.
Full Transcript
Erik Darling here with Darling Data. Bet you didn’t know that. It’s hard enough to get some people to read things like the video title before making weird comments. Even more difficult to get people to watch the video before telling you something that you said in the video like you didn’t say it in the video. So, you know, that’s fun. Not at all frustrating as a content creator, but what can you do? Hopefully everyone can take the time to read Darling Data and we can go from there. I think that’s a reasonable starting place for all you smart people, especially ones that have senior in your title. So, this is a little bit of an addendum and it only made sense to record this right after the other video because it is an addendum. Hopefully I spelled addendum right. I often don’t. I don’t have senior in my title. So, we do what we can here at Darling Data. Like check for Intel drivers and sometimes Intel times out and then it has that helpful little pop-up behind me, which I love.
Not at all distracting or annoying. Anyway, before we talk about our addendum to why missing index requests stink, we’re going to talk about business. Important business stuff. If you would like to support this channel for as low as $4 a month, you can do that by signing up for a membership via the link in the video description. If $4 a month escapes you, then you can of course, like videos, like videos, comment on videos, and even subscribe to the channel. All wonderful things to do that make my probably enlarged heart go pitter-patter. If you need help with SQL Server, if you watch the things that I talk about and you think, gosh, I’m having those problems and I’d like Erik Darling to come fix those problems for me, that’s the kind of thing that I do via my consulting.
Crazy, I know. Crazy, I know. I’m not just making this stuff up. If you would like training about SQL Server performance tuning, I have beginner, intermediate, and expert level stuff all available to you for life for 75% off, which means about $150 USD. Go get it while you can. Who knows when that might expire? Not the for life part, but I might actually put more work into that. Who knows? Crazy stuff. If you would like to see me live and in person. In other words, I might stop recording free videos and make you start paying for things.
Ah, I know. Some people, you do one free thing and they can’t stop asking you for more free things. It’s a wild world out there. But if you would like to come see me live and in person, well, the pre-cons are not for free, but actually none of this is for free unless you win something from Redgate or you sneak in. I’m not condoning sneaking in. I’m just saying it’s possible you could.
November 4th and 5th, I will be in Seattle with the lovely and talented Kendra Little co-hosting two magnificent days of SQL Server performance tuning. If there is an event nearby to you that you think the old Eric Darlingmeister might make a good addition to by way of a pre-con, let me know what that is and I might just show up and I don’t know. So, this stuff happens sometimes. So, with that out of the way, let’s go talk about why missing index requests annoy me a little bit more.
So, well, lucky me, this query is still running. If you look sort of, well, no, sorry, this one. If you look, you can still see the time moving a bit. 13, almost 14 minutes. So, when I started this query off, what I did was drop all the indexes in my database. And then I just didn’t even, like, run the query because I’m not waiting 14 minutes for anything.
Except, I don’t know. Okay, a few things I would wait 14 minutes for. Not much, not many things. I just got the estimated plan a bunch of times. And every time I got the estimated plan, SQL Server told me that a different missing index request would fix everything. If we scroll down a little bit, you’re going to see, first, we started with an index on the comments table on the score column that includes user ID, which I guess makes some sense because we’re filtering on score and we’re joining on the user ID.
And then it said, well, now we need one on the post table on score and then owner user ID that includes post type ID, which I guess makes sense because we’re filtering on score and we’re joining on owner user ID and we’re selecting post type ID. So, okay, fine, totally sensible. And then it was like, oh, well, no, we need one on the votes table.
We need one on vote type ID and post ID. It makes sense because, you know, we’re correlating to the post table on post ID and we’re filtering to vote type ID. So, okay, reasonable guess there.
And then, you know, SQL Server was like, whoa, whoa, whoa, whoa, whoa, whoa, whoa. Now we need one on the users table on reputation and display name. Okay, we’re filtering on reputation and we’re selecting display name.
So that’s okay, too. And then SQL Server was like, you know, now that I think about it, I was wrong about comments, votes, and posts. I would like three slightly different indexes on those.
And thankfully, that was the end of it. Unfortunately for us, and unfortunately for our query, that was not the end of the, sorry, I’m going to do that every time. That was not the end of our performance issue because this query has still been running for 15 minutes.
If we run SP who is active and we get the execution plan, we’ll kind of see what was happening here where, well, I mean, this part of the query was, I mean, you know, still, it got better, but it’s still kind of slow. We spend two seconds on the, two milliseconds on the users table, 374 milliseconds on the post table, 3.6 seconds on the votes table, getting a little yucky there, and then a minute and 51 seconds on the comments table.
And all this after adding in every single index that SQL Server assured us would make things better. Where we really start spending a lot of time in here is when we start getting into this part of the query. No indexing is going to help this part of the query.
There is no indexing to help anything from after this. Anything from this line on, indexing is not going to help you with. Spooling all this data into tempDB.
SQL Server is not going to fix that. Respooling that data out of this spool. So now this is sort of an interesting thing here. Because there is no child operator to this spool, we can infer that this spool and this spool and this spool are all the same spool, and we just need to get the data out of them once we have finished getting the data into this spool.
We just need to spool and spool again and again, or spool and spool alike maybe, I guess. So no index is going to help this. But SQL Server doesn’t really know that.
SQL Server also doesn’t really have a good view of what might be slow and all that. This is up to sort of the more experienced query tuner to figure out on their own. Now, one thing that SQL Server never told me was that certain things can make that query pattern faster, particularly where we are getting a couple distinct counts, which is why we had all that spooling going on.
So something like using compatibility level. Now, I want to be very clear about this. I am using compat level 140 currently, so I’m already using the new cardinality estimation model.
For example, me moving to compat level 160 is just to get batch mode on rowstore. And even with batch mode on rowstore in place, or rather, especially with batch mode on rowstore in place, this query will run faster.
At least, it did before. Whether it does now or not is up to the demo gods. But what’s even funnier about this, aside from the fact that this thing is still pretty slow, is if we get rid of all the indexes, right?
We have now gotten rid of every single index that SQL Server suggested and that I dutifully obeyed. If we get rid of all of those indexes and we run this without any indexes, right? We didn’t have to add a single one.
We needed nothing. This thing will finish. I think it was about 10 seconds. If not, I’m going to feel really embarrassed. Of course, you know, it wouldn’t be the end of the world.
There we go. Okay, I was off by five seconds. Sorry about that. This query plan might be slightly different from the last one that I got. Oh, it is.
Look. Yeah, SQL Server chose a nested loops join there for some reason. I wonder why you would do that. Why would you choose a nested loops join? Hmm. It’s a bit of rose to choose a nested loops join for.
But I don’t know. So actually kind of funny. This plan is a bit different from the query plan that I got before. But what’s different here is that a whole bunch of this stuff now runs in batch mode. If we look at this, we will see batch mode right above my beautiful gigantic head.
If we look at this hash match aggregate, even though it’s spilled, we will see batch mode floating around there. If we look at this hash match inner join, we will see batch mode there. We will see batch mode here.
We will see batch mode here. We might even see batch mode on a couple other of these index doodads, which is so nice to see. Oh, not post table. Post table is still rowstore. Sad for post table.
Hmm. But SQL Server doesn’t have to do all that crazy spooling when you do distinct counts and use batch mode. It only has to do that in row mode. Batch mode fixes a lot of stuff.
But notice SQL Server is back to saying, but you know, this might be better with an index. SQL Server just needs to learn when it’s wrong. It needs to learn when to stop suggesting things.
SQL Server is kind of like, you know, when you just want to like complain about something a little bit and someone keeps trying to like solve every problem. And you’re just like, no, just listen to me complain a little bit. I don’t need, you know, I don’t need like advice about this.
You know, like one time I was complaining about like importing an Excel file with SSMS and like 40 people were like, oh, just learn SSIS and do it with SSIS. I’m like, no, I just really, I just, I just wanted to complain about loading an Excel file in with this thing. I don’t know.
I’m not going to go learn SSIS. It’s a dead technology. Right? What do you want to go learn SSIS for? You out of your minds? Anyway, this is another reason why when you are tuning queries, missing index requests are not the end all be all of making a query go faster. Oftentimes, there are different and better approaches that you can take.
For this specific query that we’re looking at here, there are all sorts of things that we could consider that might make things better. Like we could, you know, put part of the query with part of the aggregate, like part of the aggregations already done into a temp table. You know, it’s a perfectly feasible thing to do.
Right? All sorts of stuff that we could have done. We could, I chose batch mode because that’s a real easy way to test if a query goes faster. If you get batch mode going on and a query goes faster, you’ve already solved all the problems without indexes, which is great.
Because everyone knows, or rather, everyone is terrified of indexes. Like, ugh, I can’t add another index. What if I slow down an insert?
God, what will happen? Let’s let every single read query that our application has suffer because I’m afraid that I might slow down an insert. What a great idea.
Right? It’s like LinkedIn-level advice on things. Oh, indexes. But they might slow down inserts, updates, deletes. Oh, no. Not that.
Anyway. I was going somewhere with that. But you know what? I think I’m just going to go get ready to record another video. So I think I’d rather do that instead.
So thank you for watching. I hope you enjoyed yourselves. I hope you enjoyed this addendum to why I hate missing index requests. I hope you learned something, and I hope that you will do all the stuff that I tell you to do in life because I’ve made enough mistakes that I feel qualified to tell you what to do.
So I think that’s good. Anyway, thank you for watching. Thank you.
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.