SQL Server Performance Office Hours Episode 90
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:03:00 – Free Monitoring Tool for Server Baddies
- 00:06:00 – Concurrency Problems and Wait Stats
- 00:09:46 – Tuning Advice That Does More Harm Than Good
- 00:12:40 – SQL Server’s Dumb Choices and Query Optimization
- 00:14:40 – Optimizer Budget and Time Constraints
- 00:16:00 – Conclusion
Full Transcript
Erik Darling here with Darling Data, and we are office hours-ing once again. I apologize for the sudden dearth of videos last week. There were a few technical difficulties with the setup that I just didn’t have time to get to. We’re all sorted out now, though. Everything seems to be back in working order. Functioning camera, microphones on, beautiful background here. What else do we need, folks? What else do we need? Anyway, down in the video description, if you want to ask me questions for office hours like all the other nice people do, at least most of them are nice. Some of them, not so nice. Some of them, I don’t know. Trying to, really trying to get me down. I don’t know why. I don’t know what’s wrong with you. I don’t know what I ever did to you. Link is down below. Other links sprinkled throughout my very helpful video description. You can hire me for consulting. You can buy my training. You can become a supporting member of this channel for as few as $4 a month. You can sacrifice, like, one Midwest coffee a month and give it to me to say thank you for usually producing a lot of videos every week. Last week was weird, though. Anyway, also, if you happen to enjoy this content in any way, shape, or form or, you know, you think someone else might just want to hit mute and, you know, enjoy themselves, like, subscribe, tell a friend, all that good stuff. If you are in the market for something that doesn’t cost any money, I’ve got a deal for you. I’ve got the steal of a lifetime. Free SQL Server and Postgres database performance monitoring. If you go to code.erikdarling.com, there’s more links down below that are actually clickable. You can’t click this one. It’s just, it’s on the screen. I’m not quite that adept at YouTube. Anyway, all the stuff that you would want from performance monitoring tools. I got to do more videos about those. It’s really, really turn it into a thing. It’s amazing when I use it, like, I turn it, like, use it at, like, client sites and stuff and I turn on people just like, that’s free. Like, yeah, I like you. Anyway, I wasn’t free, but that is free. Maybe that’ll, maybe this will all take my place someday. I hope so. That’d be fun, right? Just ruin everyone’s life by, anyway. It’s great if you need that sort of thing. It is almost the end of September. I suppose it’ll be closer to the end of September when this actually publishes, but I will be in November.
I will be in Seattle for Pass Summit West, taking place at the Hyatt Regency at 808 Howell Street in the United States, November 9th through 11th. I have a pre-con there where I’m doing advanced T-SQL, called T-SQL that doesn’t suck, and I also have a regular session there called Read Committed is Cruel by Default, where I will teach you things about isolation levels that you, well, do you need to know any more. I mean, do you need to know anything? Maybe I will be in the middle of September 11th, taking place at the Hyatt Regency at 808 Howell Street, in the United States, November 9th through 11th. I have a pre-con there where I’m doing advanced T-SQL that doesn’t suck, and I also have a regular session there called Read Committed is Cruel by Default, where I will teach you things about isolation levels that you…
Well, I mean, do you need to know? Maybe you can just be better informed when you’re arguing with robots about them. That’s about what I got for you. But anyway, September is wrapping. This is not, this is this, the weather outside right now is not living up to this photo. It is very windy and rainy. The weekend storm. I know you’re seeing this on Tuesday, but right now there is a weekend storm outside, which is why I get to do this on a Saturday. Life’s grand. None of it matters.
Anyway, let’s go answer these questions. When is plan stability actually more harmful than helpful? I don’t know, actually. How about when you have parameter sensitivity issues? Sometimes one plan ain’t enough for a query. You need some, you need some planiamory or something. Some queries just need multiple plans. Other times, you know, I mean, there’s some obvious stuff that you might have going on. You might have some real issues with like, you know, indexes or perhaps the way the query is written or something. But yeah, some, some, some data patterns, some, some shapes of data do require some queries to take on multiple plan shapes in order to run quickly. That’s just the way of the world. A lot of queries, you know, you don’t have that problem with. They just run 10 million times a second in the background and you hardly even notice. Other queries, they, they blow up. And when they blow up, things get bad. Both from the perspective that you might have, like, let’s go a little bit further on this. If you have a situation where like a big plan and a bad plan don’t share well with each other, you, you get the little plan first, right? And all of a sudden, like queries that need big amounts of data really, really slow down and take a long time to run. And then you have the sort of reversi problem where the, the big query plan, uh, uses a lot more resources, right? Cause it’s like, oh, we have work to do. We’re, this is interesting. There’s a lot going on here. Uh, and, uh, you know, you get a big parallel plan, big memory grant, all sorts of other stuff happening. And then all of a sudden, lots of people start running that big plan and your server, uh, becomes bereft of resources rather quickly. So, uh, that, uh, you know, um, that is when plan stability can not be fun. Ah, it moved.
That wasn’t supposed to happen. Uh, how do you prioritize tuning when management wants everything fixed? Ah, that’s easy. Uh, you ask management what they do that’s slow. Cause as soon as they’re happy, they shut up and you can, you can go do all the rest of it. Like, uh, like, Hey, Hey boss, what’s slow. And like, uh, every time I hit this random layoff button, it takes forever. And then you go look and you’re like, ah, I see what the problem is. You’re ordering the whole company by new ID.
Um, that’s why. All right. I can use table sample and some other stuff to fix this up. Don’t worry about it. I gotcha. We’ll get those layoffs faster. Uh, but if it’s, if it’s not a case where management uses the software directly and has an opinion on it, um, I guess you could start by asking them like, um, like what do people who have their phone number complain about?
It was like, if someone like, if like some other honcho type is calling them like the gosh darn tootin cockadoody bird reported slow again, you’re like, well, let me just go find USP underscore gosh darn cockadoody report dot SQL. And you can take a look there. Uh, otherwise, you know, you can, you can talk to other people who have to deal with, uh, customers, uh, support people. Uh, often there is a trail of tickets. If you have a ticketing system where people have expressed their loathing of performance issues. Um, I, I, I have found, uh, robots to be very good at going through, uh, large, vast swaths of data like that and finding sort of repeated, uh, patterns and problems with stuff. They are very good at, um, that sort of mechanical labor. Um, so if you want to give, uh, I don’t know, like you want to give cloud access to your, like Jira, ACLI or whatever, and this is like, Hey, go for it, buddy. Uh, read away. Tell me what’s the worst stuff.
That’s, that’s one way of approaching it. Um, you know, other than that, I would, I would go with my sort of stock and standard methods. Um, there’s actually a very cool new, uh, parameter for, uh, SP quickie store called, uh, find high impact. Uh, I did a video about it where I used the wrong word like 15 times cause I was tired. And then Kevin Fiesel put it on his curated SQL thing. And like, there were a bunch of like, uh, corrections in it. I was like, ah, thanks man. I didn’t realize I screwed up that bad. Uh, there were, there were some things on my mind at the time. I think, I think a Pomodoro or something. Uh, there’s, there’s a hotel in France called, uh, Cheval Blanc and they, they make a drink called the Pomodoro, which is a tomato and mezcal drink. And it is absolutely delicious. And I was thinking about that at the time. Anyway, ah, there we go. Always comes back to Paris. Uh, but anyway, um, SP quickie store has the find high impact parameter and that seeks to score it rather than like, you know, like the normal sort of query tuning method, like sort the thing by CPU or duration or physical reads or memory or whatever. Uh, it scores your queries across a variety of metrics and finds the one, find the, find the ones that hurt you most across like a bunch of stuff. Right. And that breaks down like what percentage of CPU and duration and all the other stuff they use. So you can be like, Oh, I don’t like the looks of this one. Let’s, let’s go tune it. So that’s probably what I would do. Otherwise you could go to code.erikdarling.com, download an absolutely free monitoring tool and figure out what’s really driving your server baddie.
Cause it’s a pretty good way of doing that for free. How do you spot concurrency problems before users start complaining? Uh, well, let’s see. There are many levels to concurrency in the, in, in, in the database world. Uh, you know, at the, at the lowest level you have stuff like latches and latch contention. I guess you could go a level lower and do spin locks, but you know, don’t start there cause you don’t, you don’t want to start. I wouldn’t start with latches either. Um, you know, wait stats are probably a good sort of in the middle, uh, indicator of where you might be experiencing, uh, contention, but, uh, a level up from that would be sort of hardware resource contention. But, but the nice thing is that almost all of this stuff is available in wait stats. For example, uh, if you have CPU contention, you’ll see like SOS scheduler yield to be high up there. If it’s caused by parallelism, the CX weights will be high up there. Uh, if you’re having some form of like disk slash memory contention, uh, then you’ll see, uh, you know, lots of the page IO latch underscore weights because SQL Server ain’t, uh, keeping a lot of the data that it needs in memory.
And that could be because of big memory grants. And if you have a lot of big memory grants and you could be seeing resource semaphore waits. So, um, that’s, that’s usually where I start. Like, you know, um, it did, if you’re, if you’re just looking at a server for the first time and it’s been up for a long time, it’s not as valuable, which is why a good free monitoring tool that trends your wait stats over time would be really helpful for you to figure out where concurrency problems might be bubbling up from. Uh, and then, you know, while, while you’re in there looking at, at weights, you, you, you might spot some weights that start with LCK, the lock weights. Uh, and you might start looking in your free monitoring tool that you got from code.erikdarling.com. And you might start saying, Hey, uh, it’s, it’s got the block process report and it’s got the XML deadlock report in here for me. I can, I can figure out so many things now. All right. So that’s, that’s what I would do if I were me. And if you were me, that’s what you would do too. What an amazing coincidence that is. All right. Let’s see here. What tuning advice do you think does more harm than good overall? Uh, I’m going to talk more in depth about this in another video, because there’s a lot to say here. Uh, I think, um, looking at dumb metrics is, is harmful because a lot of the dumb metrics, um, they don’t really show you what the problem is. And so you end up doing things in sort of a scattered way that don’t address the problems you actually have. I think that’s a, probably one of them. Uh, you know, other stuff like, I just had a covering index, you know, that, that whole genre of dummy. Ah, I hope you fall off a building. Anyway. Uh, yeah, there, there’s a lot of it. Uh, but I’ll, I’ll, I’ll, if you don’t mind, I’m going to park this one and we’re going to, uh, do a longer video about that. I’m going to even write something. All right. Why does SQL Server sometimes make obviously dumb choices and how do you reason about them? Well, there’s, there’s a reason for everything. If, if you’re, if you’re, if by dumb choice, you’re talking about, uh, queries, well, it’s all down to costing, right? There’s only, there’s only so much time and effort that an optimizer can spend figuring a query out, uh, things that may be obvious to you as a human being who has vast quantities of time with which to explore various aspects of the underlying data and the indexes and, uh, many other things involved, uh, that, that make up, that comprise or compose. I forget which one.
One of those, uh, that, that, you know, make, make, make your system a big circle. Uh, they, the optimizer can’t look at all that stuff very quickly and come up with a query plan for you. It’s, it’s on, it’s on a timer. It’s on a budget. Well, not exactly a timer. It’s on, it’s on a budget.
Um, and it doesn’t want to spend a lot of time figuring like, you know, coming up with a query plan. And so sometimes, uh, you don’t get the best possible plan. Uh, sometimes it is, uh, that SQL server, uh, explored a path that you think is, or that you, you might observe is much faster, uh, but it costed that path out for some reason or another. And other times you might just find that the optimizer, uh, exhausted its budget and did not get to that obviously good path that you have surmised from looking at things, maybe testing the query with various hints and all that other good stuff that, that you do because you’re a smart person with unlimited time on your hands and the optimizer is something that has to spit out a query plan quickly so that management doesn’t start yelling about stuff, right? Cause if you’re waiting 30 minutes for a query plan, you probably wouldn’t be psyched. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you off in tomorrow’s video, uh, where I’m going to provide some technical details, uh, that I’ve been, it’s a, it’s a video that I’ve been meaning to record for a while that, uh, I just got around to you. So some technical details about something, well, something special.
Anyway, goodbye. Enjoy yourselves while you can. All right.
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.