SQL Server Performance Office Hours Episode 27

What are stupid things to monitor in SQL Server?

INTERVIEW QUESTION: what’s your sql weakness?

If you were a sandwich, what kind of sandwich would you be? Would you eat yourself?

I am not an expert and have a hard time telling when what someone is saying is BS. What are some signs?

Is there a way to tell if rows are locked before trying to modify them?

Video Summary

In this video, I had a fantastic Office Hours session with the Darling Data community! We dove into some fascinating topics, including what to monitor in SQL Server and which metrics are actually useful for troubleshooting. I shared my insights on avoiding meaningless stats like page life expectancy and context switches, advocating instead for more actionable metrics such as wait stats and query store data. We also tackled a few fun interview questions—my weakness is clearly not math, but rather the complex algorithms that others can craft so effortlessly! The sandwich question was a highlight, with roast beef and Swiss on rye being my go-to choice. Lastly, we discussed how to spot misleading information in technical content, particularly when dealing with large language models. Overall, it was an engaging session full of great questions and lively discussions—thanks for tuning in!

Full Transcript

Erik Darling here with Darling Data, and I have a very, very exciting Office Hours episode for you, I’ll tell you what. It’s going to be twice as exciting as the last one. I have no substantive facts to back that up. I just have a feeling in my bones. My bones feel excited. But anyway, if you would like to ask your own questions for these Office Hours episodes, there’s a link right there. It’s down in the video description. You click on it, you ask the question. It’s wonderful. Everyone gets a chance. If you like this channel content and you want to sign up for a membership, that is also down in the video description. It’s a grand thing to do. For as few as $4 a month, you can make me happy. If you need SQL Server consulting help, health checks, performance analysis, hands-on tuning, dealing with performance emergencies, and of course, training your developers so they stop giving you heart attacks and other problems, then I am the man for this. The job. And of course, as always, my rates are reasonable. My performance tuning training, again, that link, that discount code there, the everything bundle becomes yours for life for about $150 US. And of course, the thing that I have been working so very, very hard on to the point where I barely sleep anymore is my, I’m kidding, I sleep very well, is my new T-SQL course. All 23 hours of the beginner content is out available. You can go grab it, watch it, to your heart’s content. And of course, it is available at the pre-sale price of $250. And that will double in value to $500 when everything is done.

Red Gate. Sweethearts at Red Gate taking me all over the world this summer. New York City, August 18th to 19th. Dallas, September 15th to 16th. And Utrecht, the Netherlands, October 1st and 2nd. This is all part of the Pass on Tour series of events leading up to Pass Data Community Summit, not Comumity Summit. It’s a funny thing that happened once upon a time. And that is in Seattle, November 17th to 21st. But with that out of the way, let’s do the office hours voodoo that we do so well. We got some real brain acres today here. This is going to be a purse swinger. Let’s see. What are stupid things to monitor in SQL Server?

Anything that you would look at and think, what do I fix? Page life expectancy? Stupid. Beat it. Anything that’s a queue length, like bin it. Context switches. What are you going to do? Stick to stuff that is, I guess, things that when you see them, you’re like, oh, I can tie that to something. Wait stats are great. You know, like having like query store on is great. Look at query store. It will tell like you can feel like I have a very good store procedure called SP Quickie Store that you can look in there and see your worst queries and query store. It’s wonderful. Block process and deadlock reports. Wonderful things to look at so that you can monitor what’s actually going wrong on your server and not just have this wall of numbers and wonder, oh, okay. Well, what caused the context switch?

Why did PLE drop? Who cares? Like look at things that give you something to fix and work on. Like, like, like if you want to like gather a bunch of numbers and just be like, hey, I guess I made them go down. Cool. Right. That’s, that’s something you can do. Otherwise, you need something tangible to go work on. Right. You need a, like, you need something to do. Right. Fill your day with joy.

All right. Interview question. What’s your SQL weakness? Oh God. Math. I am not a math person. Uh, I, I read, uh, articles that other people write where they do like all sorts of crazy math algorithm calculation stuff in their queries. And I’m like, I don’t know. How do you know how to do that? Did you go to school? What? These, these numbers are foreign to me. There’s, there’s like a, like Greek letters and stuff. And I’m like, oh, well, smarty pants over here. I just make the queries go faster. You write the math. I’ll make them, I’ll make it fast. That’s, that’s my SQL weakness. All right. Oh, this is, this is the, this is the best question that’s ever been asked in office hours. If you were a sandwich, what kind of sandwich would you be? Would you eat yourself? Oh, any day of the week. Uh, so I mean, my, my favorite sandwich in the world is of course, uh, the, the roast beef and Swiss on rye. Uh, you gotta have the deli mustard on there.

