SQL Server Performance Office Hours Episode 58

Erik Darling here, Darling Data, and it is Monday, officially, official Monday, which means it is officially time for an Office Hours episode where I answer five of your wonderful, thoughtful, community-submitted questions. We’ll see what we have this week. It should be amusing. Oh yeah, Office Hours, that thing. If you want to ask your own Office Hours questions, you want to have something that you want to ask me instead of a robot? Well, you know, I want some of that human-generated warmth and thoughtfulness. You can do that. There are links where the finger points down below in the video description where you can submit your question to me. There are also other ways for you to interact with me if you’re interested in giving me money. Like, you can become a subscribing, supporting channel member. You can buy my training and you can hire me for consulting. So, all of all of those things give me money. The Office Hours thing does not give me money. Likewise, liking, subscribing, until a friend does not give me money, but does bring me incredible joy as I see the numbers on the YouTube channel continue to rise beyond my mildest expectations. If you want to get some free SQL Server performance monitoring, there is also a link down in the video description for that. I’ve released a free open source SQL Server performance monitoring tool. There’s no email involved. There’s no phoning home. There’s no telemetry. You can see everything it’s doing. You can contribute to it if you want. You know, it monitors all the stuff and all the stuff you would care about when it comes to performance on a SQL Server. All the stuff that I would monitor, which is a pretty good slate of stuff. And then if you prefer the robots, of course, there are opt-in optional MCP servers that you can interact with or you can have your robots interact with. And they can look at your summarized, time-sliced, well-collected, informed performance data. And they can maybe give you some answers pretty quickly about it. I’ll be leaving the house, bringing my human-generated warmth to the people of the world, all over the place. I will be at SQL Day in Poland, May 11th through 13th. I will be at Data Saturday, Croatia, June 12th.

12th and 13th. I will also be at Pass On Tour and Pass Summit in Chicago and Seattle. Chicago is May 7th and 8th. Boy, that cuts real close to Poland, doesn’t it? Real close. Boy, oh boy. That’s right there. I’m actually flying from Chicago to Poland. So from Little Poland to Big Poland. And then, of course, Pass Summit in Seattle, Washington, November 9th through 11th. Bit shorter this year, but that’s okay. Saves me a little money because travel ain’t cheap. Anyway, let’s answer these questions. All right. So we’ll come over here to our Excel file of joyous questions that shows me what people have submitted here.

And the first one is, if your primary reason for using the Legacy Cardinality Estimator is to improve join estimates, why not use either Optimize For or Assume Join Predicate Depends on Filters? Your article on the latter is excellent. That is not my article. That is Joe Obisch’s article. I just have a sort of annoying plug-in on my website. It’s called Last Updated. And if I have to go in and make any, not editorial, but other changes to the post, usually, sometimes I’ll tweak the footer information down at the bottom. It changes and says, it looks like I wrote it.

I don’t have a way to fix that. So sometimes it looks like I’m taking credit for one of Joe’s posts. But I assure you, it is not mine. It is his. I would never take credit for that. It is a very good post. But the reason why I don’t use it is because it is far more typing.

No. So when I’m looking at a query plan, join cardinality is one of the things that will stick out to me as a potential reason to want to use the Legacy Cardinality Estimator. So that’s usually where I start. Do I ever end up there? Sometimes. But most of the time, just doing the Legacy Cardinality thing works just fine for me.

So, it’s not like I’m saying don’t use that, or it’s not like I’m saying it’ll never be useful. But for me, most of the time, just using Legacy Cardinality Estimator is what gets me where I need to be. All right. Next question.

We aggressively removed key lookups by adding tons of covering indexes, and somehow overall performance got worse. Can you explain how that happens in practice? Sure. So, assuming that you, well, I mean, obviously, you said you added tons of covering indexes.

What are they covering now? How big did your indexes get? How big did they used to be? So, what you’re going to have now is lots of presumably much larger nonclustered indexes, and much wider nonclustered indexes, which is going to have some important ramifications on several crucial, critical components of your SQL Server.

One is the buffer pool. Much wider indexes take up much more space in the buffer pool, so you are no longer able to cache as many competing objects in your buffer pool, which means that you will most likely have to go to disk more often.

The other thing that you are going to affect pretty significantly is around the potential blocking and deadlocking issues. Because now that your indexes are much wider, there is a much greater chance that, well, at least an update, but certainly an insert or delete will have to modify those.

And you may have queries that did not used to get blocked, now get blocked. Third and finally will be your transaction log. Because now that you have all these much wider indexes, it will be absorbing many more modifications.

You will also need to write more to your transaction log. So, in practice, those are the three basic things that much, much wider indexes will mess up. So, while all your key lookups may be gone, and who knows if any of them or all of them or what portion of them were a problem in the first place, you probably made three other things a whole lot worse.

So, use wisely. Let’s see here. There’s my little dot.

Next up. Everyone says, who’s everyone? Tuning queries saves cloud money. But we tuned a ton, and Azure costs barely budged. Is that normal?

I don’t know. Depends on how good of a job you did tuning. Depends on what you tuned for. And also, it depends on a couple other things. So, if you follow sort of old advice and you tune for things like logical reads, well, that may not necessarily drive down CPU.

If you did not do a very good job of tuning, then perhaps costs did not improve. I’m assuming now that you’re on the DTU model here.

So, that would be where you would see, like, insta-savings because you would no longer be using any more, you know, of those sort of cost units that Azure puts on the DTU skews of Azure SQL database. If you’re not using the DTU model, if you are using, like, the sort of, you know, core-based model for things, well, not only do you have to tune, but you also have to make the instances smaller.

Or else, you’re still paying for all that potential bandwidth, even if you’re not using it. So, depending on where you are and what you’re doing and what you tune for, just saying that, well, I don’t know, that’s probably why. But, you know, you may have something else fun and weird going on.

So, as always, my rates are reasonable. If you actually want to save money on Azure, we can talk about that. Is it better to have a stable plan that’s a little slower or a faster plan that sometimes misbehaves?

Ah, you know what? Honestly, depending on what value of little you’re using here, I would take the stable plan that’s a little slower. There are, you know, it’s not fun if you have a plan that sometimes misbehaves, even if it is, you know, a little faster on the whole, because dealing with those misbehaviors is not enjoyable, especially if they happen late at night on weekends, while you’re at a kid’s birthday party, all that stuff, all of a sudden, email starts blowing up with alerts.

No good, right? So, I would take stability sometimes. There are some times stability is nice. You know, it’s sort of like the difference between having a job and consulting, right? Like, you have a full-time job, you get a steady paycheck, you know, 401k, health insurance, consulting.

You can usually make a lot more money than a full-time job would pay, but sometimes things misbehave. And, you know, sometimes that trade-off is worth it, sometimes not. But for the most part, if we’re just talking about you doing your full-time job, like as a consultant, you know, a plan that sometimes misbehaves, I can make some money off that.

As a full-time employee, you make the same amount of money, whether you have to deal with that misbehavior on Saturday at 2 a.m. or, you know, anytime, right? So, for you, if you’re a full-time employee, I would take the stable plan.

As a consultant, I would take the faster plan that sometimes misbehaves because taming that misbehavior, that’s where I get to send someone an invoice. Anyway, last but not least, this is going to be a fun one.

We can enjoy ourselves here, can’t we? So, same query text, same parameters, same plan, wildly different runtimes. I feel like I’m losing my mind.

What causes this? Well, let’s just assume here, because I’m going to pretend that you are a very, very smart person. Even though I know nothing about you, I don’t know who you are, I’m going to pretend that you are very smart.

And the reason I’m going to do that is because I don’t want to answer another boring parameter sensitivity question and explain that whole thing again. I’m going to pretend that what you mean here is that let’s say you have a store procedure that accepts one parameter, and let’s just, for the sake of argument, say that sometimes you execute that with parameter equals one, and it’s fast.

And sometimes you execute it with parameter equals one, and it’s slow. There are going to be three things that I would look at that might tell you what’s happening. One is overall server load, right?

You might execute that at a time when there is a whole lot more going on with your SQL Server, and that query may suffer. The sort of hardware concurrency issues.

The other thing that might happen is that query may sometimes be getting blocked. All right? So if sometimes you run that query and it’s slow, well, there’s other stuff going on in the server, you may be suffering from sort of logical concurrency issues where a SQL Server, like some of the pages that your query needs to read are being locked by a modification query, and it takes, like you’re looking at your watch or your clock or whatever, and you’re saying, hey, this query’s taking a long time compared to how it used to run.

I wonder what’s happening. For those two things, what you could look at is probably who is active, right? Take a look at how busy the server is when the query is slow.

And you could also use SP who is active to see if your query is getting blocked by anyone when it’s slow. The third thing, and this is another sort of hardware concurrency related thing, is that sometimes the data that you’re reading might be in the buffer pool, and sometimes the data that you’re reading might be on disk.

If the data that you’re reading is already in the buffer pool, then it will probably be pretty fast. If you have a lot of data to read, and it’s not in the buffer pool, it’s only on disk, you have to read that data from disk into memory.

That can slow your query down quite a bit. So, those are my three best explanations for a very assumed set of circumstances. And I think that also brings us one, two, three, four, five entire questions.

Cool, we’re done. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you did learn something. Learning something in the future might be.

Everyone learns something in the future, I think. And I will see you in tomorrow’s video, where we’re going to talk about a store procedure that I wrote to help a client out.

They were trying to test different backup strategies, and so I wrote a store procedure to test a whole bunch of them and report back on metrics. So, we’ll talk about that in tomorrow’s video.

It’s already up on the Darling Data GitHub repo. So, if you want to see it ahead of time, you go to code.erikdarling.com and you can see it there.

All right. Thank you for watching.