A Very Silly Performance Tuning Trick In SQL Server
Thanks for watching!
Video Summary
In this video, I share a humorous and somewhat unconventional query tuning trick that I had to use while working with a client recently. The scenario involved a simple query in the StackOverflow database that was taking an unusually long time due to a table spool in its execution plan. After trying various methods to eliminate the spool, including using trace flags and hints, none seemed to work as expected. That’s when I decided to take matters into my own hands by converting one of the columns to an Envarkar max type, which surprisingly got rid of the table spool and significantly improved query performance. While this solution is situational and not guaranteed to work in every case, it offers a creative workaround for those facing similar issues without resorting to expert-only hints that might be deemed inappropriate by some.
Full Transcript
Erik Darling here with Darling Data. And in this short, amusing video, I’m going to show you a very stupid query tuning trick that I had to pull while working with a client this week. Yes, you can hire me. I don’t just record YouTube videos and become a millionaire. So the deal was there was a very simple query that looked a bit like this. There was not a very obvious missing index in the one that I was dealing with, but the only way that I could get this to re-pearl locally in the StackOverflow database was to not have a useful index. So, you know, you can just, for now, just ignore the green text behind me, behind my head, this stuff. Because obviously this index would fix this situation. But the issue that I ran into was that SQL Server insisted on having a table spool. In the query plan. And when, with the table spool in the query plan. And I do have to just want to note, this, this is a short video early on Friday. I do have a longer video that I plan on recording later when I have more time. This is just a quick one because again, this was just funny to me. All right. Sometimes, sometimes you’re just going to have to deal with things that I find amusing enough to record videos about. So I’ve got this top query up here. Let me make sure I’m pushing the right buttons or I’ll get in trouble with the FCC. And this, this query runs for 40 and a half seconds. And if you look at, so, you know, just to kind of put things in perspective here. If you look at where time is spent, that’s about 39 minus 13 seconds there. So that’s another 29, 26 seconds is spent in this lazy table spool. And 13 seconds is spent in this clustered index scan.
And the thing that is very amusing about this is that I tried all sorts of things to get rid of the table spool. Now, of course, there’s a trace flag you can use. There’s also a very friendly option, option hint you can use called no performance spool to get this in there. But, you know, they were, you know, hinting queries. Apparently, someone said that it’s for experts only. And even though I got paid as an expert to tune queries, the query hint idea was out. So, you know, I had to, I had to, I had to resort to some extreme measures. And if you look at this bottom query, well, this thing runs for only 13 seconds total.
So, like, just the time that we spent scanning this clustered index in this query without the spool, this thing runs for, I mean, you know, like, I don’t know, like a fourth of the time or something, right? That’s about 10 seconds. That’s about 40 seconds. So, like, let’s just say it’s about a 4x improvement. I don’t feel like doing decimal math right now. Again, it’s Friday. Friday. Why would I want to do math on Friday? And so you might be wondering, what awful trick did I play to get SQL Server to not create a table spool in the query plan? Well, it looks a little bit like this.
This query was selecting some columns up here, right? Owner user ID, score, post type ID ID. Of course, the real query wasn’t selecting these columns because the real query wasn’t in the stack overflow database. That should be obvious to you by now. Stack overflow is not one of my clients, but if anyone from stack overflow is watching, you would like expert query tuning. My doors are open to you. In this query, all I do is I convert that last column to an Envarkar max.
And if I had to put some supposing shoes on, put on my supposing hat, it would be that the presence of a max data type made the idea of creating a table spool up in tempDB with a max column a bit overwhelming. And it costed that decision away. It just got rid of it. Threw it out, said, you’re too expensive. I don’t want you. There’s no coupons on Varkar max columns today. We’re just going to have to do this the old-fashioned way without a spool. And it ended up turning out quite well.
Now, this was a very situational thing. I’m not going to promise that every time you see a table spool, if you cast a column to an Envarkar max, that you’ll get rid of the spool and things will be better. There are all sorts of optimizer-costing things and logics that are going to have to go into that that may not work out in your favor. But if you do find yourself staring down a query with a table spool in it, and that table spool takes an excessive amount of time, and you have tricks to test out the query without the table spool, this is one way that you could get rid of it without having to put a hint in there that someone will say, I don’t think you’re expert enough to use that hint. I don’t think you quite have the credentials.
Can I see your certification list? Because, you know, those Microsoft certifications are so meaningful. I’m so happy that you got a DP. It’s great for you. We all dare to dream, don’t we? Anyway, I have a call starting soon that I’m going to get to.
So I’m going to wrap this up and I’m going to post it. And then a little bit later today, this Friday, lovely Friday, this 10 out of 10 Friday, weather-wise at least. The rest of it is, you know, up to some whims and fancies, but at least this Friday is pretty good weather-wise.
So I’m going to post this and then I’m going to post another thing later about different things that you might see in parallel nested loops joints, because interpreting those parallel nested loops joints plans is quite difficult. I’ve talked about them a few times, but never quite in this way.
So I hope that you’ll stick with me. So I hope you enjoyed yourselves. I hope you found this amusing. You may have learned something. You may have not.
I don’t know. But if you like this video, give it a thumbs up or a nice smiley face little comment. If you like this sort of SQL Server performance tuning advice, kind of as silly on the face of it as it may seem, you can subscribe to my channel and join…
Let me get the most up-to-date number here. Nearly 3,503 other people. Nearly. We’re one away. And you can also get…
You’ll be one of those 3,500 and whatever people to get notified when I post these things. They’re usually much higher quality than this. This one…
This honestly just made me jump into Giggle Bush. And when I’m into Giggle Bush, I want to record stuff. So I hope you jumped into Giggle Bush with me. Maybe… Maybe…
It’s nice to have company in the Giggle Bush. Right? Giggles last longer when you have someone to tickle. That’s the rumor anyway. So anyway. Thank you for watching. You can…
Let’s see. Like, subscribe, Giggle Bush. I think I covered everything. All right. Cool. Time to go do some actual work. And then we’ll have some more fun later talking about parallel nested loops. 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.