SQL Server Performance Office Hours Episode 79 – Dang is it hot again
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:28 – TempDb Contention in 2022+
- 00:03:57 – Serial Plan on a 32 Core Machine
- 00:06:04 – Batch Mode Considerations
- 00:08:45 – Memory Pressure Indicators
Full Transcript
Erik Darling here with Darling Data, and it is back to being a sweltering madhouse in Paris. And, uh, yeah, I’m having a real tough one today. It is brutally hot out there. And, um, as good as white wine is, it is not very hydrating. So, I’m just feeling a little weird. Anyway, uh, we’re going to continue to clear out, uh, the office hours, this cache of questions that have built up, uh, while I’m, while I’m traveling on vacation. Uh, we got a few more days left here in Paris and then off to Portugal and, well, uh, destinations beyond, as they say. Anyway, the first question of the day is, is TempDb contention still a real thing in 2022 plus or mostly solved? Uh, it can still be a real thing, but I, well, let’s do two things here. One, let’s hand it to Microsoft because they did, uh, really, cause they have done a lot of TempDb work over the years. Uh, probably most notably the 2019 feature, uh, the in-memory TempDb metadata. That’s good. I, I like that feature. Um, when it works well, it works well. Uh, I, I have run into some, I mean, I think earlier on I did run into some very strange, uh, like memory leak. Everyone says they think they found a memory leak in SQL Server.
I’ve, I’ve, I’ve run into a couple of real bad memory issues in SQL Server. Um, I had one client, uh, who turned it, had to have an availability group and I’m not saying the availability group is part of it, but, uh, I hate availability groups. So, uh, let’s let them share the blame a little bit. Um, then, uh, the primary node, uh, they had the in-memory TempDb feature enabled on the secondary. They did not. And every three, seven, some odd number of days, uh, uh, the, the primary node would, would, would perform it just completely eat, eat, eat, eat, cake, uh, and, uh, uh, performance with tank. And they’d fail over to the secondary. Everything would be fine for a while. Uh, and then they’d be like, ah, everything must be cool. Let’s fail back over to the primary. And then, uh, some modulo odd number of days, uh, would pass. And then, well, things would get weird there again. Uh, so I, I, like I said, I have run into some, some, some not great stuff with it, but you know, I, I deal with, uh, the strangest parts of the SQL Server universe. I do not deal typically with very normal things that most normal people would deal with. So I’d say for you, uh, yes, it probably is solved, especially with the in-memory metadata feature. Um, other people, they need some extra help. All right. Next up. Uh, how bad is it to rely on forced plans for months or years?
And it’s not bad at all. As long as they still work well, what are you worried about? Are you going to enforce them and deal with that hell again? I don’t know. It seems like a bad call to me. It’s not, it’s not how I would want to do things, but you know, maybe, maybe you live a little bit, maybe you sail a little bit closer to the wind than I do. I don’t know. Uh, it could be, could be a real wild and crazy kid over there. I don’t know.
Uh, let’s see here. Uh, well. Why does SQL Server sometimes choose a serial plan on a 32 core machine for huge queries? Well, he said, he said the word sometimes. So I’m going to assume that there are times when it chooses a parallel plan, which most likely means that there is nothing directly inhibiting a parallel execution plan like a scalar UDF or inserting into a table variable, uh, or rather a non-inlineable scalar UDF or, uh, inserting into a table variable or something.
So, uh, sometimes it, again, these questions, every time someone asks, it all comes down to costing. It all comes down to the optimizer costing queries and figuring out if parallelism would be a useful, uh, addition to the query. Uh, remember, remember that, uh, in order for a parallel plan to occur, a number of things have to happen.
Aside from, uh, just there not being any direct inhibitors to a parallel plan being used, uh, the, the cost of your query plan still has to break your cost threshold for parallelism. And then SQL Server has to land on a parallel plan that is cheaper than the cost of the serial plan that it began with. Remember, all plans start as serial execution plans. Parallelism is a choice derived later in query optimization.
So, uh, it, it most likely the cost just didn’t work out. Uh, the cost math just didn’t work out for your query to get a parallel plan sometimes. All right. Uh, next up over here.
Uh, when is batch mode actually a bad idea? Um, so I think, I mean, it’s bad idea. I mean, there are certain queries where batch mode is less useful.
I think one of the things that, that always gets me is, um, uh, batch mode bitmaps in row mode plans or in mixed, uh, like batch mode on rowstore plans. Because, uh, you see like a filter operator after a big clustered index scan and you’re like, well, that’s, that’s nice, but it didn’t quite work out. Um, so I, I would say like, you know, um, mostly with smaller queries where it’s just not necessary, uh, is where things get a little weird.
Um, plans that are prone to spilling, uh, on a sort maybe, uh, cause, uh, batch mode sort spills are, uh, just absolutely delinquent part of the engine. And I’m not sure why that hasn’t been fixed yet because it’s been a known problem for years. They, they are, they degrade like 500 times worse than row mode spills.
Um, so I don’t know. There’s, there’s certainly some times when batch mode is not the best choice. It was like a bad idea.
I don’t know. Query by query basis. Like, like, like a lot of the Mets lineups listed as day to day. All right. Uh, how do you, shut up motorcycle.
I think you’re so cool on your Parisian motorcycle. Uh, how do you identify memory pressure before the server completely melts down? Well, it might start with small clues.
I think probably the, the, the best sign of memory, memory pressure starting to, to line up, uh, would be in your wait stats. Uh, if you have lots of page IO latch, the IO is key in there, underscore SH or EX waits. Uh, that means that, you know, your server is already, uh, potentially under memoried.
Uh, and so when, when you see, uh, queries just routinely, uh, needing to go to disk for stuff, that is most likely a good indicator that some more memory would help. Of course, other things like indexing, compressing indexes, archiving data, stuff like that can, I’ll contribute, but, you know, um, in general, uh, you know, that, that would be a pretty decent place to start.
Apart from that, uh, you know, you might see either the resource semaphore, you might see the resource semaphore waits start to crop up a bit. Uh, those are always, uh, a potential thing to show in there.
Um, so, you know, that, that wait stats is usually where I would start there. Um, you know, especially if you, uh, have queries, like even if you don’t have the resource semaphore, if you have queries that are asking for very big memory grants, that memory has to come from somewhere.
That memory generally gets stolen from the buffer pool. So queries get more reliant on disk. So there’s lots of reasons why you might, um, uh, lots of reasons why you might start looking in wait stats initially to figure that out.
All right. I think we have one more here. Uh, can temp tables cause blocking in user databases or only in tempDB?
Well, technically only in tempDB, right? But, you know, most queries don’t run in tempDB. So if you have contention that is causing blocking in tempDB, uh, like on, on user, on, like temporary objects or whatever, uh, then queries in user databases will might be the ones getting blocked.
So, uh, it’s an interesting question though. Look, what is the locality of the blocking, right? Because the resource contention is in tempDB with the queries running in the user database.
There’s all sorts of interesting things to consider there. But, um, I think unless you’re using a global, global temp table, then, uh, that multiple queries might try to modify, insert, update, delete from, uh, then that would be a different thing. But, uh, if you’re using global temp tables, God, God help you.
You have, you have done something strange in your life. I don’t know that there is help for you. All right.
Anyway, uh, it’s, it’s too hot to keep this going. I need to go into the shade and I need to, to start paying attention to my glass of wine. So 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 continue our Parisian adventure, answering office hours questions. 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.