SQL Server Performance Office Hours Episode 57

Erik Darling here, Darling Data. My contractual obligation to you, my adoring audience. It is Monday and so we are going to do office hours and I am going to answer five of your questions that are burdening you and I seek to unburden you from these questions. Sort of like a sin eater for databases, I guess. Down in the video description are all sorts of helpful links. Ways that you can interact with me and ways that you can’t interact with a robot, if you get my drift. You can hire me for consulting, you can buy my training, you can become a supporting member of the channel, or you can continue what you’ve been doing so graciously and asking me office hours questions for free. And if you find this content to be at all enthralling, lightening, maybe just seeing me on the screen burdens the load you feel in your life day to day, subscribe, subscribe, and tell a friend because I have all sorts of people who I would like to unburden. All right, let’s get right out of here. If you want free SQL Server monitoring, you should check out my GitHub repo. There’s a link to it down in the video description as well. It’s a very useful link section. I think probably the most useful link section on the internet if we were going to start measuring things. But it is totally free, totally open source. You don’t even have to put in an email address. It’s not phoning home telling me what it’s doing. It’s just a bunch of T-SQL collectors, things that I would normally run if I were doing a consulting engagement, except it spits it out into all these pretty charts and graphs. It also powers a bunch of, you know, dashboards. And, you know, if you’re the type of person who does enjoy chatting with robots about stuff, there’s an opt-in MCP server that you can use that can take advantage of well-defined and articulated rules.

for talking to your performance data and just your performance data. It works a whole lot better than just saying, hey, MCP, go look at my whole SQL Server and tell me what’s wrong. Because it’s focused, it’s over time, it’s nice and broken out. I’m telling you, my war on big monitoring continues and they have earned this comeuppance. So, help me bring down the big monitoring industrial complex and go download your free copy today. I will be out in the world even more. As soon as two dates disappear, two dates reappear. It’s amazing, right? How does it happen? How does Eric do it?

A lot of Bloody Marys is the answer. I will be at SQL Day Poland, May 11th through 13th. That spans, the 12th is included in there, right? The 12th is not optional, right? The 12th is happening. I will also be at Data Saturday, Croatia, June 12th and 13th. I’ve never been to Croatia before, but that should be fun. Pass on tour. I’ll be back in Chicago. I just left Chicago. Boy is my liver tired. I’ll be there May 7th and 8th and then past Summit in Seattle, Washington, November 9th through 11th.

I guess the 10th is not optional in there either. So, we’ll do that whole thing. Anyway, thank you for watching. I’m just kidding. It is. It is finally getting… You know what? It got nice and then it got crappy again and I just feel like God is out there toying with my emotions. I am not hip. I’m not hip to this winter situation we’ve got going on. You know, it’s ugly.

Anyway, let’s answer some questions because apparently it is what the people desire is questions being answered. Up first, what do we have here? Have you ever… I’m not clicking on that link. Have you ever seen a situation where users are forced to use nested join logic for performance gain? Is there a better way to achieve the performance gain without the somehow valid but fugly syntax of nested joins?

See Reddit posts. See your own Reddit post. I only see my Reddit posts where I tell people to download my free things and sometimes it works. So, yeah, of course I’ve seen those situations. I’ve seen a lot over the years. I’m a grizzled veteran and I’ve seen all sorts of terrible things. You’re asking about alternatives that aren’t ugly? Well, I mean, think about other ways that you would reshape a query.

You might, you know, join two tables together and put the results into a temp table and then use that materialized set of data to join off and do other things. Right? There are other ways to reshape a query, of course, but that’s just a very convenient one for some circumstances where you have to do everything kind of all in one go. Another convenient way of doing that would be using intra-query row goals, you know, using a top operator or offset fetch in order to sort of put a logical fence around certain operations to sort of make them happen in a certain time, space, place and way.

Those are, you know, just think of it as any other query tuning exercise, right? Yeah. SQL Server is doing something dumb. What do I do? Okay. I mean, granted, it’s not going to be like, you know, maybe it’s not going to be an indexing problem in these cases, but it is certainly ugly, but legitimate.

Sort of like some, some people I know. I messed that one up. Nevermind. Anyway, when looking at an actual execution plan, how do you know that it is time to try the legacy cardinality estimator? Because it’s a cheap gamble. Man, who do you think you are?

Legacy cardinality estimator has been fantastic at many things for many years. It is not a cheap gamble. If you are using the default, as Microsoft calls it, the so-called default cardinality estimator, you know, it might do okay. But nine times out of 10, if I am seeing particularly lousy join cardinality estimates, then that’s, that’s, I know, I know it’s legacy time.

But no, I don’t think it’s a cheap gamble. It’s not always an effective gamble, right? It’s not always a winning gamble. But it is certainly not a cheap gamble if you are using the so-called default cardinality estimator to give the legacy cardinality estimator a spin and see how it turns out. Because you just might find that a lot of those queries, I don’t know, maybe, maybe they’re just legacy queries that do better.

