SQL Server Performance Office Hours Episode 95

YouTube player

To ask your questions, head over here.

Chapters

Full transcript

Erik Darling here with Darling Data in full-on hair farmer mode here as I put off getting a haircut until the very last second before traveling to Seattle. I don’t know why. It’s just what’s happening. It’s getting a little strange in front. As a fellow with this haircut, and I guess anyone could have this haircut, but it’s amazing how long you can extend the life of a haircut like this just by trimming the sort of wings that grow over the years. You just give that a little zip with a trimmer, and your haircut lives like another week or something. It’s incredible. Anyway, it is Tuesday, and I have seen you, and it is time for Office Hours. Down in the video description, as always, there are all sorts of helpful links. It’s for you to interact with me in various nefarious ways. You can hire me for consulting buy my training. There’s even coupon codes down there if you’re a coupon code type of person. You can become a supporting member of this channel if you feel that the educational content here is worth your $4 a month. You can do that. This is also the link to where you would ask me Office Hours questions like the ones you see appear here.

Every week, and as always, if you enjoy the things that I do, and you’re like, oh, I don’t have $4 a month for this rambling fool, but maybe I’ll tell my friend. Maybe my friend has $4 a month, so you can like and subscribe and all that, and that’s fun too. I’ve gotten into the business of trying to make people better at robots, because in my travels, I see a lot of people using robots in silly ways, and not really getting good results with them. And quite frankly, it’s weird, because it seemed very intuitive for me dealing with the robots to get them to behave the way I wanted them to. I don’t know. Maybe I’m just a really good boss. We can pretend that’s the reason.

But if you want to learn, I have a pre-sale class up. The videos for that will start rolling out December 1st. For now, it is $250. After December 1st, the price doubles to $500. And if you work for a company where you would like to learn all this stuff from me, we can do that either remotely or in person. It’s a one-day thing where I show up and I help you do the stuff right in your environment. So, all that good stuff there is proof that I have corralled the robots into doing my bidding. I present to you my free SQL Server and Postgres and Aurora Postgres monitoring tool.

It does all the amazing stuff that you would want a SQL Server or Aurora Postgres monitoring tool to do for free, so you no longer have to live under the pain and yoke of big monitoring tool companies that make crappy products, don’t listen to you, don’t care about you, and don’t really help you figure out problems. I care about you and you figuring out problems. And I use this tool, client sites, to figure out problems. And they do too. And now I’m almost entirely unnecessary in the world because of this monitoring tool. That’s why I have to go teach people how to do AI now.

So, in, shoot, how many days is that? Six-ish days, I will be in Seattle, Washington from November 9th to 11th at Pass Summit West. On November 9th, I have a pre-con. One other day that week, I believe on the 11th, I have a one-hour session. If you have not yet decided where to park your butt on one of those days, or if you’re going to park your butt at all, I would say now is a good time to do it.

The closer you get to the travel date, the harder it is to make travel plans. Anyway, it is time to answer some questions here. I’ll assume it will cooperate. All right. How does resource semaphore pressure interact with blocking?

Well, I mean, I suppose that it could. Resource semaphore is a form of blocking all its own, really, because once a SQL Server has given out all the memory that it can to queries that are asking for memory grants, then other queries that need memory grants have to sort of wait for the locks that other queries have taken on various memory regions to release.

And I suppose if, you know, there were, I mean, like, I guess if the lead query maybe were, you know, holding some lock that might additionally do other stuff. But in general, that’s not really what you’ll find. Anyway, let’s see.

When does plan forcing become dangerous instead of helpful? Well, the obvious one there is when it backfires on you, right? You can force a plan and queries start using it, and all of a sudden, they get slower.

I suppose that’s a very obvious one. I can’t really think of another one off the top of my head. Hmm.

Well, I guess another one might be if you force a faster query plan, or rather, you force a query plan that uses a lot more resources, and then all of a sudden, all of the queries that run that thing, let’s say it runs a lot, they start using a lot of resources, and your server becomes in a resource-diminished state, becomes depleted.

