SQL Server Performance Office Hours Episode 50

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 50


Chapters 

To ask your questions, head over here.

## Full Transcript

Erik Darling here with Darling Data. Another exciting Monday is upon us, which means another exciting, thrilling, breathtaking, blockbuster, record-breaking, I already said that, maybe, I forget, episode of Office Hours is upon us at long last. Here is your chance to shine, SQL Server community. I answer five questions every week doing this. So you could have your question asked and potentially even answered. You know, no voting required. You just stick it in there, I’m gonna read it. So you could throw your question in there and I’ll do it. Down in the video description, you can figure out how to do that.

I had to update the link sort of recently. There was a weird snafu with a redirect plug-in on my WordPress site. Apparently, the dash in there, I was redirecting it to blog posts about office hours instead of going to the page where you can ask questions. Maybe that would explain why there’s a slight drop-off in questions for a minute, but it’s fixed now, so you can do that.

Down the link, down in the video description, there’s all sorts of other links. I’ve redone my training site quite a bit. There’s all sorts of new consulting offerings there. There’s all sorts of new training offerings there. So you should check that out and click on things and give me money.

As always, you should like, subscribe and tell a friend too, because, you know, I do like to see the numbers go up when I hit refresh. Obsessively, my YouTube app, looking for comments and numbers going up. Life as hell.

So, look, I had a couple of screw ups on this the last time. I forgot to change the dates for Croatia. The dates for Croatia are now correct. I will not be in Croatia and somewhere else on the same dates anymore.

You know, copy and paste. Look, I make a lot of copy and paste mistakes in my life. It’s just my way of living. So I live with it. I deal with it. I move on. Croatia is now completely aligned and correct.

I will be at all these places and I will be delivering my advanced T-SQL pre-con. Of course, all attendees get free access to the Learn T-SQL with Erik course, which is the backing material to everything I’ll be teaching. You know, we only I only have like six and a half hours for these pre-con days to tell people stuff, but I have like way more material than that.

And if you just a quick note, if you have purchased the Learn T-SQL course, there’s a bunch of new advanced material in there that I’ve been I’ve been getting out as tech review completes. So if you have it and you were looking forward to the advanced material, maybe you even completed the beginner material. It’s all it’s it’s there’s a bunch of new stuff there if you haven’t picked up the course yet.

Now’s a good time because like we’re like getting close to the end there. Right. It’s almost all completely done. Anyway, still January still 2026, at least as far as I can tell.

So we are. Well, I mean, at least I am locally still very, very cold. It is currently. Let me take a look here. How many degrees is it? Not a lot of them. I’m going to tell you that much. It is currently 31 degrees.

It feels a lot feels it felt a lot colder outside. I’ll tell you. Well, Saturday is a high of 16. Eat my life. All right. Let’s answer some questions while we’re nice and cozy and toasty indoors here.

Do to do. First question. How can I find out why the clustered index update node in an execution plan is slow? I have this problem on many servers, so I just need the general principles.

Well, if you are hopefully judging this by getting an actual execution plan and not looking at just simply at costs or percentages, you could you could try looking at at the weight stats for the query that might give you some like if you like right click on like the fine, like the root operator in the plan, you go to properties, helpful little properties tab opens up on the right side of the screen, which should if you get an actual execution plan have weight stats in it.

That’s that’s that’s one way to find out. Another way, if you know, if you because, you know, I’ll be honest and Microsoft lies about a lot of stuff in there, right? It’s like, yeah, or they don’t include everything they should.

You could also like run sys dot query against sys dot DM exec session weight stats, which would show you the weight stats for your session. So you could run the update and look at that.

If nothing obvious sticks out there, then it could be something behind the scenes that, that is going on. Other things that you might want to look at is if you hover over the clustered index update, you might see that there are lots of nonclustered indexes listed in there, right?

Because it’s going to say like object and it’s going to tell you all the objects. SQL Sentry plan Explorer back before SolarWinds bought it and ruined everything that the SQL Sentry company created. Used to have a really nice thing where if you had a clustered index update that updated multiple nonclustered indexes under it, it would actually like tell you like clustered index plus number of nonclustered indexes, which is fantastic, right?

