Erik Being Allergic To Slow SQL Server Queries

Erik Being Allergic To Slow SQL Server Queries

---

Thanks for watching!

Video Summary

In this video, I dive into the world of SQL Server performance troubleshooting by walking through a practical example using stored procedures and table variables. I explore how different compatibility levels can affect query plans and performance, demonstrating both the benefits and drawbacks of using table variables versus temporary tables. By experimenting with index creation and reordering queries, I show you how to optimize your code step-by-step to achieve better performance. This is a day in the life of what I do for a living—analyzing and improving SQL Server performance issues. Whether you’re just starting out or looking to refine your skills as a performance tuner, there are plenty of takeaways here that can help you tackle real-world challenges. So, if you enjoyed this video, don’t forget to give it a thumbs up and leave a comment with any questions or insights!

Full Transcript

Erik Darling here with Darling Data. I wasn’t actually sure that the record button worked on that. Things are a little touch and go for a minute. And I have finally gotten my awful nerd allergies subdued to the point where I think I can make it through an entire video without anything terrible happening. We’re going to see if that actually works. We’re going to see if that happens. If it doesn’t, this is going to happen. I don’t mind. I don’t get too attached to these things. I upload them. I delete them from my hard drive. They’re on YouTube. If anything ever happens to YouTube, it just wasn’t meant to be, I suppose. So in today’s video, we’re going to talk about all the different angles of performance troubleshooting stuff, right? Because that’s what I do for a living. I trouble shooting. I trouble shooting SQL Server performance issues. And, you know, I walk into a lot of situations where maybe not a lot of things were done in a great way. And it’s my job to unravel them. It’s my job to figure out what, what, you know, not every single thing that is unaligned to best practices needs to be fixed. But the ones that are causing the problems certainly do.

So let’s just pretend that we have a store procedure that we’re troubleshooting. We’re not actually going to run this as a store procedure. We’re just going to run this as some ad hoc code because it’s quite frankly just good enough. All right. So we have two queries here. We have one that inserts some rows into a table variable called way pops, which if you’re not if you’re not hipping with it, like old E. Darls over here, that’s what the kids say for way populars. Way pops. They don’t. It’s not. It’s not my kids saying go away, pops or random kids on the street thing.

Go away, pops. You’re weird and covered in tattoos and kids of our generation don’t care for tattoos. Way popular. And then what we do is we do some work with that table variable. In this case, I’ve chosen to cross apply to it with the select top one query as our sort of villain.

And, you know, you can you can call this sort of stuff unrealistic or unreasonable and you can say, I know all this stuff. But not everyone does. So and you notice down here that I have for the moment. I have the optimizer compatibility level hinted to 140.

Of course, because I’m using SQL Server Developer Edition because this Microsoft promises is not production data. You know, don’t audit me and start trying to charge me seven grand a core for this. It’s not going to be worth your time. I’ll fight you.

Because under Compat Level 150 in Developer Edition, which is equivalent to Enterprise Edition, table variables do get treated a little bit differently. We’re going to look at both ways. Don’t worry. So I’m going to take advantage of kind of a neat thing that SSMS added where I can execute the current batch.

Let’s shift alt and F5 for anyone playing along at home. And watch what this does. It’s going to turn the two queries under this booger green.

That’s not even like go Celtics green. That’s just like you’re sick green. And this is going to run for almost to say about 10 seconds.

And here’s what the query plans look like. Let’s bring that up a little bit so we can see both things in full detail. We have an insert into the table variable that all in all, all told, takes about 1.6 seconds. And then we have a query down here that takes about 8 seconds.

And we can see there’s not a lot of time spent in this part, 51 milliseconds. But this is where SQL Server chooses to say, Ah, this is where we need an index on the users table.

That’ll solve all our problems. All right, we can reduce this query’s impact by 58%. If only we had this index on the users table.

We can see looking at this thing that we spent, you know, 8 seconds in here. A lot of it’s scanning our table variable. That’s not a good time, is it?

Why would we spend 8 seconds doing that? SQL Server, come on now. Come on now, SQL Server. What’s wrong with you? So let’s just look at that real quick under compat level 150.

So under compat level 150, one thing is going to change. Exactly one thing. If I execute the current batch, it’s going to go a little bit faster.

Not like, you know, saves the day faster, but about twice as fast. So like 4 seconds instead of like 8 seconds. And this query in here did get better, right? Like this query is still about the same at 1.6 seconds.