You know, I just, I find that it’s, it really does come, I think, for me mostly, yeah, it probably does come down to seeing really bad join estimates and sometimes really bad join choices in, with the default cardinality estimator where I’m like, alright, legacy, get your leggy butt out here. Let’s, let’s see what, let’s see how you do, right? Alright, moving on here.

Have you ever seen SQL Server audit kill a server? If it did, who would catch it? I only want to monitor some sysadmins, but I worry that my big OLTP workload might be impacted.

I don’t do a lot with SQL audit. I’ve, you know, of course seen it on a lot of servers. Most people who I say, hey, what’s that audit auditing, are like, I don’t know, I didn’t even know we had that.

And then, like, you look in the error log and it’s full of, like, audit errors and you’re like, well, apparently, apparently you care a lot. I’ve never seen it kill a server. Not to say that, I don’t know, maybe it could happen, but I think, I think it is okay at stuff.

Um, I don’t know anything about your workload or your sysadmins or what you want, what, what, what you want to monitor them doing. But, um, you know, um, maybe, maybe try it out in dev first. Maybe, maybe see how that goes.

I don’t, I don’t, cause I, I don’t know all what you want to do anyway. I hate audit. I mean, not, not cause it’s like bad, but because like, there’s no query plans. So like, who cares?

It’s not, not for me. All right. TempDB is always top weights, even on weekends. Does that automatically mean TempDB is our bottleneck or is that misleading? Well, it certainly is a pretty good indicator that, that TempDB is a bottleneck.

Uh, but it depends on how you’re measuring weight stats. There are a lot of weight stat scripts out there in the world that, uh, just, just compare weights to the general population of weights and tell you what percentage of, of the, the total population that weight accounts for.

And, uh, those, those scripts don’t really do a whole lot to, uh, put things in perspective, like how long the server has been up, uh, how much those weight, how those weights compare to server uptime, uh, things like that. So, uh, and I think it, it depends on how you’re measuring a bit, but you know, certainly TempDB could be, could be a bottleneck for you.

Uh, you know, all, all the usual, uh, you know, sort of advice applies to investigating your workload further. Uh, perhaps you should try sampling your weight stats over a period of time. Or you could, you could even download a completely free monitoring tool from code.erikdarling.com and you could, you could actually find out if TempDB really is your bottleneck.

Or if you’re being misled by whatever script you’re on. Alright. Ah, we run full scan stats every night.

Wow, you must have a lot of time. Uh, still see horrible estimates on some queries. What could cause that? Well, my friend, uh, maybe you should meet our other friend who thinks the legacy cardinality estimator is a cheap gamble. Uh, so, why, why could you have that?

Um, I mean, a million bajillion reasons. Uh, let’s see, let’s, let’s count them off. Um, perhaps, uh, you are using local variables. Perhaps you are using table variables.

Perhaps you are writing non-sargable predicates in some way. Uh, perhaps you are using the default cardinality estimator, right? Um, you know, uh, maybe your queries are suitably complex that any cardinality estimator may have a difficult time, uh, figuring out, uh, just how many rows are going to emanate from various elements in your query plan. Uh, you know, but mostly, uh, you know, if, if you run full scan stats and you still see horrible estimates, uh, you know, you know, switching cardinality estimation models, it’s certainly one option.

Um, but you know, I think again, what, what’s, what’s important to understand about cardinality estimation is that it’s basically only valid, um, to any real degree. Uh, at like, uh, like, uh, it’s not, I guess it wouldn’t be a root operator, I guess it’d be like a base operator. So it’s usually like cardinality estimation is usually pretty okay.

Like when you’re reading from a table, right? So like, just think about like the far right or bottom of your query plan, like that sort of crescent shape where, uh, index and table access tends to happen. That’s really the only place where cardinality estimation is like, you know, uh, valid valid.

Uh, once you get to joins and group by and other things that may reduce rows, cardinality estimation gets really hard. The other thing to keep in mind about your query plans is that they are complete Frankenstein monsters in some cases where, you know, SQL Server is exploring all sorts of alternative plan shapes. And while that’s happening, it may come, it may have like cardinality estimates up to this one point and then it might replace a whole subtree because of like, wait a minute, I was wrong about this.

We got to do something else here. Uh, and so like it might stitch plans together in a sort of weird way. It was, it used to be very, very common, uh, to see query plans where it was like, and I think 10,000 rows are going to go into this merge join.

And I think 40 billion rows are going to come out of this merge join. And you’re like, I, I don’t understand that math. Where did that, how did that math, math, where did that math come from?

What is the origin of this math? And then like, but you know, over time, you just kind of get used to seeing oddities like that because, you know, uh, your, your plans are again, this terrible island of Dr. Moreau monsters that had to be put together very quickly. Right.

To make you happy to make, make you say, what is it? Why is the optimizer taking so long? It’s been 15 minutes. Gosh, darn it. All right. All right. Anyway, I gotta go and do something else with my day now.

Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video. I think.

Anyway, as far as I know, some terrible fate befalls me, but I don’t know. I don’t know. I’m feeling pretty lucky these days. All right. Thank you for watching. All right.

Thank you for watching. All right. All right. All right.