Because you can very easily see it’s like, oh, clustered index plus 17 nonclustered indexes. Well, no wonder. It’s a lot of running around that might be going on in there. Um, you know, if, if it were something like maybe a, um, if it were something like maybe, what do you call it?

Um, like, like, it’s probably not going to be, I mean, I guess it could be a trigger. Uh, I would look for triggers. If it were an index view, that would show up in the query plan.

If it were foreign keys, that would show up in the query plan. So I guess you could look at triggers on the table. Uh, like, like aside from that, uh, other things that you might look at are, um, you know, like really like kind of depending on how many rows you’re updating.

Um, you may find that, um, like there’s just a lot of background activity going on. Um, you might consider, you know, unfortunately you might have to look at like, uh, like perfmon counters or something for like page splits or, um, like any, like, like writing to the transaction logs, stuff like that.

Um, you know, just lots of general sort of other stuff going on. Um, so like you might not get the answer from the query plan, but you could certainly get answers by looking at the, the query plan and some sort of like correlating surrounding statistics and also check for triggers on the table as well.

All right. Let’s see here. Oh boy. As a developer calling me a developer.

Flattery will get you everywhere. Uh, how do you deal with looking into one bug or validating a new feature and finding other features that are unrelated, finding other issues that are unrelated along the way?

Uh, boy, that’s a, that’s a tough one. I am not good at that. Uh, like anytime I’m looking at like my, like one of my store procedures, just basically like the, like, like, like the extent of my, like, I’m a developer work. Uh, man, I, I get lost in so much stuff.

Um, it could be anything from just like, Oh, that formatting annoys me. Now I got to fix that. Um, or like, like, what is this? Like, why am I doing this this way? I got to fix that.

Um, like, like, like, like, uh, I get, I get so lost. So like, it really depends. Um, if, if I am just meandering a little bit and I like spot a few, like, I don’t know, let’s just call them like superficial things. I’ll just, I’ll usually just crack them out along the way.

If I spot something that I like, like knowing how I am, almost everything turns into a terrible rabbit hole. So, uh, usually I will, I will just add a to do and move on. Um, like, I will come back to that to do almost immediately after doing my other stuff.

But, uh, I know that if I, if I start, if I stop everywhere along the way that, um, like, like, like causes me to like, like pause and like have interest or like, you know, like mentally jars me a little bit, I will never get to the thing that I want to get done. So really it’s just, it’s about like, just sort of managing your, like whatever mental thing you have that, uh, that, that like, you know, forces you to analyze things as like, you can’t just ignore things and get to where you’re going. Uh, it’s really just about managing that.

Uh, and you know, make like noting stuff that you want to look at later. Um, you know, like, like, you don’t have to write a book about it. Just be like, to do like, like short blurb or something like that.

Uh, like there’s, there’s probably better ways of doing that. But like, if I, if I, if I started like context switching and like, if I had, like, if I was looking at this stuff and I had to like make a note somewhere else, like open an issue, forget it. Like, like it would just wouldn’t be unmanageable.

So like, usually for me, it’s just a short, like, like, like all caps to do colon, like, figure this out. Like, what is this? Like, fix this later. Like, don’t be an idiot. Why are you doing things this way?

So that’s, that’s about, that’s about all the advice I have. There’s probably much better advice out there. Uh, I, I don’t have anything better than that. Let’s see.

We updated statistics and the plan didn’t improve. These things happen. Isn’t that supposed to fix bad estimates? Well, it, you know, uh, to a degree, you, you might fix a, a root cardinality estimate, assuming that your query is written in a way that allows SQL Server to make a good estimate in the first place.

Uh, you know, there are all sorts of things that may get in the way of SQL Server making a good estimate. Uh, you know, non-sargable predicates, local variables, table variables, uh, you know, a lot of different things. Um, sometimes query complexity just sort of gets in the way of SQL Server making a reasonable estimate on things.

