SQL Server Performance Office Hours Episode 81 – A Slightly Disordered Release Schedule
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:34 – SQL Server Performance and Key Lookups
- 00:03:27 – Lock Escalation Issues
- 00:05:32 – Query Store Runtime Discrepancies
- 00:06:49 – Latch Contention vs. Locking
- 00:08:13 – Worker Thread Starvation Detection
Full Transcript
Alright, are we taping here? Is this thing on? Alright, Erik Darling here with Darling Data. One last lovely go around here in Paris, France, where it is still amazingly brutally hot. I don’t know, I understand why people didn’t really argue when they were getting the guillotine. Man, it’s summer here, I would just, yeah, just take me. Maybe we can just get this over with. But anyway, we continue to get rid of office hours questions that have stacked up, stagnated, I don’t know, whatever, other ST words, stuck in this queue for a while. Because I don’t feel like doing demos while I’m on vacation. There’s really no ergonomic way for me to do that. So here we are. And let’s see, what is our first question as soon as, ah, yeah, wow, thanks, Google. Google Sheets, you’re being a real pal. SQL Server sometimes does millions of key lookups. How does that not instantly kill the server? Well, I mean, apart from the fact that SQL Server is, or at least used to be, a rather well-engineered piece of equipment here, there are plenty of safeguards that would prevent it from killing the server. For example, the key lookups might be single-threaded, right?
It’s just one CPU. One CPU is probably not going to kill a whole server, unless you only have one CPU, or you’re in Azure. Sorry, Azure. And then maybe you set a max degree of parallelism correctly. And so your queries don’t use, like, all your cores to do them. You know, there are all sorts of ways in which this might happen. But, you know, coming back to SQL Server itself, it’s a rather well-engineered piece of software in general.
You know, it’s pretty efficient with things. So doing millions of a thing for a CPU, especially a modern CPU, probably not going to kill a whole server. There are other things that will, like you, but, you know, key lookups, probably not going to do it. All right. Next up, are lock escalation issues still common or mostly a thing of the past?
Well, you know, people talk about lock escalation almost in the same way as, like, fragmentation and page splits, as if it is just some boogeyman waiting to come out from under your server’s bed and snatch it and bring your server into the shadow realm where it’s never to be seen again. But so, like, lock escalation is one of those things that, you know, you should obviously try to avoid unless you’re doing it intentionally, in which case, good job. You did something intentionally. Hopefully, well-informed choice there.
But for me, what’s more likely the thing is lock escalation might get attempted a lot, but there have to be no competing locks going on in order for that escalation attempt to be successful. So, like, just because lock escalation is attempted does not mean it’s happening. So, you know, is it common? I don’t know how terribly common it is.
Is it a thing of the past? No, it still exists in the product. But for me, I just think with a lot of highly concurrent workloads where a table or object level lock would really, like, show and hurt you, there’s probably a lot of other competing stuff going on that prevents that from happening.
You can do all sorts of boneheaded things that would, you know, make it likely to be attempted and keep being attempted and, you know, maybe get lucky and happen or unlucky and happen. But, like, you know, in that case, I don’t know, maybe at some point you would want it if you’re doing one of those boneheaded things like updating 10 million out of 12 million rows or deleting 10 million out of 12 million rows. Because I don’t know, right? All sorts of reasons why you might want it.
All right. Why does Query Store sometimes show totally different runtimes than what users experience? Well, I think in general, when the database is showing one set of numbers for sort of how things are going for users and like, wait, like how things are going within itself.
And users are experiencing something else. Then it’s probably one of those things where it’s not the database anymore. It’s not our friend SQL Server anymore.
Most likely there is something happening over on the app server side. It’s making it’s like, you know, you’re seeing that big, long spinny wheel. Maybe your company is AI ready and they have changed a loading screen to say thinking.
But most for me, mostly like when if I’m seeing everything very, very fast in Query Store or at least reasonably fast in Query Store, but users are still like, I don’t know, I don’t know, Eric. Every time I run this query, I’m sitting over here waiting for a half hour. But, you know, demonstrably in Query Store, you know, or like when I run the query, it is is not taking a half hour.
Then to me, that smells like an app server problem, either perhaps an overwhelmed or under hardware budgeted app server or perhaps the software itself doing some sort of terrible stuff to that data. Once it shows up, crunching it or doing whatever to it, calling out to external services. I don’t know the millions of things in an application might do.
So you have to start looking generally somewhere else when that is the case. All right. When should you suspect latch contention instead of locking?
Well, it’s an easy one when the weights are latch and not lock. Right. So if the primary thing you see queries waiting on is something that starts with the word latch and all uppercase with some underscore.
Other two letter thing after it, then you’ve got latch problems. If your weights all start with LCK and an underscore and a bunch of other mishmash letters afterwards, then you’ve got locking problems. It’s a good one there.
All right. Final question for today. Lucky number five. Why? Why? Oh, sorry. I keep hearing about worker thread starvation, but I’m not sure how to actually detect it. Well, that’s the funny thing about worker thread starvation is if you’re trying to detect it, you’re going to need a worker thread.
And if your server is starved of worker threads, it might not be so easy to detect. So what you would need to do is either on your own, of your own volition, you would need to start sampling weight stats and looking for jumps in threadpool waits and probably looking for where the average milliseconds per weight is rather on the high side. The other thing that you could do is perhaps, you know, a young, handsome SQL Server consultant who makes a free open source monitoring tool.
And, well, I can’t claim that my monitoring tool can work around thread starvation because it can’t. I don’t, like, try to prioritize my queries above all the others. But what that will do is show you weight stats and it will show you deltas of weight stats.
So you can look at a weight stat graph and probably what you’ll see is things going, you know, looking weight statty. And then there might be some blank space in there because my queries won’t be able to run because worker threads are starved. And then what you’ll see after that is a big spike in weights, perhaps threadpool waits.
So you can either do it yourself or you can trust me to do it for you. You know, again, free monitoring tool. It’s pretty good.
Does all the stuff that the big fellas do, but at a fraction of the price. And by fraction, I mean free. So that would be my recommendation there. All right.
Anyway, this is the last one from Paris, leaving for Portugal. And hopefully have some other at least scenic backgrounds. I don’t quite have the lack of shame that it requires to go out in the world and record one of these. But I’ll do my best.
All right. Thanks for watching. Hope you enjoyed yourselves. I hope you learned something. And I will see you in Portugal. All right. Goodbye. 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.