No weird mustard, uh, prefer a Dijon of some variety and, uh, we are good to go. And boy, I tell you, I take a bite of that right now. All right. Uh, oh boy, this is, this could be a long one. I am not an expert and have a hard time telling when someone is saying BS. I assume we’re talking about, about SQL Server. Uh, what are some signs? Um, I don’t know. I mean, obviously if, if you detect, uh, through your advanced knowledge of what LLM output looks like, uh, that would be the first sign.

Um, you know, uh, like anything that is, you know, a bunch of emojis, um, you know, like random percentages, like, and numbers, like, uh, like I made my CPU 38% happier last week. Like, Oh, okay. Yeah. Great. Um, but like, if you’re, I don’t know, like if you’re reading a blog, like, I don’t know, stuff that sticks out to me is like, especially when someone is like, start, like starts off with like a problem statement. And it tells you they’re going to solve this. Like I have this query that runs slow, uh, and we’re going to make it faster in this blog post.

And then like the blog post drags on and like, like you can tell the query is not getting any faster, but like they start talking about other metrics and like start like just like, like referring to things like, like, oh, this query runs for 1.3 seconds. So now we’re going to do this thing. And, and look, we reduced logical reads and look, the query cost is lower. And you’re like, the query didn’t get any faster. What happened? Hey, where are you going? And like, stuff like that. Um, but you know, I honestly, like most of the BS comes from LLMs these days.

So really just if, if someone like, if like something like, like emoji filled nonsense shows up, just skip over it, right? Just leave it alone. It’s not, it’s, it’s like commodity information that you can get like anywhere. Like you could go ask an LLM for this thing and it would give it to you. Like this, this person did nothing to provide it to you.

Uh, so like, I don’t know that that’s, that’s, that’s kind of what I’m on the lookout for these days. Um, you know, like I suppose it’s different if someone is like presenting something and saying stuff out loud. Cause you know, I’ve, I’ve certainly sat in presentations where like the person presenting was completely wrong about something.

But like, I mean, I don’t know, like, like, like either someone else wrote the presentation and they’re just reading it or like, like whatever, like wherever they got this thing from was, was wrong in the first place. Like their source of information was bad and you’re just sitting there, but like, they are very, but like it’s on the slide and they are very confident that it’s true. So, uh, that, that situation is a lot harder, uh, you know, cause you walk away from that, like, you know, this person was presenting live at a conference.

Like how they had to know what they were talking about, but there’s not a lot of like the conference tech review of like the materials beforehand. A lot of that’s just like, yeah, go like you really, you’ll talk for free. Come on, do something.

Uh, so, you know, that, that situation is harder, but, um, you know, just in general, you know, like pay it, like kind of like pay attention, like try to follow along. If they jump around a lot and there’s a lot of like, but look over here now. Oh, look at this.

Look, that Santa’s way too fat for that chimney. Uh, that’s when you can kind of get a sense that like, you know, they’re, they are misleading you in some way. Uh, oh, whoa, boy. All right.

Technical question here. Uh, if, is there a way to tell you? If rows are locked before trying to modify them. So, uh, you could, of course, like, you know, interrogate various DMVs and look for, uh, unfriendly locks. But at like the, at the row level, that would be tremendously difficult, uh, if not impossible.

And, um, you know, like it would come at some expense trying to like interrogate these views all the time. Uh, probably what I would do is just set a lock timeout. And like, like throw a retry loop around the code.

Like you don’t want your code sitting there. Like, you know, like if exists like this. Oh, wait, hang on a second. Like doing that.

Uh, or like while exists, wait for something. Um, like you, like sending, sending the lock timeout, you know, set it for like, I don’t know, like a second or two. And if you can’t get a lock in that time, like go into some sort of retry thing.

Uh, cause like the lock timeout sends, sends back a very specific error number. So you can use the error number function to say, oh, like if like the lock timeout, like expired, I hit this error number. It’s like 12 something.

Uh, and then you get that error. And if you like, that’s the error that you got from it, it wasn’t like a primary key violation or some other failure, then you would just like, like, oh, cool. It’s, it’s like my lock, my lock expired, my lock timeout expired.

Like I’ll wait for like, you know, a second, maybe some exponential back off math in there and then retry it. So I wouldn’t, I wouldn’t try to check if rows are locked before, um, before trying to modify a query. I would, I would just, you know, I would just go for it and try it.

And then set like with the lock timeout, let your query expire and go do something. Anyway, uh, that is five questions. Count them two, three, four, five, six.

Uh, I think we’re good here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and please keep these great questions coming. Uh, I particularly enjoy ones about sandwiches. So more sandwich questions are better.

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.