SQL Server Performance Office Hours Episode 91

Chapters

00:00:00 – Introduction
00:04:30 – Query Timeouts When Blocking Isn’t the Cause
00:06:46 – Using BETWEEN for Date Ranges
00:07:52 – Slow Parallel Repartition Streams and CXPACKET Waits
00:10:32 – Parallel Page Supplier, Row Distribution, and Memory Grants
00:12:28 – When Query Store Hides Real Problems
00:15:53 – Wrap-Up

Full Transcript

Erik Darling here with Darling Data. I washed my hair and it looks kind of stupid today. I’m an idiot. Never learned my lesson. Keep showing up on videos with stupid hair. No wonder the AI is winning. Anyway, it is Tuesday, at least as far as I can tell. So, it is time for another delightful, delectable edition of Office Hours, where I answer five, of your questions. How do you submit those questions? That is a great question in and of itself, isn’t it? A question that insists on itself. It’s down in the video description. You can find the link where you can plop whatever text you want in there. Say what you will about me. I don’t know. Just ask me questions about stuff. That’s what I’m here for. I’m here for you. Who else do we have? Not each other. There are also other helpful links for you down there as well. If you would like to hire me as a consultant to make your SQL servers go faster in exchange for money or whatever that stupid line is. I make SQL Server faster in exchange for money. Hey, there we go. Marketing is not my strong suit. You can also buy my training. You can become a supporting member of the channel. Ask me office hours questions like I just said.

And as always, if you enjoy this content, if you would like to receive some form of notification when I post new things here, please do like, subscribe, tell a friend, all that good stuff. Anyway, free for you in the world. Gratis. Gratis. On the arm or something like that. Database performance monitoring for both SQL Server and Postgres. Also covering Aurora Postgres. Very, very popular with the kids these days.

Totally free. Totally open source. There’s no email sign up or phone home stuff in it. I gotta edit this a little bit. It’s, you know, a bunch of collectors going off and doing stuff. I get, you know, the, the light version, good for sort of like one off short term stuff. And then I’ve got the, the full on headless darling monitor that runs as a headless window service logs stuff 24 seven has all sorts of cool MCP web view, alerting, you name it. It’s all in there, making your life better, easier, having things suck a little bit less. If you want to check it out, it’s at code.erikdarling.com. You can head over there and download, install, have a robot download and install it. I don’t care.

However, whatever, whatever, whatever is easiest for you. Pass Summit West is coming up in, ah, boy, just about 30 days. Amazing how that works. I will have a pre-con there about advanced T-SQL that doesn’t suck. Um, we’ll give you, uh, we’ll give you, uh, the, the confidence to not only write your own T-SQL that doesn’t suck, but also, uh, inform the robots when they have done something stupid because the robots, uh, still do plenty of stupid things that need correcting. We need to, we need to remain, uh, a little bit of humanity in all of our loops, various loops in the world.

Well, they also have a session called Read Committed is Cruel by Default, where, uh, I will talk to you about, uh, the, the worst decision Microsoft ever made. And that was, uh, making Read Committed the default isolation level for SQL Server. But, uh, this is no longer valid. It is cold today. I’m wearing socks in the house. It’s cold.

Oh, I hate it. I hate it. Anyway, I don’t know. I hate the heat. I hate the cold. I just want it to be warm enough to smoke cigarettes and underwear. There we go. Uh, all right. Hi, Eric. Hi, you. Uh, there.

Have you ever diagnosed the query timeout issue where blocking was not the root cause? Uh, RCSI is enabled for the user database. If yes, then what was the reason for the query timeouts?

The select query is throwing timeout errors. Uh, I, I’m, I’m assuming that you mean, like, an actual timeout, like, it is, it is, it run longer than, uh, it was budgeted, uh, and allowed to run for, uh, in which case, there are so many reasons. There are so many reasons why a query might timeout. There are, there are, there are hundreds of millions of them. Uh, so I, I, I assume that you have the select query. Uh, the best thing that you could do would be to look at the query plan for the select query. Uh, presumably, uh, you could get an estimated plan for it, uh, and just see if there’s anything, uh, a muck in there.

