The SQL Server Performance Tasting Menu: Tuning Modification Queries With #Temp Tables

Posted on by Erik Darling

CIA

Video Summary

In this video, I delve into the intriguing world of Halloween protection in SQL Server and how it can impact query performance, particularly when dealing with modification queries that require data from the same table being updated. I explore a specific example where an eager table spool is used to handle Halloween protection, leading to significant execution time. To demonstrate a potential solution, I show how staging data in a temp table can bypass this built-in protection and potentially speed up the query by reducing the overhead associated with the spool operation. The video also includes some humorous moments, like an unexpected interruption from a local wine store delivery, which adds a bit of levity to what could otherwise be quite technical content.

Full Transcript

I wish I had someone to do like intro music for these things. I wish that like I was comfortable enough with the rules of copyright to have like a brief sample of a song that I like to do as intro music for these videos. I have a, like I heard somewhere at some point there was like a 30 second rule. Like you could have like 30 seconds of a song and then it’s gone. But there are a lot of bands that I, I mean, I don’t know, let’s still, like at least, used to listen to where 30 seconds would be like a good five, 10 seconds longer than the song. So whatever. I want to talk about Halloween protection and I forget why I call this spooky hand. In retrospect, that’s probably terrible for SEO, isn’t it? It’s got, got nothing to do with hands or spookiness. I mean, I guess, I guess aside from the fact that Halloween is a bit, a bit spooky. Boo. Anyway, uh, the, what’s, what I think is interesting about, um, Halloween protection in some cases is that, uh, it’s, it’s done via an eager table spool. And there are many times when I’ve found that, um, uh, a temp table is a faster approach. In other words, manual Halloween protection versus just letting a SQL Server do its own HP as they call it in the hood. So let’s look at, uh, an example. If I’m going to reset this column back to this being null, that’s, this isn’t the problem we have. Our problem is not with this, this, with this update. This update is fairly straightforward. We’re not, we’re not worried about this.

This update. Um, the reason I have the where one equals one there is because if I don’t, um, SQL prompt will freak out. But if I, if I, if I throw a where one equals one on it, it just runs. So thanks for checking. Ah, the things you do for love. Okay. So we got query plans turned on and I want to show you an example of, um, uh, an update. Well, I mean, it could go for, I mean, I guess it go for a delete as well or an insert. In some, some cases where, um, the values that you need to pull from are also, uh, are stored in the table that, uh, you’re modifying. And in this case, we are setting, uh, the max score column equal to the max score.

Uh, when we look at the max score. Uh, when we look at the max score. Uh, when we look at the question score and answer score in this total score by user table that I totally made up. So, obviously, this, the score in the Stack Overflow database will, will tally up the total question score and answer score for each user. And then the max score column tries to figure out if their question score is higher or their answer score is higher.

Okay. Whatever. It demos well, at least. Uh, but when we run this, you know, since this, this is the table that we, we need to update and it’s also the table with the data in it, SQL Server is going to use a spool to protect us from Halloween. So, throw some holy water on it, I guess. And this thing runs for, I don’t know, like five, almost six seconds, 5.7 seconds.

And if we look at the, the query operators and we kind of tally up where we start spending time. Well, it’s about a second up until this merge join, right? And then we get over to this table spool and we hit 3.4 seconds.

So, that’s about two and a half seconds that we spend in the spool itself, right? Because three, about three, 3.4 minus 1.062. I’m willing, I’m willing to call that two and a half. I’m not interested in arguing about a hundred and something milliseconds with you.

And then, uh, there’s about another 1.3-ish milliseconds, uh, seconds spent actually doing the update. Now, I don’t have any, like, firm, um, you’ve got to do this when type rules because query tuning is a lot about experimentation. Um, you know, there’s all sorts of different, uh, as, as I learned from a very wise Kiwi who is not just full of bird ideas, uh, that, um, there are many local factors involved with query tuning that may make different approaches and techniques worthwhile.

One that I find in this case is if I look at a query plan and I see, uh, an eager table spool in it for a modification query like this, I might, I might try staging data in a temp table myself and then running the update from that temp table. That takes out the need for SQL Server to do Halloween protection because we have a, a separate structure to read from.

That’s what a spool is. It’s a separate structure. And, um, there are all sorts of fun, um, optimizations that, uh, doing things with temp tables can get that, that eager index spools do not get. Uh, if I, if I tried to come up with an exhaustive list, I would probably be here for longer than I want to be.

And, um, I’m not going to do that. What I am going to do is show you a way to have modification queries go faster by avoiding, uh, the, the built-in Halloween protection. Now, uh, what we’re going to do is dump the results of that union all into a temp table.

And, and I just got a phone call from, uh, the, my local wine store that I have placed a delivery from. And, uh, I had to, I had to pick a substitute wine. So if there’s a little hiccup in this video, now you see why.

I didn’t even attempt to make it look seamless. Isn’t that fun? Cool. All right. The wonders of modern technology.

I can order wine, record SQL Server training videos, and, uh, that’s it. That’s all I can do with my life is order wine and record SQL Server training. I was going to come up with another thing, but it doesn’t exist.

So, uh, we’re going to, I already did it. Let’s do it again. Let’s do it again for fun. Let’s stick the results of that query into a temp table. That, that happens pretty quickly. That only takes a second.

All right. So that’s, that’s good news there. And then, uh, let’s update from that temp table. And that should take just about a second and a half. It does. Right? So cool.

So all together, if we run this all at once and one, one big hunk of chunk of munka of burning love, this runs in just about two and a half seconds, one second to do the insert. Oh, actually, no, it’s faster than faster.

Look at that. Look at that. So much faster. So much better. Right? So once about two seconds total, which is awesome because the original took like what? Six and a half seconds. Now we think of all the locking and blocking time we could save ourselves by, by doing manual Halloween protection rather than doing automatic Halloween protection, I guess it would be called. Right?

Manual versus automatic. Get the stick. All right. Whatever. So, um, yeah, uh, whenever, whenever I’m looking at queries and trying to tune them, uh, you know, there are some servers you get into and there’s just massive locking and blocking issues.

You might not be able to, uh, get an optimistic isolation level turned on, or you might be in a situation where, uh, modification queries are blocking each other. Right? It’s not just like readers and writers fighting.

It’s like writers are like, and like optimistic isolation levels don’t help that. No way, no how. Um, I mean, snapshot isolation can sort of, but it’s complicated. And so, uh, there are times when it does help to, uh, reduce the duration of modification queries to reduce locking, to reduce blocking.

And one great way to do that is to use temp tables like I just showed you. Oh, it’s crazy. Oh, it’s insane. Oh.

All right. Cool. Uh, I’m done here. I’m going to go record another video. I’m going to hit save there. I think we’re good. All right.

Cool. Talk to you later.

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