SQL Server Performance Office Hours Episode 59

Erik Darling here with Darling Data. Back to Rock’em Sock’em Monday, Monday, Monday. We are going to do our world-famous Office Hours episode in which I answer five user community submitted questions about life, love, SQL Server, protein shakes, exercise routines, whatever, really whatever is on the minds of the good people of the world. So, suppose it’s time that we start doing that, then. Anyway, down in the video description, you will find all sorts of helpful links, not least of which is a way for you to ask me Office Hours questions, but you can also do other things that I find useful, like hire me for consulting, buy my training, become a supporting member of the channel in order to fund my endeavors to keep bringing you this high-quality SQL Server content, and, of course, if you do it, enjoy this, and you think someone else might enjoy this, then you have many options before you. You may like, you may subscribe, and you may tell a friend that this channel exists, and then that friend can subscribe to the channel and like things, too, and then they can tell a friend, and so on. We get the network effect, don’t we, right?

It’s a very important thing with the social medias and other stuff, right? Broad user base. If you like free SQL Server monitoring, boy, have I got a deal for you. I’ve got a free SQL Server monitoring tool that looks at all the stuff that I would look at if I were monitoring SQL Server, which, coincidentally, I am now.

So, that’s what you get. Cool, huh? Wait stats, blocking, deadlocks, query, CPU, memory, disk, tempDB, you name it. I monitor it, and I’ll even tell you when it stinks. I got built-in robots, so if you want to have your robots talk to my tool set, you can turn that on, and you can have your robots connect to the MCP tools, and those MCP tools can look directly at the performance data and just the performance data and have a very, very well-defined set of things to look at and tell you interesting things about.

So, I would highly suggest that you ditch that lousy Sunsetware paid monitoring tool that you have and jump on the free future of SQL Server monitoring. If you want to learn stuff from me out in the world, I will be out in the world. I don’t know why my voice is getting like this. I think I’ve been talking too long. I will be in order. I will be at Pass on Tour, Chicago, May 7th and 8th. I will be at SQL Day Poland, May 11th through 13th.

I will be at Data Saturday, Croatia, June 12th and 13th, and then I will be at Pass Summit in Seattle, Washington, November 9th through 11th. What is happening between June and November? I have no idea. If you have a conference between June and November that you might like me to show up and do a pre-con at, like these other nice folks have, well, drop me a line. Let me know.

Maybe I can show up and do one there. Who knows? Crazy things happen in the world, don’t they? All right. All right. Anyway, for now, it is April and we are home and we are recording videos and we are making sure that the SQL world stays well informed. So, let’s answer some office-y, hour-y questions.

Does optimize for unknown ever really solve parameter sniffing or does it mostly just hides it? Well, it 100% just hides it, right? Because you don’t sniff parameters anymore. SQL Server comes up with an average guess and uses the same average guess for every single query that runs.

This can, in rare circumstances, fix a problem. I’ll never say never, right? I’ll never tell you to never do something. Well, there are a few things I would tell you to never do.

Like, don’t tattoo your eyes. That looks really painful. That’s not my favorite look. But, yeah, sure, everyone, yeah, optimize for unknown, you know, it prevents parameter sniffing, right? Which can, in turn, prevent parameter sensitivity.

But it can also present a pretty lousy plan to SQL Server that a lot of, that all your queries will then use. Kendra Little once referred to this as optimize for mediocre. I think Kendra Little may have changed her mind a bit about this because she has bragged to me about using optimize for unknown in several places to solve a parameter sensitivity issue, which apparently an average guess was better across a variety of query executions than parameter sensitivity was.

So, yeah, it can eventually, it can occasionally solve a problem in the space. But, you know, for me, there are other more attractive solutions that I would invoke, largely involving either recompile hints or dynamic SQL. Maybe a little query and index tuning to go along with them.

Who knows? Crazy. But, yeah, it, it, it, it does mostly just hide it. By mostly, I mean 100% just hides it. So, moving on.

Let’s see. How much do implicit conversions actually matter in real life workloads? Is this one of those, it depends things? Oh, just, no, just one of those depends things.

Not it depends, just depends things. Yeah. So, there are really two different types of implicit conversion that you might see crop up in an execution plan. But, there are ones that may affect cardinality estimates, which, which don’t, don’t, don’t really rile me up so much.

You know, SQL Server throws, we’ll, we’ll show that in an execution plan. And I’m kind of like, well, okay, where? Like, was that cardinality estimate going to be great anyway?

But the, the seek affecting ones, those are the ones that get to me more. Because those are the ones where someone has mismatched a data type in such a way where SQL Server must convert the column values to match the value of some input variable or parameter. And those are the ones that I find are far more deleterious to performance than their counterpart, which is the, the, the one that might affect a cardinality estimates or with a plan affecting convert or something.

