SQL Server Performance Office Hours Episode 20

Hello Mr. Erik, I’ve attended your tuning course inside a theater in Seattle years ago! If I may ask a question about the future of SQL. I heard in the past that Joe Sack left MS to MongoDB and now he has returned. That genius guy still working with improvements of sql whatever “onprem” or azure ?

If you don’t mind, could you tell us more about the stories behind your tattoos? What do they represent, and how many do you have?

Without parameterized queries, how would you suggest to decide which queries to tune?

Hi Erik! Are your educator skills just natural talent or do you have any good sources for improving that?

Give me the case against partitioned views.

Video Summary

In this video, I dive into the world of Office Hours with Darling Data, where we tackle a variety of SQL Server questions from viewers. We start off by addressing the future of SQL and Joe Sack’s recent career moves, which sparked some interesting discussions. Then, I share insights on deciding which queries to tune without parameterized queries, introducing a new feature in SP Quickie Store that helps analyze query hash totals. The conversation continues with personal questions about my tattoos and teaching skills, offering unique perspectives on how these aspects of life have influenced my work. Finally, we wrap up by discussing partition views, sharing both the benefits and challenges they present. It’s always great to engage directly with our community and hear from you all!

Full Transcript

Erik Darling here with Darling Data. And we are once again greeted with the background. So we are once again doing Office Hours! Kaboom! If you would like to ask your own questions for Office Hours, this is the link to do it. It’s down in the video description. Likewise, if you would like to support my channel and give me money to keep talking, if there were a way for you to pay me to stop talking, perhaps there would be more generosity from the greater public. I am I am open to that. I can be bought. I’m not beyond that. My morals and my ethics do not extend that far. So if you would like to pay me to keep talking, you can do that. If you would like to pay me to stop talking, shoot me an email. We can work something out. If you like this channel but not in a money way, you can like, you can comment, you can subscribe. And if you would like to hire a consultant to do SQL Server stuff because you’re having trouble with SQL Server stuff, guess what? This total package here does SQL Server stuff. Health checks, performance analysis, hands-on tuning, dealing with SQL Server performance emergencies, and of course, training your developers so that you have fewer SQL Server performance emergencies. Right down to zero SQL Server performance emergencies. We can, I can do all those things. Not we, I. There’s only me here. You only get this face. There’s no substitute face that shows up. Doesn’t know who you are or what you are.

If you would like to get my performance tuning training material, you can get all 24 hours of it for about 150 USD for life. Again, link video description. My new T-SQL course, which I finally fixed this slide for. Videos will start dropping in June. You, of course, get the pre-sale price until the advanced material shows up after the summer. This is companion material to the pre-cons that Kendra Little and I are doing in Seattle this November. So if you are attending those, you get access to this material at the price of the pre-cons. If you go to pass and you don’t come to the pre-cons, I don’t care. Right? Like, you have to show up for me and Kendra for me to care. If you would, again, speak more about the live and in-person stuff. Pass on tour. Boy, this is going to be fun. New York, Dallas, and Amsterdam. August, September, October. I will be at all three of them. And of course, I will be at Pass Data Community Summit in Seattle this November. doing the aforementioned pre-cons. So we will have a grand time with that, won’t we? But with that out of the way, let’s do these office hours questions. Let’s zoom, zoom, zoom, zoom, zoom, zoom, zoom, zoom. What do we have here?

Hello, Mr. Eric. Hello, you. I’m not sure how to address you. I’ve attended your tuning course inside a theater in Seattle years ago. A theater, you say. If I may ask a question about the future of SQL, as much as I am not a psychic, I’ll do my best. I heard in the past that Joe Sack left MS to MongoDB and now he has returned. That genius guy is still working with improvements of SQL Server, whatever, on-prem or Azure. So he did come back.

Joe Sack did go to MongoDB for about a year or so. And then he was back at Microsoft. And he was back at Microsoft for about a year and a half. And, you know, it would be inappropriate of me to comment on Joe’s situation at Microsoft. But Joe was sort of unhappy with what his role had morphed into. And so Joe went to work at another database company called Elasticsearch.

So Joe is now some sort of head honcho, not sure what a head honcho is, over at Elasticsearch. Doing a great job there, kicking butt. They are very, very lucky to have him. You know, I miss him dearly working on SQL Server, but it was not meant to be.

All right. Ooh, a personal question. Look at us go here. If you don’t mind, could you tell us more about the stories behind your tattoos? What do they represent and how many do you have?

Well, the stock answer that I have when someone asks me how many tattoos I have is all of them. Because I’m pretty well covered. But the thing where I depart on having stories behind my tattoos is that I got nothing.

All right. Like really, most of them mean absolutely nothing to me. There’s no story. There’s no meaning. There’s no like heartfelt life event that led to me getting them. Like I got a couple like wife and kid name tattoos, but those are just sort of like if I didn’t get them, like they’d be mad.

Right. That’s about it. It’s just, you know, I learned I rather I figured out at a very young age that I was the type of gentleman who wanted the who wanted attention from the type of lady who had a lot of tattoos. And I realized that the best way to get that attention was to get tattoos. And I was lucky enough to make friends with some tattoo artists, like especially like, you know, friends of mine, like lifelong friends of mine who are like starting out getting tattoos, who have gone on to like be really good at tattoos and like own tattoo shops.

But I have all their like starter work. So I have a lot of really old tattoos right now that they were just like, hey, I want to do this tattoo to practice. Can I do it on you? And I was like, dope. I’ll buy burritos.