Uh, if you’re able to get an actual execution plan and let it run to completion, that’s cool. A lot of people have trouble with that. So, uh, what you should probably do, if you, if you can’t, is, uh, turn on actual execution plans, uh, start running it. And then in another window, run SP who is active with get plans equals one, the parameter get plans equals one on, then it will capture the in-flight actual execution plan, uh, uses a different DMV, right? Uh, and when you do that, you can see where time starts accumulating in the query plan, not costs, not other stupid percentages, but time in the query plan starts accumulating. And when you do that, uh, you can start to see where things are slow in there as the query is running. So, uh, I don’t know why your query is timing out. If you would like me to figure that out for you, uh, my rates are reasonable. Uh, if, if, if you want to go this on your own, well, that’s, that’s what, that’s how I would approach it.

Uh, um, if you have query store enabled, uh, you might find the aborted or timed out, uh, query plan in there, which might give you some sense of what was happening. Um, but, uh, I don’t know exactly why yours is. And I’ve seen too many queries run slowly in timeout for too many different reasons to list them all here. Uh, it would far elapse my budget for standing here and talking. Um, I heard somewhere that you should use, uh, I should highlight these, shouldn’t I? I’m not doing a good job. I’m a bad presenter.

I’m, you know, lousy and cranky, fluffy hair. Uh, I heard somewhere that you should use greater than or equal to and less than or equal to instead of between when querying for date ranges. Is this true? Uh, not for any reason other than code clarity. Um, not everyone understands the full semantics of between. Uh, they don’t understand that it is an inclusive, uh, range on both sides.

Uh, and a lot of people, uh, when they, they, they find that out, they’re like, oops, because really they wanted greater than or equal to and a less than, uh, in between it would include too much stuff. So, uh, if, if, if you want to use between and you know exactly the semantics of it, uh, that’s cool. A lot of other people might not.

So, I, I do prefer to use the greater than, equal to, and less than, equal to, um, for that reason. Let’s see here. Ba, ba, ba, ba, ba, ba, ba, ba, ba, this is gonna be a long one.

I have an actual execution plan that takes hours, but the only lengthy operator times are two parallel repartition streams after a relatively small index scan. Repartition streams feed into a parallel many-to-many merge join, and all of the rows are on a single throw. Ah, the query only does 30, I’m not even reading those words out loud because they are useless words to me, uh, and uses seven seconds of CPU.

How do I figure out what it is waiting on? The only significant wait stat is CXPACKET for the whole duration. Everything else is almost there.

All right, so, um, what I would do is, is not actually spend any time trying to figure out what it’s waiting on. I’ve seen this, this situation too many times. It, it’s not a fun one to debug, uh, and, and a, a lot of the, the weights and other stuff that you’ll see aren’t very helpful.

Like, it, it certainly could be, like, a work table in the, in the many-to-many merge join racking up, like, doing all the duplicate-y stuff that it has to do. Uh, I, I, just don’t do it. Um, if you want to do yourself a big favor here, um, just run the query with an option hash join hint.

Uh, get rid of the parallel merge join, the, the, the mystery solves itself. Um, that, that, 99% of the time, that, that is the solution to the problem. Parallel merge joins are a terrible mistake.

Uh, you should avoid them at all costs, because stuff like this gets really hard to figure out. Um, I, you, you could be dealing with, like, some sort of, like, weird exchange buffer thing going on. Uh, you could be getting close to a parallel deadlock, or just, like, you could have exchange spills and stuff.

Uh, it, it all sucks to deal with. Just throw a hash join hint on this thing and be done with it. Um, if there are other order-preserving operators in the plan, like, say, a stream aggregate, uh, a hash group, uh, hint would also be useful.

So, option hash join, option hash group. Um, if you are in a situation where perhaps you would like to take advantage of batch mode, uh, because the, good, batch mode query plans tend to get rid of the, the, the parallel exchanges. You see far fewer, like, repartition streams, distribute streams, and stuff, and then, uh, try to use one of the normal tricks to induce batch mode in there.

Uh, but I, honestly, I would just start with option hash join and watch the problems go away. All right. How does the parallel page supplier affect row distribution and memory grants and real work?

No, I only care about fake workloads, uh, being a fake consultant who could never make it in the real world. Uh, so, the parallel page supplier is an interesting thing. Uh, it’s what’s responsible for handing rows out to parallel, to parallel threads in a parallel execution plan.

