SQL Server Performance Office Hours Episode 75
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction to Query Tuning
- 00:02:31 – Force Seek Hint for Index Usage
- 00:05:49 – Prioritizing Tuning Work
- 00:08:53 – Identifying Performance Issues with Wait Stats
- 00:11:16 – Underestimating Row Counts in SQL Server
Full Transcript
Erik Darling here with Darling Data, and we are going to have ourselves an office hours in which I do my Darling Data darndest to answer your very, very important SQL Server questions. We have a nice time with this, don’t we? We enjoy ourselves here. If you would like to ask your questions for office hours, there’s a link down in the video description where you can do just that. That is free, that is online. the house. But if you’re going to do that, you should also do this and make sure that other people get to see your great question and get a great answer, right? Get a top answer. There are all sorts of other helpful links down in the video description as well. You can hire me for consulting, buy my training, at a discount, right? We offer coupon codes down in the video description for people who love me enough to visit this channel, and especially ones who watch the intro here. And you can also, if you feel like you’re getting like four bucks a month worth of entertainment information, knowledge, anything like that, you can also choose to support the channel up there. If you are in the market for free SQL Server performance monitoring, and I’m only lying to you a little bit here because it’s not just performance monitoring anymore. I got suckered into adding in AG monitoring, and I didn’t like it. I’ve got, I’ve got, I’ll show you in a minute, but oh God, it’s a annoying. AGs are the worst, man. But if you, if you want free SQL Server performance monitoring, I’ve got it open source. You can see everything it does, doing everything that, you know, you would care about knowing how a thing works.
It gets all the interesting performance metrics for your servers. And the new version that is a headless Windows service backed by a Postgres database is available now. It can, it monitors an entire fleet. I think up to like 500 servers was my benchmark test on it. So that all went pretty well. And that’s, that’s, that’s, that’s up and running now. It’s a replacement for the old full dashboard that would like create a SQL Server database and you have to like put it on your production server. That stunk. I didn’t, I didn’t love that. I thought that it would be cool to like, hey, back up your data and send it to someone to analyze. It turned out that wasn’t a thing. So I was wrong there, but I think I’m right about this one. And, and, um, you’ll see, uh, if you, if you go get it for free, uh, that it can easily monitor many, many servers, but let’s go. I’ll show you that real quick. Uh, so I’ve got Docker down here and Docker has, uh, my AGs in it. Uh, and boy, that’s annoying. Uh, but here’s the, here’s the, the, the, the, the refreshed, uh, monitoring tool surface, uh, for, um, for the, the, the, the darling, uh, performance monitor.
And, uh, here is our availability groups tab, right? And that’s, this is, I mean, there’s not a lot going on with my local availability group because it’s a tiny little thing in Docker, but, uh, it is up there and working. So, uh, we, you, you do have, we do have that going for us. Oh, that’s a fun time. Anyway, let’s answer some questions here. That’s what we do. And number one, had a situation where hundreds of spids ended up in a sleeping state with one open transaction.
Hmm. This turned out to be a underpowered app server. What was happening here? Uh, it sounds like you figured it out. It was an underpowered app server, uh, app, just not picking up the thread again to close it off. Uh, yeah, it could be that. Um, you know, uh, I suppose like you could have like the application, uh, server version of like thread pool. A lot of the times when this happens though, you’ll also see async network waits pile up because sometimes it’s the app server being really overloaded by the results that SQL Server is returning to it. Um, you can do a bit to sort of help your app servers out, right? Uh, if you control the application enough, you could add in sort of like a rate limiting and maybe like cap off result set sizes.
So they’re not blowing your app servers to smithereens, uh, whenever queries run, uh, there can, there can be two useful ways of, of sort of, uh, making sure that they stay happy and healthy. Um, but yeah, that’s, that’s a fairly common thing. I, there was one consulting call I was on where, uh, this is a long time ago. This is a bare metal days of things, uh, where there was an app server and balanced power mode and turning it into normal power mode instantly just fixed all the SQL Server problems.
So that was a good time. Uh, is there a reliable way to, to, to detect parameter sensitivity automatically? Uh, not built in, you would have to do the automation yourself. Um, I would look in query store. Uh, you can either group stuff up by like a plan ID or query plan hash. Uh, you, I mean like you could try query ID or, or, or query hash, but, uh, if you, if your query is producing multiple plans, then, uh, you know, you’re not really getting like, uh, like a, an accurate picture there. Um, you know, it could like, it might, there might still be parameter sensitivity, but if your query is generating all sorts of other execution plans, then at least sometimes it’s, it’s, it’s just like, it’s recompiling or something.
Right. And you’re getting different plans for, for different things, or even maybe very similar plans for, for different things. Who knows? You might not have a good plan to begin with. Uh, but I would probably want to look at, um, you know, the plan ID or a level or the, the, the query plan hash level. And what I would want to look at from there is if there are really wild swings in, um, in like min and max for like things like CPU and duration, not logical reads. Cause those are, those are a stupid proxy metric and you’re, you’re not a stupid proxy person. Uh, you look directly at the things that matter like CPU and duration. Uh, so you, you, you do that and then you might be able to detect pretty easily if one plan is sometimes running very, very long and one plan is sometimes running very, very quickly.
Um, averages might not be a great thing to look at there cause it would just skew things up for you, right? Or rather it would just not, it might just smooth things out and you wouldn’t see like the, the wild, uh, variations in performance. All right. What’s the most common reason good indexes still don’t get used. It’s always costing. Every time someone asks a question, it’s like, it is always costing.
Uh, SQL Server looked at your index and said, I think you’re too expensive for me. I’m going to use a different one instead. Uh, and then that’s when you have to step in and tell SQL Server you are wrong. Your estimates are incorrect, but you, you know, of course, careful testing there. Um, you know, the thing is I, I don’t love index hints so much. Uh, you know, like you, you name an index and all of a sudden, I don’t know, maybe someone renames an index or does something to the index.
And now you’ve got this query using this index, you know, by hint that it can’t, uh, do any, you can’t do anything useful with anymore. Um, my, my stronger preference is, uh, you know, if you’re, if you, if it, if it gets you the correct plan shape and the correct index usage, there’s something a bit looser, like a force seek hint. Uh, because then, you know, you’re just saying, Hey, SQL Server, you, you, you should force, you should seek really here by promise you.
Uh, and then, um, you know, uh, hopefully it’s still use the index you want, but that, that’s an adventure for another day. All right. Uh, how do you prioritize tuning work when literally not figuratively, literally everything looks slow? Um, well, there are a few, there are a few different ways to think about this, right?
Um, you know, you could start with the things that users complain about the most, um, because once you get the users off your back, you can, uh, certainly make time for whatever pet peeves you, you, you have derived from your, uh, performance analysis. Um, but sometimes it’s not like, sometimes it’s not directly what users are doing.
Sometimes there’s like other orchestrated tasks in the background that are, uh, that are just impacting user workloads. That’s another thing that can happen quite often in SQL Server. Um, and then another way to think about it might be like, uh, if you, if you’re trying to like, you know, just unclog a server, uh, you might look at wait stats and you might look for, um, you know, probably your most prolific wait stats that, uh, that are related to, to query execution.
Uh, and you might try to tune around those. For example, if you have, um, like, so like for the CX waits, SOS scheduler yield generally also has to be very, very up high with those. Because if you just have a lot of parallelism, like, you know, it’s like, okay, there’s a lot of parallelism, but is it causing CPU pressure?
And SOS scheduler yield is a good pair, uh, thing to pair that the CX, CX waits with to make sure that, uh, you actually do have some CPU pressure on there. Uh, another one that I would look at are the page IO latch waits. If you have IO bottlenecks, um, you know, sometimes compressing indexes or adding in better indexes can get you around, uh, some of that stuff.
Um, but you know, uh, like those can also be signs that like the hardware isn’t effective for the workload as well. So for, as far as prioritizing tuning work goes, uh, it really depends, it’s, it’s for me, that’s very situational. Um, and for me, you know, a lot of the time it, it sort of depends on what signals I’m hearing from people, uh, that I’m working with about what, uh, what their priorities are.
And, you know, it’s not like you, it’s not like you always have to agree with them, but, you know, um, you know, like still, of course, do your analysis and, you know, uh, you know, point, point out thing, point out things that they might not have, might not be aware of that they, they might not be able to get to with their knowledge of SQL Server. Uh, but, uh, that, I think taking that signal into account is, is certainly worthwhile. So, um, for me, uh, you know, if like you just stuck me at a server and you said, tune whatever you want, um, I would probably look, I would probably look around wait stats.
If I got like, like high, like lock waits, if I had a lot of those, I’m going to start going after modification queries. Um, if I have, you know, really high SOS scheduler yield waits, whether it is accompanied by CX packet or not, I would go after CPU dominant queries. Uh, if I had high page IO latch waits, I would look for queries that could benefit from indexing or just an environmental change to page compress my indexes.
So that I had a smaller data footprint, both on disk and in memory, and I could fit more of my indexes in memory and, and maybe even clean some indexes up with a wonderful store procedure like SP index cleanup. There are, there are so many ways to approach this. The mind boggles occasionally.
Why does SQL Server sometimes underestimate row counts by orders of magnitude? Uh, well, I mean, you know, you, you’ve got a list of things there that could be, um, you know, statistics being out of date could be one, right? That’s a very easy one.
Um, you know, you could have some sort of, some non-sargable predicate in there. That’s throwing up the, throwing up, throwing, uh, gasoline in the face of the optimizer, trying to make estimates for things. Um, you could be using some other, uh, thing in SQL Server that just generally makes cardinality, cardinality estimation difficult, if not impossible to derive.
Uh, local variables and table variables certainly contribute to that. Local variables because of the density vector estimate and table variables because they do not, they do not carry a statistics histogram on their columns the way regular temp tables do. Uh, that could certainly be it.
Uh, another thing could just be cardinality, cardinality estimation model. Um, if you’re on newer, a newer version of SQL Server and you are using a, uh, newer compatibility level, you’re using the new cardinality estimator, not the default one, and you might end up in a place where you are just kind of stuck, um, getting strange estimates from across, uh, many queries.
So, uh, that’s usually it. Um, you know, there, there could be other odd, odd reasons that you might run into something. But in my experience, those are usually, that’s, that’s where I’d start and that’s where I’d start.
Uh, I think, you know, updating your statistics is a nice thing to do for SQL Server. All right. Anyway, that’s it.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about some T-SQL-y stuff. All right. Thank you for watching. All right.
Thank you for watching.
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.