SQL Server Performance Office Hours Episode 66
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction to Performance Troubleshooting
- 00:02:34 – Why Does a Big Machine Suddenly Start Choosing Serial Plans?
- 00:06:27 – Troubleshooting Weekly Performance Issues
- 00:10:27 – When Does Dynamic SQL Become Worse Than Static SQL?
- 00:11:28 – Indexing Foreign Keys and Delete Performance
Full Transcript
There it is. There’s the microphone. Erik Darling here with Darling Data. Having a grand old time. Not really. Anyway. Just kidding.
Monitoring Tool Mogul of the Year. Be on the front page of Monitoring Tool Mogul magazine smoking a cigar. It’s gonna be great. Anyway, it is time for office hours because it is, as they say in the Bible, Tuesday. I don’t think the word Tuesday is in the Bible.
Does that mean Tuesday doesn’t exist? I don’t know. Like Tuesday dinosaurs, the color blue? Where is any of that stuff? Where did it come from? Anyway, down in the video description you will find all sorts of helpful links to help you attain all of your life’s dreams and goals.
You can hire me for consulting. If you would like me to show up physically, virtually, romantically to fix your SQL Server problems, I can do that. You just have to hire me to do it. There’s a link that will help you hire me to do that down below, underneath me somewhere. If that’s too much for you, if you’re like, you know, I really just like this guy on video, maybe in person is a little too much, you can just buy my training.
It’s very reasonably priced, as my consulting rates are also reasonably priced, and you can just learn everything I know with this one weird trick. You can also support the channel. Either by becoming a subscriber. And we’ll answer five of those every time I do office hours. Also down in the video description, oh, look at that.
This is why I’m going to be on Monitoring Tool Mogul magazine. I’m not cheating on Beer Gut magazine. They’re actually, Monitoring Tool magazine is actually a subsidiary of Beer Gut magazine. So I’m not cheating on Beer Gut magazine with Monitoring Tool Mogul magazine.
It’s actually Monitoring Tool Mogul monthly. Uh, so I don’t know who, I don’t know who’s going to be next month. Maybe they’ll go out of business.
Couldn’t find anyone else. It’s one page. Uh, anyway, there’s also a link down in the video description. If you want to get my totally free, totally open source SQL Server Monitoring Tool, a bunch of good stuff gets collected in there, help you figure out performance problems.
Um, you know, the normal spade of stuff that you would get, uh, you know, resources, CPU, disk memory, all that crazy. Stuff.
And also if, if you, if you want to let your robot companions talk to your monitoring tool data, well, boy, how do you can get them chit chatting to the monitoring tools and there, and you can, uh, you can let them do some read-only analysis of your monitoring data.
Uh, coming up this year, I will be at data Saturday, Croatia, June 12th and 13th with an advanced T-SQL pre-con. I will also be at pass data community summit in the West in Seattle.
Washington, November 9th through 11th. And, uh, once we’re there, who knows, who knows what’s going to happen? Cause it’s going to be a crazy times, but anyway, we will continue our March through May.
It’s funny, right? We will continue our March through May and I will answer some questions. Now I’m going to drop this whole stick where I make jokes about stuff.
All right. So let’s see here. First up. I have been asked to look at adding created.
And last updated date, time columns to my main transactional OLTP tables. My, my, my, uh, that’s going to be fun for you. Uh, created can be auto-maintained using a default populated on insert, but how would you advise maintaining last updated a trigger?
Well, you could certainly use a trigger. I have used a trigger in many times to do that. Uh, I have a few blog posts where I outline, uh, some good ways to write triggers. Uh, you’ll, you’ll definitely want to do, uh, you’ll.
Check the row count immediately. And if it’s zero bailout, um, since this is, uh, a last updated trigger, you will also want to use the trigger nest level function to make sure that you are not updating your last updated based on the updated last updated column, getting updated.
That could be bad. Um, there’s also another thing that you can do. So, you know, we have temporal tables in SQL Server, and I’m not saying that you should use those, but you can add temporal table time tracking.
Columns to a normal user tables. Uh, I have a blog post about that. Uh, it’s called tracking row changes with temporal columns or something like that. Um, you, you, if you, if you, if you, I would say Google that cause Bing, man, that that’s like, like searching by asking someone who’s not really listening for advice or a question.
It’s, it’s bad. Uh, so, uh, check, check that out. If you need to, um, there are some imperfections with that.
Like, like when you, when, like, if you just, if you were to like, add the last updated column to your table, um, it would be null for things that have been updated. Uh, whereas w but with the, um, the temporal table thing, uh, the, the last modified date by default has to be something.
It’s not nullable. I think at least, at least that’s what I recall when I’m last messed with it. So it’ll look like when you add the column, it’ll look like everything was last modified.
Then, uh, you might be able to. I don’t know, maybe I didn’t try very hard. It’s anything that’s possible, but those are the two things that I would try there. Um, you know, you gotta be real careful with that stuff on a transactional tables because, uh, doing another update, doing an update on top of all the other stuff you can, you can run into a lot of deadlocks doing that.
You, you know, it’s, it’s a, it’s a perilous, perilous circumstance. Why would a big machine suddenly start choosing serial plans? For large queries?
Well, uh, for the same reason, the small machine would start choosing serial plans. It’s all costing all the way down, uh, the optimizer being the cheapskate that it is, uh, if your plan all of a sudden, you know, uh, or rather it looks at a query that comes in and, uh, it does all the costing stuff. And you’re either now your query doesn’t break the cost threshold for parallelism, or maybe it does.
But the, the cost. The parallel plan was more expensive than the serial plan. Then you’re going to get a serial plan, buddy.
That’s just the, the answer to these questions is always costing, right? It’s always optimizer costing and never anything else. I mean, sure.
There’s like settings that someone, some dummy could have changed. Like, you know, you could have changed cost threshold to like whatever the high value for like 32,600 and whatever. Uh, or you could, you know, someone could change the server or database level.
Max. Stop to one or, um, apparently a traffic jam outside, uh, you know, um, someone could turn off like scalar UDF in lining and, or at have added a scalar, a scalar UDF to a query or something, but there’s all sorts of reasons why, why, like parallel serial plans sometimes get forced into the equation. But if, if nothing like that has changed, then it’s all about costing.
Perhaps, I don’t know, you could always, uh, you know, and now I assume that the, um, the. Enable parallel plan preference use hint is now officially supported by Microsoft since they were using it where it’s still are using it in some of their code within SQL Server. So it must be okay for everyone else to use that.
But, uh, I added, uh, oh my Lord. Oh my goodness. Yes. You’re playing my song here.
How do you troubleshoot performance issues that only happen once a week for 10 minutes? I monitor them with a monitoring tool. Like, I don’t know, maybe this free one that I have. If I go.
Way down here. And if we, let’s just say we look at SQL Server 2025, uh, look at all this monitoring data that we have that might tell us what a performance problem might be like, you know, maybe let’s go look at weight stats. Look at all those weight stats.
Look at, Hey, look at there’s our 10 minute problem. Woo. We found it. Um, I, I don’t know. That would probably be my, my first inclination. If I was, if I only had a performance problem once a week for 10 minutes, I would probably just monitor the server and I would, I might even.
Be astounded to find that I have other performance problems that I didn’t know about maybe outside of that once a week, maybe outside of that 10 minutes. So you could, you could always get always download and use this tool for free. The link to do so is down in the video description.
Anyway, back to our Excel file. When does dynamic SQL become worse than static SQL from a performance perspective? And, uh, in, in general, as long as, uh, the conversion from static to dynamic is a one-to-one, I would say like almost never, but of course, like, you know, if you’re, if you’re going from, uh, like static SQL, where you’re passing in literal values to like parameterize dynamic SQL, you could always hit a parameter sensitivity issue, but that’s not dynamic SQL’s fault.
That’s, that’s parameters is fault. Um, so I think this one, I would. I would say almost never, um, I, I can’t think of, I can’t think of anything off the top of my head that, uh, that there would be good for, that there would be a good answer for this.
I, I can’t really just, I’m, I’m, I am stumped on that one. All right. Uh, why does indexing foreign keys help delete so dramatically?
Well, man, it’s like when you have foreign keys, like this, like, like actual foreign keys, not just like ones that you imagined in your head. Right. Not just like, ah, that’s a.
Foreign key to that table. I’m, I’m not gonna tell SQL Server about that. I know that, but I’m not gonna, you know, add anything to do that. Um, you know, of course, in order to validate those foreign keys or even to cascade actions from one foreign key to another, uh, SQL Server has to join those tables together. And, uh, for the same reason that indexes would, would, would potentially make joins more efficient, they might potentially make foreign key deletes more efficient because they have to figure.
If the, the SQL Server has to ensure the referential integrity of that foreign key is maintained, then a delete, uh, an index would help that delete quite a bit. All right. So that is our five questions.
Uh, this is a monitoring tool. I hammer DB running. That’s why that thing is so big. Uh, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video. Uh, we’ll talk about something that I. Haven’t figured out yet.
I mean, I have some ideas, but I’m, I’m not quite sure what we’re gonna do with them yet. Anyway, 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.