Uh, and sometimes the, the hashing mechanism that it uses, well, the mechanism that it uses to distribute threads, uh, usually the one where it ends up, um, uh, unevenly distributed would be a hashing mechanism. Uh, fellow co-blogger Joe Obish has a very cool post on my site about, um, uh, picking a range of values that all end up hashing to the same thread. Uh, so, usually it’s like, you take, like, the set of key values and you say, like, modulo dop, right?

So, your dop is eight, so it’d be modulo eight. And then, like, all those values hash to the same, uh, thread. And they all end, you get, end up with very uneven, uh, row distributions on the parallel threads.

Uh, that doesn’t affect memory grants, but what that does do is screw your memory grants up. Because, as I’ve said many times, all, all query plans start as serial execution plans. Uh, the decision to go parallel, parallel is made later, but the memory grant is derived for the serial plan and then divided by dot for the parallel plan.

So, if you end up with a plan where the parallel page supplier did a not-so-great job and modulo’d all your rows to one or just a couple threads, uh, and they are, but they all get equal amounts of memory, obviously those, that one or two threads is going to, um, uh, not have enough memory potentially to, uh, do everything that it needs to without spilling. So, that’s sort of how that goes. All right.

When does Query Store hide real problems instead of exposing them? Well, uh, there are, there are a couple ways that happens. Um, if your Query Store is set to auto mode, uh, then queries have to meet some certain eligibility requirements before they’re allowed to, before they go into Query Store.

So, you can miss stuff in that way. Uh, but also there’s a lot of stuff that happens before a query even gets going and does stuff. That, uh, Query Store can’t tell you about.

Uh, for example, there are, like, pre-weights that go into things. Uh, not compiling, but, like, uh, say you have threadpool waits or you have resource semaphore or resource semaphore query compile waits. Those are all things that happen, uh, technically before a query even starts executing.

And so, those things don’t show up in Query Store. Like, you can see those in other ways, right? Like, dang it. The secondary metrics, when we start yelling about stuff.

Uh, wait stats will show you threadpool. They’ll show you resource semaphore. They’ll show you resource semaphore query compile. So, you can get all that stuff from someplace else, right? So, like, Query Store doesn’t necessarily need to have everything.

Query Store is a great place to look for query performance problems. A lot of those things are sort of server performance problems. They’re, like, bigger. They’re outside.

They’re, like, just, like, anything that is a problem with a single query. It’s, like, your whole thing is crappy, right? No, the workload stinks. So, like, there’s just stuff that you, like, like, doesn’t need anything.

I’ve long been an advocate for more things going into Query Store. Like, the query performance stuff is great. But then, like, you know, you have the block process and deadlock reports, which are in completely different places, but are completely related to stuff that, like, you know, is part of query performance.

So, like, that, like, I feel like those things should be in there. Like, that should tie in a little bit better. But, you know, there’s some stuff that, you know, like, Query Store is not a full server monitoring tool.

If you want one of those, I got one for free, baby. Code.erikdarling.com. But, yeah, that can, that’s certainly one way. What else?

I mean, you can kind of see that. I think, you know, another thing that I think would be valuable for Query Store, and this is just one guy talking here.

It would be, there’s another DMV that got introduced into SQL Server that keeps a last actual execution plan around. I think it would be cool if Query Store sort of absorbed that and kept a last actual execution plan around, or maybe, like, you know, a number that the users can configure on their own and decide.

I want the last, like, because you can keep, you know, like, I think the default is, like, 200 plans for Query. Like, who would want that? But, yeah, I think it would be cool if you could decide if you wanted to keep some actual execution plans around, because going and getting them later sometimes kind of stinks.

And if you’re dealing with a parameter sensitivity problem, having a query plan around that demonstrates the compile and the runtime values when something may have been parameter sensitive is incredibly valuable, right?

Incredibly valuable stuff. Anyway, I’ve talked for too long. I’m going to go, I’m going to leave it up to you to decide if this is a tiny espresso mug or if I am just an enormous human being holding a regular size mug.

That’s for you to decide. But I’m going to go drink this now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video.

It’ll be a surprise to both of us. All right. Thank you for watching. Goodbye. Bye-bye.

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.

SQL Server Performance Office Hours Episode 91