SQL Server Performance Office Hours Episode 78 – There’s No SSMS In Paris
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:02:45 – Blocking Chains and Deadlocks
- 00:05:15 – CPU Low but Response Times Awful
- 00:07:39 – Plan Forcing Safety
- 00:09:26 – Statistics Updates Impact Performance
Full Transcript
Erik Darling here with Darling Data. It’s another wonderful day here in Paris, France, and I am coming to you from a slightly different window in the apartment. There are some windows where it would not be feasible to do any of this from, but we get a few good ones with rather nice views of stuff out there that, I don’t know, well, I get to look at and enjoy, so I’m sharing a little bit with you, but I’m probably not going to invite you over. Yeah, anyway, we got some questions to answer here from the fine folks out there in the SQL Server community. So the first one is, we see massive blocking chains during peak loads, but no deadlocks. Is that normal? Is something broken? And the answer is, I would say that’s fairly normal. Well, sometimes blocking does beget deadlocks. Quite often, blocking just happens rather naturally on its own without two queries getting into a fight over the same resources. Note that I said resources there because deadlocks can happen for far more reasons than the usual sort of embrace of death scenario like, you had to update table A, then table B, and this one tried to update table B, then table A. There are all sorts of things that are happening in the same way.
There are all sorts of weird things that can happen in there. Like even on the same table, it’s like you could see two queries deadlock if one tries to update index A and then index B, and the other one tries to update index B and then index A. And queries can even deadlock on themselves in a parallel query plan. Sometimes things get a bit mucked up in there. Most frequently around exchange op, well, I mean parallel deadlocks by definition happen around exchange operators. Usually when they are order preserving. So if you have a parallel query plan with merge joins or sorts or stream aggregates in there, they are more prone to parallel deadlocks. All right, next question. Sometimes CPU is low, but response times are awful. What gives? How does that happen?
Well, I mean, it’s something that I’ve talked about, I think, at least a couple times in recent memory here. You know, CPU being high is actually, I think, better because it means that everything is kind of running. I would take high CPU over low CPU and things being awful. Low CPU and things being awful could be any number of things. You could be looking at, you know, the usual blocking scenarios. You could be looking at threadpool. You know, you could be looking at your, I feel like I just answered this. Just ask me this question. You could be looking at things like your app servers being overwhelmed and just encumbered in ways that they were not meant to be.
So all things worth looking at. All right. Is plan forcing safe long term or just something to buy time until we fix real problems? So I assume by plan forcing, you mean query store plan, plan forcing, which in my experience is quite fragile and I would not rely on it long term. If that query ever gets a new query ID, you fail over, yada, yada, yada, yada. You are looking at plan forcing no longer working.
So from my perspective, it is a temporary bandaid. The one place where it is potentially not a temporary bandaid. And this would go for query store hints as well is if you are using an ORM rather than store procedures or even something like Dapper that allows you to write ad hoc SQL queries into a string. There are all sorts of places where I think link to SQL allows that as well to some extent. I’m not a developer, so I do not traffic well in these things.
But I would say it is a temporary fix unless you are in a position where from your position, you cannot change the code immediately. Query store plan forcing and query store hints are both rather fragile though, so be prepared to monitor that situation closely. All right.
Our app suddenly started timing out after a statistics update. Could a stats update actually make performance worse? Sure. There’s always that possibility. You know, I am in the camp that sticks at least pretty closely to up-to-date stats being the best way to allow the optimizer to make reasonably good choices.
Mostly because of the, like when you have data that gets added to a table and SQL Server does not have a representation of that data in the histogram, but users are querying that, the optimizer doesn’t have a really strong way of figuring out cardinality estimates for those off-road histogram steps. Other database engines like CockroachDB will do things a bit smarter. They do something a little bit more sophisticated where they’ll like look at like how many rows exist for like other values and be like, oh, well, I can infer, you know, cardinality will probably look like this for those newer values.
They project some stuff out. So SQL Server just, you know, on the legacy cardinality estimator, you get a one row guess and the new cardinality estimator, you get sort of like a density vector-ish estimate on the number of rows. It’s like 30% of the table or something like that.
But, you know, so like I do think that stats updates are important for those things. But I also do recognize that stats updates can cause issues in other places. For example, they might cause a plan flip where you get a worse execution plan afterwards.
That’s always a possibility. And, of course, if you run a stats update and all of a sudden a whole bunch of things need to compile plans and, you know, you don’t have a rather – no, I’m sorry. I was going on the wrong path of that.
And if you have to compile plans for stuff and you have a really highly concurrent environment, you can see a bunch of compile locks and all that stuff. So, you know, stats updates, they, you know, well, I do believe they are necessary. They can mess with stuff in new and interesting ways.
So, again, I do think you should update stats, but you should also monitor the situation closely. All right. Last question here.
How do you debug performance issues that only happen under real production load and not under the dev server load? Well, this is why I built a monitoring tool because all of the good stuff happens in prod, right? All the interesting stuff happens in production.
And, you know, not having active monitoring there, it leaves you just really blind. You know, the way that I try to tell people is if you can’t see a problem, you can’t solve a problem, right? These things need observability, you know, especially as DBAs are, I think, you know, shifting closer and closer to the SRE family of employees where, you know, you have DBREs and all this other stuff.
I think observability is far more important. You know, you’re not just checking on like, oh, did the backups finish? Did the maintenance finish?
Oh, do I have to patch this thing? Like you’re not doing just like dull ass production work anymore. You’re actually having to do like real stuff that, you know, keep servers up and alive and running. And, you know, having a problem and not being able to see deeper into that problem or have any record of that problem, you know, you might get notified of a problem far after it happened.
And by then, SQL Server may have been restarted and you may have absolutely no evidence of what was happening on the server at the time. So, you know, anything that you can do to increase observability is important for you because like setting up an extended event or something or a trace or something after the fact buys you nothing, right? Like you might catch the next thing if there is a next thing, but you still don’t know what caused the current thing.
So having as much observability into the current state of the database and current database problems, it just gives you such an advantage because you can not only have, you can respond, you can respond reactively more intelligently when you have that, that observability. But also it gives you the ability to, you know, just like forecast these problems, see what’s getting hot, see where, you know, see what’s really lighting up the server, you know, start fixing stuff before it turns into that. It’s just so important to have your SQL Server monitored.
And again, that’s why I built free SQL Server monitoring tools because, you know, I got sick of showing up to client sites and them not having a monitoring tool or them having a really crappy monitoring tool. I won’t name any names. You know who you are, every single last one of you. And like, just like, like, okay, like, well, you have a monitoring tool. Do you use it? No. Why? I can’t figure out what’s wrong.
Okay. Well, you don’t have a monitoring tool then, right? Let’s just over and over again. It’s that. So, you know, from my perspective, like I got real sick of that and that’s why I built my thing. It’s free. And there’s absolutely no reason not to use it. It’s for you to do your job better, right?
I ask nothing of you in exchange for using it except do your job better. All right. Anyway, that’s five questions. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. It wouldn’t be office hours without a siren.
And I will see you next Tuesday with another office hours video. All right. Thank you. 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.