How To Tell If Lazy Table Spools Are Slowing Your SQL Server Queries Down, And Fix Them
Video Summary
In this video, I dive into the world of lazy table spools, specifically focusing on how they appear in query plans and whether their removal can be beneficial. I explore these concepts by analyzing execution plans from queries that use cross-apply, a scenario where nested loops joins are often used due to SQL Server’s limitations with certain join types. Through detailed examination of the plans, I demonstrate how to assess the effectiveness of spools using rebinds and rewinds—key metrics that can help determine if a spool is truly adding value or just cluttering up your query plan. By walking through practical examples and offering tips on when to consider removing spools, this video aims to provide actionable insights for database administrators looking to optimize their queries.
Full Transcript
Oh, it’s Erik Darling with Darling Data. And, boy, what a weird week it’s been. I kind of wish I had a do-over on this one. But anyway, in this video, stunning masterpiece of a video, we’re going to talk about how to tell if spools are effective, specifically lazy table spools, because those are otherwise known as performance spools. And they have some rather interesting characteristics. And there are some, there are some times when you’re going to want to handle them, or rather handle their removal differently. Or at least testing to see if their removal is a good idea or not. So, first, we’ve talked a lot about eager index spools. Table spools, in select queries, anyway, are quite similar in their reasoning for appearing in a query plan. They appear on the inner side of a nested loops join, which we’ll look at in a moment. And they’re there because SQL Server doesn’t want to do a whole lot of repetitive work on the inner side of a nested loops join. And by repetitive work, let’s mosey on over to this window. I’ll show you what I mean. Let’s say that on the outer side of a nested loops join, you only have the numbers one through 10. That means that SQL Server, if it uses a nested loops join, will only have to take each number, go do work with it, and do that work one time.
All right, exactly once. That, it’s pretty easy. You most likely won’t see any spooling. Well, you might see a eager index spool if you don’t have any good indexes. But you likely won’t see a lazy table spool on the inner side of a nested loops join when you have a distinct list of numbers. A pretty distinct list of numbers. If you have some duplicates in there, SQL Server will probably forgive you a bit. But where things get far less forgiving is, let’s say our list looks more like this. All right, so I’ve just kind of cross-join this generate series function to itself. And we have this list now, where we have 10 number ones, that was close, 10 number twos, 10 number threes, and so on.
In this case, SQL Server would have 100 loops to do. But for every, for like, you know, 90 of those loops, we would just be reworking things that are already done, right? We already, we already figured out what to do with what the data for this is. We don’t need to rerun a whole query to do that nine more times. So that’s when eager index spool, or sorry, when lazy table spools typically come into play, right? When SQL Server’s like, wait a minute, you’re telling me, I just figured out all this stuff about the number one, and now we got to figure out stuff about number one nine more times? No way.
I’m going to cache this data, and I’m going to reread this data, rather than go do that work over and over again. All right, so let’s look at some plans, right? Good times, good times, these plans, these execution plans, just unlimited mirth and joy comes from looking at query plans. I’m telling you.
So you may ask yourself, what’s the point of these spools at all? Why, if SQL Server thinks that a nested loops join is just going to be that much work, why, why on earth wouldn’t it just use a different type of join? And the answer is that not all types of joins are supported by all constructs in SQL Server.
If you use cross-apply, even a lot of times outer apply, if you cross-join two tables together, only nested loops joins can be used for that. And if you don’t have a join within a quality predicate, so like all you clever dim date calendar table developers out there, when you join on that table on like where like the date in some table is greater than or equal to the date in your calendar table, and also less than or equal to the date in your calendar table or something like that, there’s no equality predicate.
There’s no column equals column in that join. So SQL Server can only use nested loops for that. And because of these scenarios, SQL Server can’t just always pick a hash or a merge join or something like that.
So we end up with nested loops because there’s nothing else. If I had to make like two wishes for SQL Server, one would be no more parallel merge joins, and the other would be batch mode nested loops.
I think those would be very nifty additions to SQL Server. Well, one addition and one removal, I guess. So let’s look at some plans that have table spools in them, and let’s see if we can figure out if those are effective spools or not.
Now, what I did was I loaded up a couple 10 tables with a million rows, and the contents of those 10 tables is a little, well, let’s say different. So this is the top million users where the owner user ID in here has 10 or more posts associated with it.
This is ordered by score descending. And then this 10 table down here is loaded up with only owner user IDs who have nine or fewer posts. So just sort of either you do a lot of stuff or you do a little stuff.
But we get a million rows of each of those fun people in each of those 10 tables. Good time so far. And then we’re going to run a couple queries that are almost guaranteed to end up with a lazy table spool in them because we’re using cross-apply, and like I said before in other probably recent videos, cross-apply is just a really neat shorthand to say, give me nested loops, because once again, I find the inner side of nested loops joins quite fascinating.
Every time I see one, I’m like, oh, it’s going to be nice. We’re going to find something good out here. So let’s look at the query plans for both of these.
Now, you’re not going to be happy with the runtime of the first one, right? Because the runtime of the first one is about a minute. And this is why I pre-ran all these queries because, you know, again, I like to respect your time and your wishes.
And who knows when you’re watching this? Who knows what I’m taking you away from while you watch these videos? I’m sure it’s something very important, something very social.
Moments in your life that you would miss and never get back. Don’t want that. But let’s look on, again, inner-sided nested loops. That’s where the fun stuff is, right?
We know how long the query runs for. But we spend a whole lot of time in this branch, right? We can see that a million rows come out of here. And SQL Server does something helpful.
And it sorts all those rows and puts them in order so that it can almost guarantee for itself that every time it hits something that it needs to populate this table spool with down here, we can reuse that table spool for any other duplicates that come along immediately. So we don’t have to worry about, like, going back to, like, you know, when I was showing you, like, the numbers in a row.
It was, like, 10-1s, 10-2s, 10-3s, stuff like that. If those were all jumbled up, using a spool would be far, far less effective because we would be like, oh, well, go fill up the spool for ID1. Crap, it’s ID2.
Okay, go get that one. Oh, we’re back to ID1. Oh, go do that again. It just wouldn’t make sense, right? It would be all out of whack. Wouldn’t be a good time. It would be like, might as well just not even have a spool there at all. And then I would have nothing to talk about.
What moments in life am I missing, right? Oh, this is giving me a crisis here. You know, tender my resignation at Beer Gut Magazine and start spending more time with my family out in nature.
Enjoying sunsets or something. But let’s look a little bit more closely at the spool itself. So a million rows come over here.
They get sorted. And then SQL Server starts using this spool. And I don’t actually know what the lowest number in the spool is. Let’s just say it’s number one.
So SQL Server is like, boom, nested loops join. Pass that ID number one down to the spool. The spool is like, I got no number ones. I don’t know if I know nothing about number one.
Let me go get that for you. So the spool runs this part of the query plan, fills itself up with number one. And then for any additional number ones that show up here, we just reread from what’s in the spool. And if we look at what’s going on in the details of the spool, and let me make this a little bit more sensible.
From a readability perspective, there we go. We can see that this spool actually was pretty effective. I know that’s a slow query, but it’s an effective spool.
We can tell it’s an effective spool by looking at this. I think it was Paul White who came up with a very good way of phrasing the rebinds and rewinds thing. And you can think of a rebind as like a cache miss where the spool has to go get data.
You can think of a rewind as a cache hit where the query can reuse data in the spool. So we had to go execute that other part of the branch 151,570 times. But we reused what was in the spool 848,430 times.
So that’s pretty effective. And I know it doesn’t seem that way because the query still ran for a minute. But wait until I show you what it looks like without the spool.
That’s where things get weird. Now, down here in the table where we have far fewer, the people in the second temp table have far less activity. They have far fewer posts and probably far fewer comments.
And they’re just not as active site users. This query runs for under a second. So if you have a query with a table spool in it and it’s already fast, don’t really worry too much about fixing the spool because it’s not really doing much of anything.
You’re getting off easy. Move on to something that takes longer. Move on to something more meaningful.
Look for queries where they take almost a minute to run. That’s where you should be focusing your time. Don’t spend a lot of time with this stuff. Because no one’s going to celebrate your career.
Spend a lot of time fixing this and ignoring this. Focus on the stuff that matters. But this table spool, if you were to look at this objectively, is not a very effective spool.
Sorry about that. There we go. So this table spool is kind of iffy, right? But it’s still fast.
We have 639,558 cache misses or times we had to go repopulate the spool. You have 360,442 cache hits or times we could reuse stuff in the spool. So we spent, they had way more activity going and running that query.
But it was still fast, right? Again, still fast. So don’t always judge a spool by the rebind and rewind numbers. Because there’s a lot that they leave out of the story.
That’s exactly how much work had to be done in order to go get data to fill that spool up with. So how can you tell if a spool is effective? Well, what you can do is not put the wrong, but don’t put things in the wrong order.
That’s a good start. Like that, too. That’s also in the wrong order. What you can do is, depending on what you prefer, you can use query trace on 86. So I should actually go back a second here.
Query trace on 86.91 means put a spool in there. Use a spool. Like spool away, sir. You need some spooling. All right.
Play some Led Zeppelin or something. And so I specifically use this here because I wanted SQL Server to use a spool no matter what it thought. I think when I was originally writing this, hitting the table with very few useful people in it was not spooling.
So I was like, no, screw it. We’re just going to force the issue because I want to show people what to look for in these plans. So 86.91 says, use the spool.
86.90 says, don’t use the spool. There’s a slightly more accessible query hint. You know, using trace flags requires, you know, elevated permissions and stuff.
There’s a regular option hint called no performance spool that will get rid of table spools for you. Or actually, it will actually get rid of table spools and lazy index spools. It will not get rid of eager index spools for some strange reason.
But when we run both of these queries without the benefit of a spool in them, right, you can see there’s no spool in here. Well, this query goes from taking a minute to taking three minutes, right? So that spool, even though that query was running for a long time, that query ran for almost a full minute, that spool saved us two minutes of time, right?
So clearly that was a good choice for this first query. The second query, if we get rid of the spool, again, we slow down, but only by like 400 or so milliseconds, 300, 400 milliseconds. So it’s not like a big critical thing that will, again, these are not the types of queries you should be focusing on.
You should be focusing on the stuff that takes time. If you’re sitting there trying to experiment and micromanage a query that’s already fast, you’re not a very useful engine, right? You focus elsewhere.
So we don’t really have any great details to look at in here. We can just see that this thing took a long time. And this branch is a full two minutes spent just seeking into this thing over and over and over again, very repetitively going to get a bunch of repetitive data. And no one’s happy, right?
No one’s going to say, hey, good job, man. You got rid of that spool. The query’s three times slower now, but that spool’s gone. It just doesn’t happen.
Maybe, I don’t know, maybe you’re luckier than I am, but that doesn’t happen for me. So one potential way of getting rid of spools is to run your queries, is to change the query a bit like this. And this is a trick.
I forget if I pick this up from Adam Mechanic or Paul White. I can’t recall which one illuminated me to this first. But this can be a generally good way to get rid of a spool in a query plan, right?
By just saying, rather than having SQL Server have to protect itself from a bunch of duplicative values, we can just say select a distinct list of values from the table and then go cross-apply that distinct list of values.
And when we do that, both query, well, this query, you know, is actually just about equally as fast as it was the first time around, but maybe like 100 milliseconds faster. But this one up here really does well, right?
Rather than take three minutes with no spool or one minute with a spool, this takes 1.6 seconds just like on its own, which is a pretty good improvement. The reason why this gets tricky, so like an alternative here might have been like if we just did like a select, rather than populate this 10 table with a million whatever user IDs, we could say like select top million distinct owner user ID into the 10 table.
And that would give us about the same thing. But one thing that gets tricky with that setup is if you’re selecting more than one column, right? Obviously, you could end up with a bunch of duplicative owner user IDs.
Because even like if you like, you know, say select distinct, you could have, you know, unique combinations of other columns that still result in duplicative user IDs. Now you could, if you really felt like experimenting with stuff, do the select distinct from the table to get this part done, and then join back to the 10 table to get other columns out in whatever, you know, arrangement you need them in.
That’s one, that’s one potential solution. But, you know, if you’re the big, if you’re dealing with a lot of big tables that can, you know, might not be the greatest setup.
But if it gets rid of a spool that’s really slowing your plan down, then it’s probably worth it. Now, another way of getting rid of spools is to write the query in a way where different join algorithms are possible, right?
So rather than writing those as a cross apply, if we just write those as a derived join like this, and rather than do our join, or rather than do our correlation inside the apply, we just do our join outside the apply, we can get far different execution plans.
You know, these, I mean, these are a little bit trickier to see how long they ran for, because this says zero seconds, but like almost all of this plan is in batch mode. And so like all these operators are just kind of keeping track of their own time.
They’re not keeping track of like the full plan time, like you’ll see in row mode plans. But we do have a row mode operator down here. And we do have, well, it doesn’t matter if this is row or batch mode, because it kept track of the three seconds of time it spent scanning the index.
And there’s about another like not even 100 milliseconds or so of time in the stream aggregate. So that’s another potential way to get rid of spools in a way that makes the query faster. I want to be very clear about the fact that, like coming back to these two plans here, just getting rid of the spool isn’t enough.
You need to get rid of the spool in a way where it speeds the query up, right? Just getting rid of the spool isn’t always enough for that, right? All right, cool.
So we covered that. Anyway, scroll down to the bottom here. Because I have all these queries pasted in here so I can bring the demo around and show people spoolie stuff. So scroll past a few things.
So if your spool’s rebinds are way higher than the rewinds, or if you’re just generally suspicious of a spool in your plan, a couple ways you can test the query to see if it’s better off without the spool are to either use option no performance spool or to do option query trace on 8690.
What sometimes works is what I was showing you with the queries that do the select distinct list from the temp table. Again, that does get tricky if there’s multiple columns in there that you need to return because now you’re looking at two trips to the temp table.
And, you know, that can also have its own downsides, let’s say. Just a sort of quick example of that. Like, if we select the distinct top 50 from here, but we’re getting owner user ID, post type ID, and score, you know, we grab this data and all of a sudden, you know, well, the query plan doesn’t matter, but if we look at the results, you know, we have a bunch of number twos in here.
Oh, that didn’t go well. We got a bunch of number twos in here, and then you can see where that switches to three right below it. And then we even get a whole bunch of fours in here. So that might not just, that just might not be enough.
Might be a good start to reduce the result set by some amount, but it’s not going to, probably not going to be enough to get rid of the spool because we still end up with, like, big, long, listed duplicate values. Anyway, that’s about enough about spools today.
If you enjoyed this video, I would appreciate you to the back teeth if you liked the video, gave it the old thumbs up, the old internet yeah. If you want to get notified when I post videos like this and try to animatedly and excitedly explain SQL Server performance issues to you, give my channel the old internet bing.
The old subscribe. And then you’ll get those notifications, and then you and me can be best friends forever. Yeah, sound good. Anyway, hope you enjoyed yourselves.
I hope you learned something. Thank you for watching. And I will see you in another video another time. I have two more windows open here, so who knows when I might just have to record something.
Anyway, cool. 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.