So most of these tattoos have no real meaning, no real story, maybe just a thing that I kind of liked at the time where the tattoo artist was like, oh, I want to do this Japanese thing today. I’m like, I don’t have any Japanese stuff. Let’s do it. So like, you know, I just got covered with a lot of stuff that means nothing to me very quickly.

And guess what? It worked. May not be the most thoughtful thing in the world, but it was highly effective and painful, painful and effective. So anyway, well, we’re on the subject of pain.

Without parameterized queries, how would you suggest to decide which queries to tune? So this is actually a neat question because I recently added, well, I don’t know, I’m actually, because of how busy I’ve been recently, I have no concept of like actual time. Months have gone by where I’m like, where are we?

But I sort of recently added a new parameter to SP Quickie Store to help you decide if queries are worth tuning. And that parameter is called include query hash totals. There are underscores in there because I like putting underscores in things.

To make them readable. I don’t like the uppercase, lowercase thing. It makes me feel cramped and crowded and it gets me all claustrophobic feeling. But so I added this parameter because like, like what I would find is like I would run Quickie Store.

And like there would be this whole like list of queries that looked similar, but would have like one or like just a couple, maybe like five, six executions. And people would be like, this doesn’t run that much. I don’t want to spend time on it.

So I put this include query hash totals parameter in. And what that does is it looks at the query hash. So like if you have unparameterized queries that are effectively running the same thing over and over and over again. Right.

It’s the same query. You just have like different dates or like a different name or a different number of in clause things. All the stuff that like kind of like gets you like the same query hash, but like with different like with what gets you like the same query plan. No, it’s not the same query plan.

Gets you different query plans, but like the same query hash. Like the text of the query ends up all the same. Like it gets, it counts all that up and it gives you totals for like CPU and duration and like executions and all that stuff. All like the other metrics that are in the in QuickieStore’s output for the query at the query hash level.

So like you might find a very sneaky query that looks like it’s only executed once, but the query hash tells you otherwise. If you look at the query hash, it’s like, wow, this thing actually executed like 7,000 times. We just only see this one, like we just see this one example of it in the output.

So like when I, when queries aren’t parameterized, now that’s the tool that, or rather that’s the option that I use in my tooling when I want to figure out if something is worth going after. It’s still worth using QuickieStore and it’s still worth figuring out like, like if the query is meaningful to the workload, like other ways you can do that with QuickieStore. There’s a parameter in there called workdays.

And I like the workdays parameter because like it’ll just look at stuff that’s run Monday through Friday, but then by default nine to five. But there are two other parameters you can use to change the span of hours. But what’s nice about that is that like you, you screen out automatically all the like overnight processes that you might not care about.

If you like, if you want to focus on the overnight processes, go ahead and say, set the start and end date or whatever, or like, like use workdays, but go in reverse. Like with the, with the times, with the timestamps. But like, so I would probably use some combination of those things to do that.

Okay. Hey, look, another sort of personal question here. Look at that. Are your, hi, Eric.

Hi, you. Nice to meet you. Are your educator skills just natural talent? Oh boy. I don’t know if I call it that. Or do you have any good sources for improving that? Uh, so I have no good source for improving that. Um, any, any, any ability I have as an educator comes from being dumb.

Right. I’m not, I am not a naturally smart person. Things don’t come quickly to me. I’m like one of those like old CD burners that goes at like one X, like it, like it, it burns slow, but it burns deep.

Right. So I don’t learn things very quickly. And it like, so like when I need to learn something, like I need to like really break it down in my head a lot further than people who get things a lot more intuitively. Like people who are much smarter or more clever, whatever it is.

Like, like they, they like, it’s like, oh, like they just look at something like, oh yeah, I get it. Me. I’m like, no, no, no. I need like, no. Why does this thing go from here to there? Like I don’t get things that quickly.

So I’m good at teaching people because I’m dumb. Right. Like it takes a lot for me to learn something. And by the time I’ve learned, I’ve learned something, I feel like I’ve learned it very well and in very small pieces so that like when I have to tell someone else about it, like all those small pieces are just like burned into my, my CD brain. Right.

My CD brain. So I like, if it’s, if it’s any, if there’s anything natural, it’s cause I’m spinning slow up here. All right.

Give me the case against partition views. I don’t really have one. I don’t really have one. My only, the only real rot that I’ve found with partition views is trying to get things right so that they’re updatable. That’s a damn nightmare.

That is not a good time. I don’t suggest that. It doesn’t fit a lot of situations. So if like you want parts, if you want to use partition views, I say, go for it. Just, you know, create your, make sure that you get your constraints right and make sure that whatever needs to like, you know, like whatever view needs to be refreshed to get new data in there is running at the right like intervals and stuff.

And you’re pretty good. You know, I like partition views like, like usually quite a bit better than like capital P partitioning, just because like I can index stuff differently. If there’s like, you know, if I add new columns or remove columns, it’s easy enough to like fix that in the view definition.

When you have like proper constraints on the tables to tell a SQL Server what data lives where, you know, you can get like pretty like clean execution plans from it. So I really don’t have much against partition views aside from like trying to get them to be writable, which again, that kept me up for, that kept me up for a while trying to, trying to like get a good demo where like to where they were writable. And I, I bailed on it.

It was just, it was too much. It was too many things, too many things went wrong and happened that I, I did not like nor love. All right. Anyway, I believe that is, that is five questions.

One, two, three, four. Yeah, that’s five. I can, I can count to five. Most, I, I, I, I, I credit most of my ability to count to five from, from barbell training because doing sets of five really does get you good at counting to five. All right.

So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video, video, video. So thanks for submitting questions. Submit some more.

All right. That’s not a very good sales pitch, is it? 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.