SQL Server Performance Office Hours Episode 84 – Adios Azores
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:30 – Parameter Sniffing Basics
- 00:04:52 – Resource Semaphore Waits
- 00:06:25 – Tuning Deletes
- 00:07:30 – Random Latency Spikes
Full Transcript
Erik Darling here with Darling Data, fighting this gimbal thing a little bit, which is trying to stay focused on me, but having somewhat of a hard time. We’re going to do a slightly different view out on the Azores today. Again, this gimbal thing is driving me up a wall. Alright. There we go. Nice view out over the Azores. Alright. Cool. Anyway, this is our last day here. We move on to Lisbon after this. And then, well, I’ll let you know when I get there. Because I don’t want any of my rabid fans to show up in these places and stalk me. It’s happened, well, really that only happens at conferences when people follow me into the bathroom asking me what they should set MacStop to. But it’s a story for another time. We’re going to continue to clear out the office hours questions queue as I travel around. Well, again, I’ll let you know when I get there. So the first one is, let me bring this a little bit closer here. How do you know when parameter sniffing is actually the root cause and not just a symptom? Well, parameter sensitivity does suggest a number of factors that one can consider. I think the primary one is that perhaps your indexes are not all so well aligned to your queries. You know, there’s not a whole lot you can do about data distributions. Right? It’s not like you can’t just delete data that, you know, makes your queries parameter sensitive.
As much as I often wish that I could just get rid of a lot of data that bothers me. Like, I don’t know, credit card bills, that would be a good one. But for me, it usually does suggest that there is some indexing that could be done to give the optimizer fewer choices as far as which plans it can use to execute your query. But you know, not just a symptom. I suppose it depends on where in the query plan, the parameter sensitivity sort of shows itself. Just as an example, let’s say that sometimes you’re the query, the optimizer chooses a nonclustered index seek with a key lookup. And that introduces a parameter sensitive element to the query’s execution. That would be a sign that indexing is more something that you should pay attention to most likely.
But then there are other times when let’s say sometimes you would have just a clustered index, a nested loops join in a clustered index seek. And other times you would have like a clustered index scan and either a hash join or a merge join. Then it’s not really an indexing issue. Then it is just, you know, SQL Server choosing different strategies depending on the amount of data that it has in there. In other words, there’s, there’s, there’s, it’s not a choice between like which index to use. It’s how the index is used and how the data gets joined together.
Usually in those cases, I’ll either force a plan or use a query hint to keep things on track. I think dynamic SQL is wonderful for sorting out a lot of issues like this. And I abuse that because there are just too many times when the optimizer refuses to stay on the right path for things.
And, you know, that parameter sensitive plan optimization just generally doesn’t do a great job. All right. Next question we have here. I see resource semaphore waits, but CPU and memory look fine. What gives?
Well, if you’re seeing resource semaphore waits, then I do doubt a bit that memory looks fine. CPU might look fine because just because a query asks for a lot of memory does not mean that it’s going to use a lot of CPU. They are independent resources and all that.
I would, I would want to know how you’re looking at memory where you’re saying it looks fine. You know, the obvious caveats around task manager not being a great view of how SQL Server is currently using memory. But if you’re seeing resource semaphore waits, then, you know, the obvious ways of dealing with that, if you’re on, well, if you’re on enterprise edition or if you, even if you’re on, if you’re on standard edition of SQL Server 2025, you have resource governor available to you.
The more memory you have in a server, the more useful having resource governor becomes because you can control. But so by default, SQL Server will give any query 25% of your max server memory setting for a memory grant up to. So resource governor allows you to control that.
You can drop it. You can drop that percentage down. That’s one, that’s one way of dealing with it from a workload perspective. But if you’ve just got a few individual queries that could use some help, you know, the, the normal query and index tuning advice follows there. All right.
How do you tune deletes that randomly take forever and block everything else? Well, you know, the, the, the normal ways. If you want to start from like an outside, outside view, you might look at indexes.
You could use my store procedure, SP index cleanup. You could look at indexes on the table, see if any of them can be removed as unused or deduplicated. So that you don’t have, that your delete is not responsible for deleting data from unused and duplicative indexes.
You could look at indexing from the perspective of, does my delete have a good index to locate the data that I’m deleting? That, you know, usually seeing a seek before you delete stuff is a good thing. And then the other thing would be trying to control how many, batching the deletes up.
So controlling how many rows at a time get deleted. Those, those would be the, the, the, the first few things that I would, I would do. All right.
What usually causes random latency spikes that only last a few seconds? It’s a broad question. Let’s just say blocking for that one.
Unless, you know, someone’s unplugging your network cables, which sometimes the gremlins do that. Okay. Final question for today is how dangerous is disabling auto update statistics in production?
Yeah, that’s, that’s not a favorite thing of mine to do. Um, uh, I’ve, I’ve, I’ve not run into too many situations where, uh, disabling, uh, auto update or even auto create statistics and manually managing that, uh, has been, um, a successful enterprise. Um, I, I, I, I’m not saying it doesn’t exist.
I’m just saying, uh, if you’re plugging SQL Server questions into, uh, a random spreadsheet like that, um, it’s probably not the course that you want to take. So, uh, that is, that is not, um, that is not a course that I would follow. All right.
I have to go pack my suitcases because I’m getting on an airplane. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you back. Well, today is Thursday. So we will be back Tuesday with another office hours video.
All right. 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.