That would be another one. But these should sound very familiar to you, because this is exactly what happens when plans flip in SQL Server under other circumstances, right? You get the little query plan, and all of a sudden, the large amount of data slows to a grinding halt.

Or you get the big query plan, and all of a sudden, your server, CPU, and all sorts of alerts and alarms start going off, because every query is now running at, you know, DOP zero and asking for a 40 gig memory grant or something, right? It’s almost the same thing, just you chose the bad plan instead of SQL Server doing it.

There we have that. Why do TOP N queries… I appreciate this question.

Sometimes run slower than full… I shouldn’t move that… scans. Well, generally, because TOP N queries introduce to SQL Server’s cost-based optimizer a row goal, R-O-W-G-O-A-L. And that row goal, because it affects cardinality, also has an effect on costing and plan choices.

And sometimes SQL Server will pick a query plan to navigate to a much smaller number of rows than the full query would ordinarily produce. And that plan is less efficient than just one scan of the data.

This is especially true if your query is looking for data that is rare, or maybe for a condition that does not exist. Like, give me the top one thing, top one row, where some condition is met, but that condition isn’t supposed to exist in the database, right?

So you’re saying, essentially, if this row exists, I need to go do something else, or I need to let someone know because we should not have this condition in our data. Or perhaps there are just conditions in the data where they may be not non-existent, but just very rare conditions, and you need to look and, like, find a new example of a rare condition.

That’s particularly when the sort of more serial, nested-loop-y, key-look-up-y type query plans tend to do worse than, like, say, a big parallel scan or something. Especially true if you have joins involved, because, you know, you’ve got to go look in two places instead.

Let’s see. What really causes worker thread starvation besides just CPU pressure? Well, traditionally, that’s a blocking is one of the primary causes there.

And, you know, you get one query that, you know, maybe it’s a long-running modification query, or maybe it’s, you know, a query that took locks, you know, did a transaction or something and then just went to sleep.

Nothing ever closed the session out. And then you just have queries backing up behind it, waiting to take various other locks. You know, under SQL Server’s incredibly cruel default read-committed locking isolation level, that might be read queries.

They might be going parallel and consuming many, many worker threads. Or you might just have other modification queries piling up behind it as well. So, you know, it’s, you know, the…

Let’s not split hairs too much. You know, aside from just, like, you know, a kajillion parallel queries all descending on your server, like flying monkeys and messing things up all at once without any blocking, I mean, blocking would be the other chief cause here.

Why does SQL Server sometimes favor merge joins that look terrible on paper? A lot of this, I mean, aside from the obvious answer about just, like, optimizer costing and stuff like that, a lot of it is order preserving, or order preservation, rather.

Since merge joins require sorted input, sometimes, you know, like, like if, like, say SQL Server was going to choose a hash join, that data would become disordered and require further sorting.

So if you have, you know, a query that’s set up to pretty perfectly index-wise support the join that you have written, SQL Server may very well choose a merge join because some downstream sort would be costly. So it’s either, like, you know, you do no sorting whatsoever, and you use a merge join, or you use a hash join and you end up having to sort the data physically because the hash join disordered the set of tuples that your query would return.

That’s probably the most common thing that I see. There’s probably some other fancier stuff that I could throw out there, but that’s, that’s, that’s good enough.

All right. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video. I forget what I’m talking about.

I have a bevy of things that I could talk about. I think tomorrow, though, I had opened up a user feedback issue for SQL Server that hopefully gives them a better idea of what to do about cardinality estimation for off-histogram values than what they currently use.

Sort of a kissing cousin to the ascending key problem. So maybe I’ll talk about that. Maybe I’ll talk about something else.

We don’t know. I haven’t decided yet. Whichever one comes up first in Management Studio, that’s what I’m going with. Anyway, I will see you tomorrow.

Going Further

Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.



Leave a Reply

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