So, you know, while it does depend, it is also one of those, you know, one of those, one of those errors that just seems so mind-bogglingly simple to not make, right? Just a completely unforced error that, that, well, sometimes they’re forced on you large, I see a lot of ORMs mess this up, where they will infer some data type, regardless of the column it is being compared whence to. Is an envarkar of a varying length, right?

Like, like, like, like, you’ll see plans where, like, you know, like, if, like, like, let’s say it’s a, like a, well, I mean, it doesn’t really happen so much with dates because dates sort of have a standard length to them, like, you know, 20, 26, 0, 3, 2, 8, at 3, 23 PM, right? Like, like, like, like, the length of those is all kind of standard. But if you were to look at, like, a name, like, you know, my name, my name has four letters in it.

So the, the, the, the ORM word would infer it as an envarkar 4. But if you said, like, Tom, it would be an envarkar 3. And if you said Bobby, it would be an envarkar 5.

So, like, you would see all different, like, length parameters getting passed in. So not only would you have, like, this sort of lousy implicit conversion if you’re, let’s say, first name column or not, we’re, we’re, we’re a varcar or not an envarkar. But you also have the sort of, like, I’m going to create a query plan for every variation here problem, which, which sucks too.

But, um, yeah, you know, it, it, it, when, when you, when you find a problem with it and you solve a problem with it, um, it is often a night and day difference in query performance. But, um, you know, again, we’ll depend, how night and day will depend much on the size of the table and other, other things going on. Let’s see what we got here.

Lowering max dot, reduced CX weights, but blocking got worse. Why would changing parallelism affect blocking at all? Well, when you lower max stop, you lower the amount of available CPUs that a query might have, uh, at its, at, at, at hand in order to execute.

So let’s say that you have a query that runs for, I don’t know, one second at max stop eight, and then you change max stop to four. And now it takes like, I don’t know, four seconds or even two seconds. Um, that’s another two to four seconds of the query executing and another two to four seconds of the query holding locks, maybe.

All right. It’s, you slow the whole thing down, right? So the lowering max stop, sure. Yeah.

CX weights go down. Congratulations. You cracked the case, bucko. But, uh, now all your queries are going to be dot slower because they have dot fewer cores to, to process rows with. Welcome to the world of trade-offs.

Take an economics class. Or read an economics book. I’ve never taken an economics class, so it would be silly of me to tell you to do that. Uh, but you could, you could read a book on economics where you would learn about these sorts of trade-offs in the world.

Uh, anyway. What are the top three mistakes people make when reading execution plans? All right.

Looking at costs, looking at costs, and looking at costs. Those are the top three. How often does query compilation actually matter versus execution time? Um, it can certainly matter.

Um, I’ve run into some funny cases where, um, like, you know, adding a recompile hint, uh, would, of course, you know, get, like, solve, like, problems with either local variables or parameter sensitivity. But, um, now like, you know, like a query, let’s say that when, uh, you, let’s say that, uh, you weren’t using optimize or you weren’t using option recompile, right? You just had a standard plan that came in, and for most query executions, it was totally fine.

So you pay that one-time compilation cost. And let’s say that one-time compilation cost was 500 milliseconds. And then every time the query ran after that, it was, like, zero milliseconds.

Except when you hit a parameter sensitivity problem where it ran for, like, five seconds. So every once in a while, the query would be, like, five seconds. Ah!

But the rest of the time would be very, very fast. Like, let’s just say it’s, like, a date range or something. And most of the time, people are looking at, like, the last hour of data. But then every once in a while, that knucklehead comes along and is, like, I want to see six months of data. And that knucklehead’s query takes five seconds, okay?

So then now, you throw an option recompile hint over here. And you no longer have the occasional five seconds. But now every single time that query runs, it takes 500 milliseconds. Because you have to compile that plan every single time now.

So it does get interesting when you think about, like, again, the economics of these things, the trade-offs that happen. So you do have to balance all this stuff out. And maybe this is a better case than, like, instead of just saying option recompile every time, you could use dynamic SQL.

And you could do something, like, if the difference in, like, days or months or whatever between two parameters is more than an hour, you recompile. But the rest of the time, you don’t recompile. So you do get rid of the parameter sensitivity, right?

Because you no longer have that, oh, the same parameters. I mean, you used a plan. It was bad. No kidding. And now you replace that with every once in a while, you pay an extra 500 milliseconds, and you recompile and do that. But the rest of the time, you’re not recompiling, and everything stays okay.

So how often does it actually matter? I don’t know. If you hire me, I can tell you. But in general, it can certainly matter.

It can be a very interesting thing to deal with. Anyway, that’s the last question. That’s fine. Save the best for last on that one.

Thanks for watching. Hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, where we will get back to the Learn T-SQL with Eric material. And we will continue our voyage through different storage materials, different storage media, I guess, in SQL Server, right?

I think this week we’re going to be talking about columnstore, partitioning, partition views, some other stuff, right? Interesting things afoot in the world. Anyway, that’s it for me.

Thanks for watching.