But this query all of a sudden has signs that, well, perhaps this missing index request is not where we should be focusing our time. Perhaps an index is missing on our table variable because we have an index spool coming off our table variable. SQL Server has taken its just, you know, magnificent big brain and said, I’m going to take this temporary object and I’m going to create a temporary index in tempDB after I’ve scanned all the rows from our temporary object.

And then we’re going to use that index instead, which is not a terrible strategy because we’re down from 8 seconds to 2.5 seconds, right? So, you know, in this case, index spool kind of worked out in our favor. But that’s not good enough, right?

We still have this thing that runs for like, I don’t know, let’s see, 1.6 plus 2.3. I don’t know, let’s put 4 seconds. We don’t, it’s not good enough for us.

We are professional performance tuners. I don’t know who you think you’re messing with here. So the eager index pool is certainly a sign that we should have an index around somewhere. So let’s try indexing our table variable, right?

So we’re going to, since we can’t create an index after the fact, we’re going to create one in line. So let’s just say index C clustered user ID and then date. And then since we’re ordering by stuff by date descending down here, let’s give it a shot with date descending, the clustered index.

And we’re going to switch this back to 140 because, you know, not a lot of people I know are operating under 150. But we’re going to look at both because we are, if we are one thing here at Darling Data, we are fair. We are fair to everybody.

Mostly. Except scale our UDFs. Kind of unfair to them. So now let’s execute this and see what happens.

And, you know, that’s two seconds. So something got better, right? So down in this query, I mean, SQL Server is still like, we need an index on users, which is, you know, stupid at this point. 183 milliseconds.

And we spend about one second in here. But now this got a little bit worse. This got about 300 milliseconds slower. And the query plan for the insert changed, didn’t it? Right?

So let’s, what happened? We now have this sort in here. And this sort spills a little. We can tell by the little, little bangy thing here that this sort, this sort spilled off to disk. Maybe that’s why it’s slow.

Maybe it’s not. Maybe that’s just, you know, maybe that’s just how much time it took. The spill wasn’t too devastating, right? If we look at what happened in here. We spilled 3,900 pages.

That’s not so much. You know, I don’t think, I don’t think that’s what’s, I don’t think that’s what’s slowing us down tremendously. So I think we’re going to need to rethink how this insert is structured.

Right? Like, what are we doing in here? Right? Because now all of a sudden, so we have this index on the badges table.

Should probably, probably tell you about that. We have this index on the badges table. Right? Name, user ID, and date. And since we’re seeking to the name in the badges table here.

Right? Name and yada, yada, yada. Yeah. Well, that index should present everything in a reasonably orderly fashion. So let’s take a look at why we are now sorting things to get, put data into the clustered indexes.

Remember, indexes sort data, and it’s the kind of thing that, the kind of thing they do that makes other stuff fast. Remember, the query we have down here now with all our data nice and indexed and sorted is pretty good. Like, we still have a crappy estimate, but in this case, like, it’s, you know, such a small number of rows that we just don’t, we just don’t need to care too much about that one.

So, all right. How can we rewrite this insert in a way that will maybe be a little bit less painful? So, let’s take this, and let’s actually do this twice.

Actually, let’s see. What’s the best way to do this? I’m going to type in a demo and screw with all your heads. So now I just need to figure out how I want to do this.

So, let’s take this part. Let’s get rid of this. I’m going to need this right now. We’re going to say equals, and then we’re going to put that there. We’re going to say equals, and we’re going to put that there and say equals, and then we’re going to get crazy.

No, not that crazy. Union all. There we go.

Let’s put a couple union alls in between these things. And now let’s dandy up our where clauses. I’m going to put one there, and then we’re going to put one here, and we’re going to forget an end quote, and SQL prompt is going to put an extra one in, and then we’re going to take this one and put that there.

Now we can finally delete this, this empty shell of a query. All right. Let’s just see what happens.

Let’s see. Oh, no. We should do. There we go. Terminate your queries properly, everyone. So let’s do this.

And we’re still at like two seconds. And now instead of one sort, we have three sorts. There’s one.

There’s two. And this one in the middle still spills. So maybe our index definition is working against us. So let’s actually take the descending out of here.

Let’s see how far this gets us. So rerun this. And now, well, things got a little snappier.

All right. So the index order that we have for the badges table, right, we are now fully preserving that index order. All right.

We seek to the name in all three of these things. And because we seek separately to the name in all three, we now have the data in order by user ID and date descending. All right.