Um, you might try, uh, different cardinality estimation models. You have the default as Microsoft calls it, a lot of hubris in that, but there is a default cardinality estimator and the legacy cardinality estimator. You might try that.

There are also all sorts of use hints to, um, change different things about the way cardinality estimation works. But a lot of that stuff is, I mean, it can’t, I’m not going to say it’s overkill. Like if, if you are really invested in like figuring out why a cardinality estimate is bad, there are all sorts of use hints that you could like, you could do that.

Like, like assume min selectivity and like other, other things like that. The other thing to consider though, is that, you know, cardinality estimation can really only be good at like the table access. As soon as you move past that and you get into like, you know, like any like grouping situation, like, like any aggregate, uh, you start joining stuff together.

Uh, you start like mixing, like all these cardinality estimates. Things can get really weird, especially because, you know, the optimizer, you know, like, like the final query plan that you see might, might be like a Frankenstein of a plan. Right. Cause like SQL Server might like, like cost and reshuffle and do all sorts of other things along the way.

And like, you might have this weird staple together plan that like, has like almost looks like, like just deranged. Uh, I remember, you know, um, when I was first started looking at query plans, I would see these things were like, like the, like the num, like the estimated numbers would just be like crazy all over the place. We’re like, just like in like, like an inconsistent ways.

And it’s just because the optimizer, like one branch of a plan might get a cardinality estimate. And then SQL Server might like, like keep like this part of the plan, but then like change something in this part of the plan. And then the cardinality estimate down here might change and look weird up here.

Like there’s just all sorts of stuff that, that can go on. Um, so like, you know, like fixed bad estimates. Yeah. But there’s only like, so deep, like, like, like it’s only like one point in the plan.

Can you really see the fruit of that? Like after that, you know, like, like if estimates were way off here and you improve estimates way off here, you might improve estimates downstream. But, um, like, you know, just, just because like, you might’ve had good estimates in general way over here, but then it was like stuff that happened at later stages in the plan that, uh, that, that, you know, through cardinality estimates off.

So, um, you know, like, like, yeah, there’s, there’s, there is an element of, you know, we updated statistics and, you know, uh, where there’s an element of disappointment to updating statistics and nothing improving or like, you know, like the plan not getting meaningfully better in some way. But like, there’s all sorts of things that you might be doing that might be screwing SQL Server up. And there’s all sorts of things that like, just due to like query or plan complexity might like, you know, just, just, you’re just getting bad estimates because at some point SQL Server kind of gave up, right?

It was just like good enough plan found or like optimization timeout or something like that. So, uh, you know, just, it only goes so far at some point you have to dissect things a little bit. Uh, you know, like if you’re still getting like real bad estimates from stuff and the query is like, like complex past a certain point, that might be, it might be a good time to like start breaking the query up into some component parts.

Like if you’re selecting from two tables and joining them together and each of those tables, like has like occupies some space in a where clause, like just try like, like isolating each individual query. Like you might try putting one thing into a temp table, like going from there, uh, maybe even both things into a temp table. So SQL Server has a materialized result to work off of, uh, you know, like updating statistics and only do so much in a plan that is, you know, past a certain point of complexity.

All right. We created all the indexes suggested by the missing index DMVs. You, uh, but performance got worse.

Yeah. Okay. Why would SQL Server recommend bad indexes? Uh, well, maybe SQL Server didn’t recommend like terrible indexes.

Um, you know, maybe SQL Server was just doing its best. Right. Like, like imagine if every time you looked at a query, you thought to yourself, Oh, like maybe this index would be okay, but you had to do it really fast.

Right. So like, like SQL servers, missing index requests generally care about two things. Uh, one, um, making data easy to locate.

That’s the where clause in two, um, not having to do key lookups. So that is a select list, all sorts of other stuff that might be useful. And as well as in an index, like especially key columns, like, you know, order by group by, um, you know, if you have a windowing function, then the partition by order by in there.

