SQL Server Performance Office Hours Episode 70
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:00:36 – Tuesday Office Hours
- 00:01:17 – Performance Monitor Questions
- 00:05:08 – Temp Tables and System Performance
- 00:09:42 – Table Variables Usage
- 00:11:32 – Conclusion
Full Transcript
Erik Darling here with DarlingNada, and I do apologize if this video is not my most illustrious effort, the air quality in New York today, it is like breathing dirt, and my insides are burning here, so I don’t feel great, but I’m going to see how this goes anyway, because we’ve got to do office hours, because that’s what we do all the time. On Tuesdays, we love Tuesdays, don’t we? Down in the video description, if you would like to ask me office hours questions with your whole hand, you can do that, there’s a link down below, well that’ll work, let me move over a little bit this way, I think I moved my camera or something, no, I just lose a little finger.
You can ask me office hours, there’s a link down below, right in there, somewhere. You can also find ways to interact with me that will cost you money. Like hiring me for consulting, or purchasing my training, or becoming a, what do you call it, supporting member of the channel, there we are, like PBS, right?
I’m not sending you a tote bag, though. You can also, if you don’t feel like directly interacting with me in any one of those numerous ways, which will sadden and depress me, and I don’t understand why you’d want to hurt me in that way, you can also do stuff like like, subscribe, tell a friend. We are around the 85.
500 subscriber mark, which puts me on course to maybe break 10,000 and finally surpass that damned Amiga repair channel once and for all, so, you know, if you can do that, if you’ve got burner accounts, that’s fine, too. Also, down in the video description, you will find a link to my free open source SQL Server performance monitoring tool. That’s right, free open source SQL Server.
Server performance monitoring tool does all the stuff the big boys do, slightly different ways, of course, because I’m like the engineering team is me, the product team is me. That’s why I’m a monitoring tool mogul, though, can’t beat that, but I just released version 3.0. It’s got a lot of neat new stuff in it.
I’ll probably do a video talking about that at some point, but I got to talk about this Learn T-SQL stuff and do office hours for a little bit, so we’ll come back to that. But, yeah, it’s time to answer some questions, so we’ll go over to our Excel file. Maybe we’ll pop in our monitoring tool for a minute here, and we’ll just take a quick gander at the joy and the beauty of my monitoring tool.
Look at all these wonderful charts and graphs. Now we got queries, and there we go. I’ve been trying to do some work on the UI to make it a little snappier.
So now the clicks are a lot faster. Sometimes it takes a second to draw the graphs, so hopefully that’s a reasonable trade-off for most people. There’s nothing in these graphs, because this is the oh crap section.
You don’t want to see charts and graphs in there. You want those to be empty, because that means nothing bad happened. Anyway, oh yeah, Excel, that’s where the questions are.
All right, here we go. May I hear a story? Oh, why is that dot so big? Let’s shrink that down a little bit. There we are. That’s a little bit.
That’s more reasonable. There we go. May I hear a story of when you have saved the day by removing merge? Yeah, save the day by removing merge. I don’t know.
I mean, I’ve seen merge in all sorts of places where it didn’t belong. Mostly in very large ETL processes where it was just really bogging and dragging them down. And yeah, just switching those to use stock and standard insert update patterns.
It’s made a big difference. I don’t know. It’s like any other query tuning thing. It’s like, you hear a story about how you save the day by adding an index?
Ah, sure. I had an index and the query went from taking four days to four seconds. It was quite a time. I don’t know.
It’s not my most exciting moments. All right. Hey, Eric. Hey, you.
How are you doing? What are your thoughts on HTAP? Are there any solutions out there that run Transact? Transactional and analytical workloads equally well? Yeah, you know, I don’t do a lot of work with like the databases specifically in that space.
I’ve heard good things about TIDB, if that’s even how you say it. I’ve heard good things about single store. A lot of people really want to love ClickHouse, but I don’t know if they actually do love ClickHouse.
Personally, I think SQL Server does a pretty good job with both, you know. We’ve got probably a bestseller. We’ve got a best-in-class relational engine for OLTP stuff.
And, you know, we’ve got column stores and whatnot for the other stuff. So, depending on, you know, what you need to do specifically, SQL Server might be a good choice for you. Which I assume you’re using since you’re asking me.
So, why not prop up SQL Server a little bit? It was crazy. I think like MSBuild didn’t have anything about SQL Server. It was all fabric.
And, I don’t know, AI and stuff. So, that whole SQL Server 2025 from ground to cloud to fire is skipped over SQL Server, apparently. I don’t know.
Anyway. That’s a question about performance monitor. I am using performance monitor to have configured alerts to the UI on my laptop. The alerts work, but only when my laptop is connected to the network. Yeah, that sounds about right.
I’d like to set this up in a more reliable 24-7 manner. Ideally, on a dedicated VM. So, Teams. Yeah, so, right now, yeah, that is the recommended approach would be to use a dedicated VM with auto logon and launch. That is the best I can do right now.
Long-term architecturally, my plan is to get rid of what I refer to as the full dashboard. That’s the one that creates a database on a SQL Server and uses store procedures. And, what not, to log things to that database.
Long-term, my plan is to maintain a truly light version of the light dashboard and then make the light dashboard run a Windows service type thing. So, it would be headless, and it would be running in the background and doing stuff. But, that is a pretty big architectural shift for me.
And, just being one person who is pretty busy. I don’t know exactly when that’s going to happen, but that is my longer-term plan for this. So, right now, it is a bit clunkier than I want with the light one.
The full dashboard would provide you with that. It doesn’t require all the other stuff since it’s running normally. But, yeah, that would be the way to do it.
How do you tell if temp tables are helping or hurting overall system performance? Well… Yeah.
I mean, I suppose the most obvious thing would be to look for tempdb contention and make sure that it is properly ascribed to the use of temp tables. That would probably be the most obvious one for me.
If you are rewriting store procedures to use temp tables, and you are noticing that your store procedures generally get better faster and stuff, at least when you run the F5 and run them in isolation.
Then you would see other metrics potentially go down, like the annoying ones, like the queries don’t take as long, their duration goes down. Maybe they don’t use as much CPU or something like that.
Maybe they get better execution plans or whatever. And so metrics around stuff that queries use, aside from tempdb, would improve. You might see tempdb usage go up, of course.
Right? Right. I mean, temp tables are an ephemeral part of the workload, and their rather rapid creation and destruction is expected. I would say that I would keep an eye on the usual tempdb contention suspects, the page latch, ex and up weights, maybe even sh occasionally.
But for the most part, yeah, I think that’s really what I’d keep an eye on, is like weight stats there. Monitor for typical tempdb contention. Monitoring for tempdb usage would be useless there, because if you’re using more temp tables, then obviously tempdb allocations and stuff would go up.
All right. Are there any real workloads where table variables actually make sense? Yes, but they are quite rare.
I think you would have to be putting data into temp tables in a context where a parallel execution plan, because remember, the two things table variables have that are downsides when compared to temp tables is there are no parallel execution plans when you’re modifying a table variable, and table variables do not maintain statistical histogram information about the data that lives in the columns.
The best you can get is table cardinality, even when you index a table variable. So the context would have to be the data load into the table variable. Right?
It would not, the speed of that would not have to be dependent or reliant in any way on a parallel execution plan. And that table variable would not be used in a way relationally where the lack of statistical information would result in a subpar query plan.
So that pretty much means that as soon as you start joining table variables off to other tables where, like, things like histogram comparisons. Comparisons to make join cardinality estimates, or even knowing what data lives in the table variable to filter on it and then do some other stuff.
As soon as you start doing that, table variables tend to become a bad choice. Of course, there are all sorts of edge cases, and maybe sometimes SQL Server comes up with a better query plan.
But for the most part, you’re just not going to see that play out. I’m sure that you can find some example of that. In the greater world, and you can say, Eric, you’re so wrong, but I admit that they exist. I’m just saying that they are not the majority of the cases.
All right. I’m going to go spray some more stuff up my nose, and that is not a drug reference. Well, I guess oxymetazoline is a drug, but it is not a banned substance. 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 learning T-SQL with some guy named Eric. All right.
Thank you for watching.
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.