So 1.6 seconds. It’s still not great. But, you know, like this query is still going to be fast. You know, 180-something milliseconds. So let’s think about this a little bit differently.

All right. Let’s maybe think about our good old friend the temp table. All right. So let’s leave this where it is.

But I’m going to steal this part. And what I’m going to do is just down here, rather than declaring anything here, let’s create a table here. And let’s replace that with the pound or hash sign, whatever you want to call it.

And now let’s do an insert into that. Oh, I hit the wrong button. I don’t know.

Got all ahead of myself. Now let’s try that out. All right. So now we have two inserts, right? We’re just testing the inserts right now.

We’re going to test the insert into the table variable versus the insert into the temp table. And now let’s see how these two. Let’s drag race these two.

Oh, man. What did I do wrong? I forgot to drop table if exists. What an amateur. Drop table if exists.

Way pops. Go away, pops. All right. Let’s try that again. Error free this time.

It’s running. It did its thing. And let’s look at the difference between these two. So the first query, the insert into the table variable, takes about twice as long as the insert into the temp table.

Now, if you’re like me and this sort of thing interests you greatly, you might go on, you know, your SQL Server and you might fire up PerfView or Windows Performance Recorder and you might grab CPU stacks for when this stuff is happening. And you might look at flame graphs and all sorts of other things to figure out why one thing is so much different than the other. But the short answer is that the insert into an empty clustered index on a temp table gets all of like the fast loading minimal logging stuff that if you are a big fan of wine distributors from New Zealand, you may have read various blog posts about.

And that’s something that the table variable doesn’t get. It has never gotten. So in this case, something that I’ve talked about many times in the past is that table variables make queries ineligible for parallel execution plans.

That’s true all across all of everything, unless you play weird tricks on your table variable like inserting from like executing dynamic SQL or inserting from executing the store procedure, which I don’t recommend doing the second one because that has some weird locking stuff that happens. I recorded a video about that that you should probably watch and catch up on if you haven’t done that yet. But there’s no parallel plan difference here.

These are both single threaded plans. The only difference is that the data load into the temp table is far more optimized than the data load into the table variable. So let’s just get rid of the table variable.

We’re just going to quote this thing out now because we have once again determined that temp tables reign supreme when it comes to performance. Don’t worry. They don’t always.

Certainly times and places for table variables. It’s up to you to figure that out. I can’t tell you every time you should use something.

But so let’s see here. We’ve got our drop table. We’ve got our create table. We’ve got our insert. And then we need to fix this query a little bit, don’t we? We need to make that point to the temp table that we created.

And now let’s start this over a little bit. And let’s do our fancy trick here where we execute the current batch. And now we have everything rocking and rolling pretty okay, don’t we?

I think we did a pretty good job here. We got the initial insert query down from a couple seconds down to just under a second. And we got the final select down from eight seconds to just under 200 milliseconds.

And we didn’t need to create this index on the user’s table to get there, did we? So this is sort of a stock and standard approach to query tuning.

You fix little bits until you’ve got a whole thing that performs pretty well. All right. And this is what I do all day, every day for money.

Weird, right? Get a piece of code, make it go faster. It’s a darling date away.

So anyway, I hope you enjoyed yourselves. I hope you learned something. I’m moderately amazed. It’s actually sort of a miracle that I have not had one single allergic symptom during this entire, so far, 17 minutes.

I have been holding back a little bit because I’m afraid that if I do anything too sudden, I might just break into a sneezing fit. And you wouldn’t enjoy that. You wouldn’t like that at all.

So this is just a couple things that you can look at and do. Hopefully some lessons learned in here. For all you aspiring performance tuners out there on YouTube. So yeah, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this video, thumbs ups are appreciated as are nice comments. Boy, that Eric darling sure is spiffy.

Wish he was my dad. I would never tell him to go away pops. Something like that. If you like this sort of SQL Server content about performance tuning, which apparently some people do, you can join nearly 3,609 other satisfied customers of a free product and subscribe to the channel and get notified every single time I post something.

And normally when I post things, my voice doesn’t sound like scratchy box and I’m not awaiting some terrible allergic disaster befalling me in the middle of recording. So I promise the other videos are a little bit more lively.

So yeah, so we covered the like, the subscribe, the thanks. I think that’s about it. I do appreciate you spending your time choosing to learn from me.

And I will see you in the next video. All right. 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.