SQL Server Performance Office Hours Episode 77 – Paris is nice again
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:25 – Query Plan Changes Explained
- 00:04:07 – Redesign vs Tuning
- 00:06:39 – Performance Queues and Delays
- 00:08:58 – Query Store Analysis
Full Transcript
Erik Darling here with Darling Data, and we’re coming to you from a slightly different Parisian window today where thankfully the temperatures have gotten back to a place where I no longer feel like I’m dying without an air conditioner. So enjoy the view a little bit. There we are. Look at all these wonderful French people doing wonderful French things. Anyway, we’re continuing to clear the air conditioner. clear out the office hours questions queue. And so that’s our job. And we’re gonna do it. Just because I’m on vacation doesn’t mean I’m not working. Do what you love and you’ll never take a day off in your life. Alright. First up, what causes SQL Server to suddenly change join strategies for the same query text? Well, could be any number of things. Alright. Usually all the same things that it always has been. SQL Server came up with a new query plan, perhaps because of a recompilation or what it would be not necessarily a recompile, but a fresh compile because like the cache got cleared or something. Perhaps some statistics updated. Perhaps we have a parameter sensitivity issue. But it is always one of those things, right? Something just changed and we had to we had to make a different decision for some reason. And here we are brand new query plan, brand new join strategy, sometimes better, sometimes worse, but always valid. That SQL Server always valid. You know, there are a number of things you can do to stabilize these plans. Like we talked about in yesterday’s video. I’m a huge fan of query hints. I think they are underutilized by people. I think people are unnecessarily afraid of them. But you know, you can always do other stuff too. You know, if you want to do, I wish, I wish the guarantees of query store plan forcing or hints were better. They’re not. Not that great. Yeah, so that’s not, that’s not really my favorite recommendation. You know, there’s plan guides, I guess, but really what it usually comes down to for me is looking at the two different query plans and figuring out if there is something you can do in order to get SQL Server to choose a more stable query plan naturally. Sometimes that is index changes. Sometimes that is query rewrites. But in general, that’s that’s the first place I go. And if that offers me no, that offers me no real help. Then I will help then I will go to query hints or something else. All right. When should you walk away from tuning and recommend redesign instead? Well, man, that’s a big one. So the thing with redesign is that that means I mean, I guess it depends on like what at what level we are redesigning. Are we redesigning a wide table to normalize it? Are we redesigning some something else about the way that we’re storing data to make it easier to access the data paths that we need?
You know, for me, you know, for me, it is usually a question of will that redesign take longer and benefit the general performance characteristics more than me just pulling the data that I need out and into the format that I need along the way? Temp tables are a great way of doing that. You know, so I don’t think that I would ever walk away from tuning. I think that the two tasks really have to be done in parallel. Because when you have when you have when you have like performance problems across the board, you need to do something to alleviate them immediately.
But you also want to take the proactive and take good long term steps so that, you know, you like you don’t have to keep doing all the other work to move data around just so a query can run your query should. Your query should, you know, again, the polishing ivory that I usually do here is, you know, store data the way you query it and query data the way you store it. If you have to keep changing data in order to get it out, perhaps you should start storing your data in a format that is more aligned with the results that you are returning.
But this is specifically geared towards like when I would recommend to redesign, not just like, you know, go ahead and start changing tables and doing whatever you want. When I get it willy nilly because that’s that’s painful and often requires application changes. If there’s one thing I know about DBAs, they don’t make a lot of application changes.
So that’s that’s that’s what we got there. Let’s see. Why does SQL Server sometimes just stop like everything queues up for five to 10 seconds and then magically recovers? Well, there’s a there’s a lot of reasons for this.
I mean, the obvious one is locking and block. It’s this usually comes down to resource contention. It’s in it’s someplace along the pipe. So the most common one, especially if you’re using the God forsaken garbage isolation level read committed, locking and blocking between read queries and write queries, I’ll absolutely do it. More severe ones would be things like threadpool waits running out of worker threads, resource semaphore running out of query memory grants, resource, query memory grants space, resource semaphore query compile, or even just beyond that compilation locks.
Like if you’re waiting a long time on like synchronous stats updates and like the same procedure needs to compile a plan across like 100 sessions, that first session sitting there waiting on the stats update and then the 99 other ones are waiting for this one procedure to cache a plan so that they can all use it. That’s another gnarly one.
There’s all sorts of stuff that could do it. Even if you don’t hit threadpool, you can get pretty backed up on just like runnable queues. Like, you know, queries have four different, let’s say four different states in SQL Server.
They’re running, they’re on a CPU, they’re runnable, which means they could run if they could get CPU attention. They’re suspended, which means they’re waiting on something that’s not CPU or they’re sleeping dormant, whatever. You know, if you can stack up your runnable queue pretty high even without hitting threadpool waits.
You can see that a lot when people mess with the max worker thread setting, which is kind of a goofy thing to do for most people. I would say maybe don’t run and do that first. Like that’s maybe not my favorite setting for people to change.
Alright. Query store says average duration is low, but P95 is horrible. Which one should I care about more?
Hmm. Uh, well, that’s an interesting question. Uh, I don’t know if 95% of your queries seem to be unhappy for some reason. Uh, then that’s probably something worth looking into.
Um, the thing with, well, I mean, actually I don’t even know. Cause, cause like it’s just like a query store should have a pretty good detailed query history. And so you should be able to ascertain mostly that like average duration is not good for something.
Um, I would, what I think, so if you’re getting, I would, I would wonder where you are getting your P95 from. There are lots of monitoring tools out there that are not quite as granular as, uh, query store is. Uh, Datadog being one of them.
Datadog is highly sampled and highly aggregated data. And they do not exercise, uh, their data collection across many of the sources in SQL Server that you would expect, uh, query sampling to go from, to, to come from. Not like DMV wise, just the columns and the DMVs.
Um, they do a lot of sort of like averaging over totals. Like I think they only collect total. I don’t think they collect like mins, maxes and stuff. So I would really wonder where you’re getting your P95 numbers from if query store is saying that average duration is a-okay.
Uh, so I would, I would wonder, I would wonder a little bit about that. Uh, let’s see here. We got, that was one, two, three, four.
We got one more. Oh boy. All right. Last one. And then I will, I will give you another view of Paris and then I will say goodbye. Uh, uh, I see memory grant feedback kicking in over several executions, but performance still sucks. Is that feature actually reliable?
Well, it has gotten more reliable. Uh, but if memory grant feedback kicks in and does stuff, uh, perhaps it is not memory grants that are your problem. Your problem could lie elsewhere.
Uh, you know, it, it is, it is amusing how people think that these features, uh, individually will, will just solve all their performance woes and worries. But, uh, it’s not, not often the case. Um, you gotta, you still gotta do some work once in a while.
So what I, what I would look at, I mean, you could look at like what memory grants are affecting about the queries execution. You know what I mean? Like if you’re, if you get to a stable memory grant and things are not spilling, then that’s cool. But you’re, you know, there’s still a million other things that can go wrong on a query plan and you should spend some time looking at what those million other things might be.
Because memory grants are just one very, very, well I wouldn’t say one, one very small, but they are certainly one, uh, piece of the puzzle that make the entire query performance picture whole. Alright. So here you go.
Here, here’s, here’s Paris. This is, well this is sort of what I get to wake up and see every morning. Uh, it’s pretty nice. Uh, I like it. Anyway, uh, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something and I’ll see you in tomorrow’s video where we will clear out more office hours questions. Uh, they, they, they really piled up. Uh, and the, the once a week thing was not clearing out the cubes. Uh, you know, anyway.
Alright, 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.