SQL Server Performance Office Hours Episode 85 – Lisbon is for Lovers
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:15 – Optimized Locking Discussion
- 00:03:38 – Server Restart Benefits
- 00:05:47 – Query Identification and Throughput
- 00:07:56 – Parallelism and Spills
- 00:09:23 – Vendor Application Tuning
Full Transcript
Erik Darling here with Darling Data, coming to you today from Lisbon. I’m not sure how much of the background you can see out there, but we’ve got a view of the replica Golden Gate Bridge and the replica Rio de Jesus Nero thing in the background. Also in the background, traffic and a train and an airplane. So again, no matter how far I go from New York, I never seemed to be able to escape city sounds. Except when I was in the Azores and the only thing allowed there was the ocean, which I suppose was a nice change of pace.
Anyway, we’re going to keep clearing out the office hours queue here. And we’ve got five more questions to deal with from my lovely, adoring fans who so far have been kind enough to not stalk me while on family vacation.
anyway I’ve never seen you suggest optimized locking as a solution to a problem do you dislike it no I like it just fine it’s just so few people are in a position where they can use it it there’s not a lot of point to recommending it right it’s like it’s SQL Server 2025 it’s you know I guess if you’re in you’re unfortunate enough to be in Azure you you might find yourself using it by accident. So if it’s solving a problem for you, great, but recommending it, you’re either there or you’re not. It also solves a very, I would say, a very narrow class of problems. Just as a small for instance, when I was redoing some of my locking material for a past webinar and for my session materials in Seattle coming up in November and what it actually broke exactly two of my demos where I had locked there was just a single primary key being locked. So I guess if your problem is with a single primary key being locked, well, there you go. I think the one thing that I do like about optimized locking from an outside perspective, and this is not because of the feature itself, is that the feature documentation pretty explicitly says this works best if you enable read committed snapshot isolation. So further legitimizing read committed snapshot isolation as the wave of isolation level future is, I guess, a nice side benefit to that.
All right. SQL Server sometimes runs better right after a restart. Why does that happen?
Well, do you sometimes run better after a night of sleep? I know I do. I’d like to get one of those sometime. You know, you clear a bunch of junk out. Who knows what was going on on that server?
Who knows how long that server had been up? Sometimes you clear a bunch of stuff out, you get some new plans and cache, SQL Server is feeling good about itself. It’s the same thing. Sometimes that works really well.
I don’t recommend it as a performance tuning thing. Don’t just restart your server every hour, but every once in a while, maybe patch things up. Maybe don’t, I don’t know. Restart your server, give it a fresh perspective on life, let it take a little nap.
sometimes that does work pretty well. How do you identify queries that are killing overall throughput but not necessarily slow themselves? I’ll usually look in query store for things that have a very high execution count because for me those are usually the things that just keep CPUs unnecessarily busy and give other queries some sort of fits when it comes to co-scheduling things.
So that’s usually what I look for. You know, like in there, you know, you might find some stuff that could use a little bit of help, but usually those queries are very fast.
And the options there are, of course, usually, usually, but not always things that you would want to control outside of SQL Server. You would either want to implement some caching for them, or maybe you would want to rate limit your various application APIs and whatever, just to sort of take some of the burden off of SQL Server and not let the queries execute as much.
Those are usually the ones that meet that criteria. you. All right. Why does increasing parallelism sometimes reduce spills? So, well, there are a few ways you could go with this. I would say that that is maybe generally not the experience that I have.
You know, because like, okay, so like in SQL Server, all queries start as serial execution plans. That’s when you get your memory grant. If SQL Server later chooses a parallel plan, the memory grant gets divided up amongst threads.
So, you know, if there’s very uneven parallel row distributions across threads, you can see spills. I would say the only time that that is sometimes different is if you increase max stop and all of a sudden SQL Server thinks oh well with max stop set to 8 instead of 4 the parallelism has greater benefit and now we’ll actually choose a parallel plan and sometimes the parallel plan that gets chosen might have some additional memory consuming operators in it that might increase the memory grant and then you might get like just a plan with a better suited memory grant to what you’re doing. It’s also possible that you know you might see plan changes around like you know you change MacStop you know SQL Server is like well new dot new plans.
You might you might get a plan that now has batch mode on batch mode in it. you know you might get just any number of things might change you know maybe I don’t know with all the memory grant feedback stuff I you know this stuff generally tends to sort itself a little bit more more easily than it used to or a little bit more automatically than it used to but my general experience has not been that increasing parallelism reduces spills so anyway all right the final question here how do you approach tuning vendor applications oh boy well you can’t change queries or indexes much uh well i i change what i can uh you know if if hardware is uh impacting the way queries run then I’ll usually suggest hardware changes. Sometimes you can change other things about the database, sometimes it’s the compatibility level, sometimes it’s the isolation level.
You know, there are all sorts of things that you can do, but like, you know, if you can change things but not change things much, you know, change things to the greatest degree you can. But, you know, it’s not uncommon for me in my consultancy to start working with customers of software vendors and have those customers refer me to the software company to help them fix the application more broadly. You know, usually the people who call me up are, you know, people who are like one of the biggest consumers of an application. And so when they have problems with it, the application vendors tend to listen. You know, there’s sort of a joke in the, I guess, whatever space that you never want to be an application’s biggest customer.
I think that is somewhat generally true, but it does come with some benefits. Like you can twist their arms a lot more and you can refer young, handsome consultants with reasonable rates to your software vendor to help them tune their applications that deal with SQL Server.
So if you are out there and you’re using a third-party vendor application and it is struggling with SQL Server, you know, we can always chat about how I can maybe work with that software vendor to help them make things go faster for you. Because that’s one of the things that I do. All right.
Anyway, those are the five questions for today from Lisbon. tomorrow we’ve gone to Kashkaj where hopefully it will be a little bit quieter and we’ll have some more wonderful scenery but for now thank you for watching, hope you enjoyed yourselves I hope you learned something and I will see you tomorrow
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.