A Little About Nested Loops Prefetching In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into a fascinating and somewhat humorous artifact that I believe might be the funniest SQL Server artifact I’ve encountered: a little red gate showing up in PowerPoint presenter mode. It’s unexpected and certainly adds a unique twist to what could have been a straightforward discussion on nested loops prefetching. This SQL Server feature can lead to some interesting behavior, especially when dealing with parameter sniffing issues in stored procedures. I walk through how the prefetching mechanism works, demonstrating its impact on query performance with both ordered and unordered scenarios. The video also touches on the nuances of parallel execution plans and how they exacerbate these issues. For those interested in diving deeper, I provide links to relevant blog posts by Craig Friedman and Paul White. Additionally, I share information about affordable SQL Server training and upcoming events where you can catch me live. So, buckle up as we explore this quirky aspect of SQL Server together!
Full Transcript
Erik Darling here with Darling Data, feeling absolutely magnificent today, positively capital, if you will, because in today’s video, I don’t know if you’re ready for this, you might need to strap yourself in to something, because we’re going to be entering the funniest artifact that I think I’ve ever encountered in my life, is this. little red gate thing showing up over PowerPoint in presenter mode. That’s cute.
We’ll just… I don’t know, I can pick one again there. Since I already clicked and you already kind of saw it, we’re going to be entering the sexy world of nested loops prefetching. Huh? Yeah, alright.
Hope you don’t have your tight pants on. Before we get into the incredibly lewd body world of nested loops prefetching, if you happen to like me enough to spend $4 a month, if you’re not currently supporting… As long as that $4 a month wouldn’t like come from like the $4 a month it takes to support a starving child somewhere, you can subscribe to my channel via membership.
There’s a link to do that in the video description. If that would take money away from a starving child, which I would never want to do. You can do other things to show your undying support, your unmitigated devotion to being a dated darling by liking my videos, commenting on my videos, and doing the one time maximal effort dance by subscribing to my channel.
If you are in need of SQL Server help, many of you are. I see you out there struggling, having problems, and can’t tell which end of the pants go on first. If my rates are reasonable. I can do this stuff. I can do more stuff at a reasonable rate. Whatever you need, let me know. If you are in the market for some affordable SQL Server training that will last you for the rest of your life.
I have over 24 hours of it available streaming. You can just play it and learn. Like, put your head… fall asleep with it on. Whatever you want to do. And you can get that for about $150 with the discount code SPRINGCLEANING there down below.
If you would like to see me live and in person. If you would like to see me up and talking about database-y things. In the flesh, as it were. But again, this video is going to be so sexy that I don’t think we need to add the word flesh to it. It might just be overkill.
You can catch Kendra Little and I performing two days of SQL Server miracles, November 4th and 5th in Seattle at Past Data Summit. And as always, if there is an event nearby you that you think I would be a good addition to. And they are in need of a pre-con speaker.
You just let me know what that event is. And perhaps I can work out some way of showing up. Because that’s what I do best is just show up. Like I do here. Every day.
So with that out of the way, let us continue on to the nested loops prefetch party. Alright. So I believe I’ve already created this index because it says query executed successfully.
And I did. We are large and in charge here. So first off, no video about something so just titillating would be complete without blog posts from some of my favorite bloggers of all time. Craig Friedman, Microsoft fella, used to write a whole bunch of really great stuff about SQL Server.
From what I understand now, he’s busy trying to figure out how to use an iPhone. And of course, Paul White has a good article about this stuff as well. So if you are interested in diving further into this, these links will be available in the video description.
You might have to do something so bold as scrolling to find them. Some people will ask me where to find things. And I will say, you did not scroll far enough.
I might start charging for butt wiping lessons for some people who refuse to read things. So what I’m going to do is I’m going to first drop clean buffers. And I’m doing this because what I want to show you is that when data that you are prefetching is not already in the buffer pool, you will do additional reads from disk.
Because SQL Server reads ahead to get data that it will need for other iterations of things. It will go and do that ahead of time so that it doesn’t have to issue synchronous IOs all the time. Because, well, I guess at the time, you know, the spinning disks that were available when all this prefetching stuff went in there were not very good at supplying data quickly to CPUs.
Granted, that story has gotten better except in the cloud. So if you are in the cloud, this might still be relevant to you. I don’t know.
So we are going to do this. And I want you to note that I am selecting the top 1,000 here. That’s one and three zeros. All right.
There we go. One, triple zero, thousand. And if we look at the query plan for this, though, we will see that SQL Server went ahead and read almost 1,400 rows or almost 1,400 or did rather not wrote SQL Server. Yeah, did almost 1,400 additional, almost 400 additional rows over the thousand that we have selected there.
And that was because SQL Server was like, oh, I might need those later. And if we go and we hover and we hover over the nested loops to get the tooltip up because we don’t want the tooltip interfering with the right click, which is one of the worst user interface things that happens in SSMS. If we right now right click on this so that we can get the properties overlaid over the tooltip and we go over here, you will see that we have an optimized nested loop that does not show up in the tooltip.
And that the optimized nested loop used, used, used, used, used, used, used. I’m just going to mangle everything today. The unordered prefetch.
So it went out and just sort of scatter gathered stuff from disk. It went, you, you, you, you, you, you, you, you, you, you, you, you, you. It was like one of those, like grocery shopping challenge shows where it’s like you need to put as much expensive stuff in the cart as possible. And someone just runs down the aisle almost shoveling everything in.
We can, of course, change that attribute by asking for stuff in order. And if we run this and we look at the nested loops here, we will now see that we did an ordered prefetch of things.
Right. But this nested loops is now not optimized, but it is ordered from the prefetch, from a prefetching scenario. And this one also did also read an additional 399 rows here.
Okay. So we got, we got that. We got that going for us. Now this will be exacerbated in parallel execution plans because you’ll have multiple threads rushing out and doing prefetchy things.
Like on this one where we read 1700 instead of 1000. That’s up from the 1399. We did some, did some extra work in there.
And this one down below where we will read an amazing, a whopping 1410. Right. So 410 additional rows.
So that, that will, that will change with a parallel plan. But that’s not really the point of this video. The point of this, the point of that stuff was just kind of show you where you see the nested loopy stuff in there. And also to, um, I guess kill a little bit of time.
I don’t know. I did want to show you like the additional read stuff. So, uh, where this can get really nasty is in store procedures where parameter sniffing is a thing. So I’ve got this store procedure ginned up here.
And it’s going to select the top 50,000 rows from the post table. And just to make things easy to demo without having to worry, without having to fiddle too much with things. Um, I’ve got it hinted to use the specific index that we want.
Uh, and I’ve also, uh, got this set to max.1 to, um, sort of highlight where these things can be particularly problematic. Uh, also up at the top, we are doing the same thing that we did above with the checkpoint, the drop clean buffers and a slight wait for delay to let the buffers clean out. So, uh, we’ve got this thing created.
At least I think we do. Let’s double check that there. And, uh, what I’m going to show you is what happens when this. So this is just like another weird parameter sniffing thing. Because some very low numbers of rows, SQL Server will say, oh, we don’t need an optimized nested loops.
We don’t need to prefetch anything. We’re just, it’s very, it’s a very small amount of data. We don’t have to worry about doing any of the like more involved stuff to optimize IO retrieval. So what I’m going to do is I’m going to grab the top.
Well, I mean, it’s still doing the top 50,000, but this person only gets one row. Most of the one second that we just waited for that was the drop clean buffers thing. If we go in the query plan and look what happened, this thing actually finished in about 13 milliseconds.
Right there. So if we, uh, get the properties of this, we will see that this is not optimized and that the, uh, the prefetch attribute is just straight up missing. So SQL Server doesn’t tell you, uh, SQL Server only, rather SQL Server only tells you if, uh, uh, ordered or unordered prefetch is are used.
If neither one is present, that thing is just missing. It doesn’t say like ordered pre like prefetch false or anything like that. It’s just straight up not in there.
So if we go and we look at how this runs. So this, this user, this is John Skeet. John Skeet has about 28,000 rows in the post table. Uh, this will get pretty slow.
All right. We were just waiting and waiting and waiting. And since John Skeet, since we’re getting the top 50,000, but we don’t quite have 50,000, we only have about 30,000 rows in there. I did want to grab one user ID.
That’s the community user ID that has like 220,000 something rows in there that, uh, will take a little bit longer than this even. But if we go look at the query plan now, you’ll see that we spent a lot, not much time in here. Right.
Notice we’re still harboring under the estimate from, uh, the, the, the last compilation. So this is like a parameter snippy thing, but now this nested loop section takes about six seconds or sorry, uh, this key lookup takes about 5.9 seconds. A nested loop takes an additional a hundred milliseconds.
And then we finish out the top there. So not doing that, that read ahead IO really hurt us in here. Now, if we go and we get for user ID equals zero, which has the, again, the 220,000, some odd rows in there. And we’ll actually fill out the 50,000, uh, uh, 50,000 row row goal from the top.
Things will look, um, slightly worse, right? That was, that was about six seconds. And now we are up to about 10 seconds, right?
So this key lookup situation took 10 seconds, one, zero tough time. And where that changes though, is if we recompile this and we go back and we run this the first time for John Skeet, right? So the 22656 user that didn’t take five something seconds.
Did it that took about half a second, right? There’s 531 milliseconds. And if we go look at the nested loops join now, uh, even though the optimized part is false, the ordered prefetch thing is true. So SQL Server did go out and do a bunch of prefetching to optimize the IO from the lookup, right?
And now if we get out of here and we go look for user zero, this will also be pretty quick, right? It’s not going to be quite as quick as John Skeet, but we’re only, we’re only about 300 milliseconds, uh, different rather than like three seconds different, right? Cause remember these numbers almost scale in the same way where, when we ran for user ID six, whatever first that had one row and we didn’t get the prefetching, uh, John Skeet then took about 5 point something seconds.
And this one took about like about 10 seconds. So these go almost up in the same amount, but just in milliseconds rather than, uh, rather than full seconds. So if you are ever dealing with a weird situation with a query plan or even a, you know, something in a store procedure or parameterized, uh, parameterized query where you have a nested loops join.
And, and like the plan doesn’t plan itself doesn’t change. And you, you suspect parameter sniffing and you’re like, maybe you go to query store, maybe you use SP quickie store to look at quickie store. And you’re like, well, this thing generates different execution plans, but they all look the same.
They all use nested loops. What’s going on? Uh, doing something like this and looking at the properties and figuring out if sometimes the, um, the nested loop join uses prefetching and sometimes doesn’t is probably going to be your answer. Now there are various ways of fixing that.
Um, you know, if you have, if you know kind of what you’re doing, uh, you may be able to add an optimized forehand. Uh, to your query to continuously get the prefetching, right? Cause the prefetching is based on cardinality estimation for low numbers of rows.
SQL servers like, no, we don’t need to go out of our way for that. Um, I, if, if I’m remembering correctly, there’s like a, it’s like 25 rows that you, at minimum that you need to get the, the prefetching stuff. But, uh, those details may have changed and may actually be subject to change.
I don’t, I don’t, I don’t recall, uh, exactly how long ago I had, I had figured that one out, but, uh, it may, it may be different now. And, um, it’s kind of a pain in the butt to find all the different test scenarios for these videos and still have them take a reasonable amount of time where my camera will stay on and won’t overheat and all that other good stuff. So anyway, uh, the takeaway here is if you’re dealing with a situation where a query continuously produces a nested loops join, either for a key lookup or even a table to table join, and you, uh, produce the same nested loops plan.
But one of them is much slower than the other sometimes. The answer is probably going to be at the nested loops operator where you are going to be missing the prefetch for some and not missing the prefetch for others. The prefetch can make a very big difference, especially on crappy hardware.
So with that out of the way, with that brilliant recap out of the way, everyone, I hope everyone’s doing okay now. I hope everyone is sufficiently covered from, from this, this just charged video. Uh, uh, I hope you enjoyed yourselves.
I hope you learned something. Uh, I hope that you will continue to watch these wonderful videos and, uh, support this channel in whatever way you, you are able to. Um, cause I, I do appreciate you.
Anyway, uh, that’s about it for this one. Uh, thank you very much for watching. Um, I believe it’s, it’s nearly time to go to the gym and, um, you know, make, make, get a, get a physical workout, break a, break a physical sweat on top of the mental sweat that I spent talking about nested loops prefetching. Ah, all right.
That’s good here. 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.