Uh, there’s all sorts of things that would make sense. Um, even like join keys, like the SQL Server doesn’t, you know, put join keys in the, the key of an index, right? It’s just the where clause stuff.

So there are all sorts of things that, um, you know, like missing indexes, miss missing index requests, miss that you as a human being would do better at. The problem is it like, like, this isn’t like, this isn’t like an indictment of the missing index requests. Like I’d rather have, I could rather have one show up just so I can be like, Hey, maybe something is wrong here.

Um, but when you see missing index requests, you should never just like look at the DMVs and implement them all. Like, like that’s just a, that’s a real bad idea. What you, what you should be doing is just looking for long running queries.

All right. Like look for your slow queries. And then like, you know, if there is a missing index request or more, more than one in the, in the query plan, um, see if those missing index requests align with. You know, like data access methods, like index seek or scan or whatever, uh, that are also slow.

Cause like that, like, but you know, also like, you don’t just create what the missing index request says. Like use a little, use a little bit of your gray matter on that one. All right.

Like, like, like look, like look a little bit more, uh, closely at the query. So it’s not that, it’s not that the index recommendations are necessarily bad. I mean, some of the, they’re, they’re certainly not like, you know, um, what a senior person might, uh, recommend for an index. But I think they are a good enough sign that you should look into things that like, you know, like, but that’s like on a query by query basis.

That is not at the DMV level, right? You can correlate your, like the missing index requests or requests that you see in a query plan to what’s in the DMVs. But I certainly wouldn’t just go off the DMVs because SQL Server might recommend a missing index on it.

Like, like happens all the time in the stack overflow database when I’m doing demos, you know, like there will be a scan of the user’s table that takes like 80 milliseconds. But SQL Server is like missing index, like 98% impact. Like it’s added and we’re like, we’re going home boys.

That’s not the case. Right? So careful with that. I have a five table join. Do you now?

Well, call me later. Uh, that shows statement optimum level full and reason for early termination, early for termination timeout in the plan XML. But the query only has simple equality joins and returns 50 rows.

Well, uh, I feel like I’ve said this before, but I’ll say it again quickly. Cause this, this is 20 minutes of my life. I’ll never get back.

Uh, just kidding. I love these 20 minutes. Um, uh, timing out does not necessarily mean that SQL Server, uh, spent some amount of time doing something and then gave up. What, uh, what the optimization timeout means is it very early on in query optimization based on some, like, you know, early, like, like, it’s just like a heuristic view of your query, you know, based on complexity, number of joins, group buys, all the other stuff that goes on in a query.

SQL Server will give that query sort of an initial cost starting point, right? It’s not going to be the final cost and, you know, costs are all estimates anyway. So, you know, it’s all, it’s all kind of a, all kind of a wild, wild goose chase in the dark with the stab or something.

But, um, it, what it’ll do is it will a lot, a certain number of steps that it is willing to take in order to, um, in order to, uh, like come up with a query plan for you. And the, the, the, the, the reason for early termination being a timeout means that SQL Server has gone through all of the steps that it is willing to take in order to come up with a query plan. And you get whatever the cheapest one that it currently has is after those steps have, um, have, have been taken.

So, um, you know, like regardless of, you know, your simple equality joins or only returning 50 rows, SQL Server doesn’t know that it only returns 50 rows. When you’re, when it’s coming up with a query plan, right? Like it might estimate something different to begin with, but like, it doesn’t know that the final thing will always be exactly 50 rows.

So, uh, you know, like, that’s not really a point there, but, uh, like it, you know, seeing a, a five table join hit a reason for early termination timeout probably just means that your, your starting query, you know, was because it has, you know, just simple equality joins. And maybe the table is relatively small. Um, it could have a relatively low, um, starting cost heuristically.

And it might not because of that, you know, because that low starting cost, uh, SQL Server just might not be willing to invest all that many steps in coming up with alternative plans for it. So that’s probably good. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something and I will see you. Well, I’ll see you tomorrow, but I’ll also see you next Monday for another office hours episode. Thank you.

Oh, so very much 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.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *