SQL Server Performance Office Hours Episode 76 – Paris Heatwave

SQL Server Performance Office Hours Episode 76 – Paris Heatwave

---

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction
• 00:01:35 – Hints for Long-Term Solutions
• 00:04:02 – Performance Issues Beyond Hardware
• 00:05:30 – Execution Plan Analysis
• 00:06:28 – Delete Operations and Indexes

Full Transcript

Erik Darling here with Darling Data and today’s video is coming to you from Paris, France. Be here for two weeks with the family. Sounds just like New York out there. And then we will be popping around Portugal for a couple weeks. So I’m just going to use this as an excuse to clear out the office hours question list a bit and hopefully, well, I don’t know. hopefully maintain my high-quality SQL Server output for everyone. It is very, very hot here in Paris today. It is like 96 degrees and there is still no such thing as air conditioning. They have still not invented that yet. I don’t know who is in charge of Paris in this round of civilization, but I wish that they would hurry up and invent air conditioning because the people suffer, your majesty. So the first question that we have is, is there ever a case where hints are the best long-term solution? And the answer is yes, absolutely. Despite SQL Server being a highly advanced LLM, it takes query text in and spits out query plans. It still can’t figure everything out in one go. Sometimes it just needs a little bit of extra help along the way. So I would highly suggest that you, if you’re running into a problem and it feels like only hints can fix it, use the hints. That’s what they’re there for. They’re not just there as a cautionary tale in the docs for people to only certified database administrators can use hints. It’s silly. Anyway, I use hints quite a bit for everything from, you know, fixing production problems all the way down to just sort of like experimenting with different queries to see what different query plans look like and how things get costed.

At the very least, at the very least, they are an educational tool, but I find them quite handy for fixing all manner and variety of issues that would otherwise just not work properly. So there’s that. All right. The next question is, why do some servers feel slow even when CPU, memory, and IO all look fine? The two biggest things I find, it’s either going to be network related, like the results take a long time to get from the server point A to point B, or it is going to be something on the application or web servers that are receiving your results and maybe taking a long time to process or render those.

That is the two most common things that I see aside from anything, you know, let’s just say SQL Server side with something easy like blocking, right? Queries taking a long time to compile, you know, just like, you know, stuff that is not going to be like, hopefully not going to be the normal state of affairs. I guess locking, you know, SQL Server, sure. Yeah.

But, you know, the compile thing, that’s a little outlandish. So don’t, don’t, don’t run off and go tell everyone I said that. All right.

What parts of execution plans do people obsess over way too much? Oh man, it’s always costs and percentages. Those are the things that people will not let go of. I don’t know who told them, who taught them that costs were real things, that they were some sort of durable performance metric, but alas, they’re not, they, they just very misleading at the best of times and as complete liars at the worst of times.

So I think that that’s probably the thing that I would stick with there. Aside from that, you know, I would say individual operators, people hyper-focus on scans. That’s another thing.

I don’t know. Probably about it there. Mostly it’s costs. That’s the, getting people to stop paying attention to costs is far and away the most, the most uphill battle that I fight day to day.

And it’s not just with people. It is also with the robots. That’s why in Performance Studio and the Cloud Query Plan plugin that I built, like it is littered throughout the instructions to not look at costs, right?

If someone gives you an estimated plan, ask for an actual plan. If all you have is an estimated plan, there are like, you know, a variety of query plan patterns that you can look at, but always make sure that it’s grounded and you need to get an actual plan to figure out what’s actually slow.

So, all right, let’s see. Question number four here. What mistakes do people most often make when trying to fix parameter sniffing?

Optimize for unknown hints or local variables. That is it right there. I see people do that all the time, and I guess maybe it gets rid of parameter sniffing, but often the plan that you get is not the most high quality of execution plans.

That’s an easy one. That was like a ringer. Ah, man, I feel a little guilty about that. Why do some deletes take forever even when only removing a few thousand rows?

Oh, God, because you probably have way too many indexes that those deletes have to bounce around in. I mean, perhaps blocking, right? That’s always a thing that could happen.

But usually when a delete takes a very long time, it is because whether the plan is a narrow or wide index plan, it is most likely that you have a buttload of indexes, and all of those indexes have to be deleted from. You’re looking at ordering data stuff that has to go with sorting the data by the index key so you can delete from it, all that stuff.

That is far and away the most common reason why. So, yeah. Well, that or maybe you don’t have an index that allows your delete to find the data that it needs to delete in an efficient, timely manner, and that’s what’s messing you up.

All right. That’s five questions. It’s hot, and I need to go take my third shower of the day. I will not be live streaming that. This is not the time.

This is not the channel for it. But thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video, which will also be office hours, but hopefully a less hot office hours. All right.

Goodbye.

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.