Last Week’s Almost Definitely Not Office Hours: January 18

In this video, I dive into some complex topics related to SQL Server performance tuning and parameter sniffing. Starting off, I address the nuances of parameter sniffing by discussing scenarios where a stored procedure might generate either a big plan or a small one, and how keeping the big plan can serve as a temporary fix. However, I emphasize that this approach needs careful testing to ensure it doesn’t cause issues in highly concurrent environments. Moving on, we explore resource governor and its limitations, suggesting it should be renamed “query thought throttler” due to its tendency to negatively impact performance. The conversation then shifts towards the challenges of intelligent query processing, particularly parameter sniffing, and how improvements could lead to more adaptive plans based on varying parameters passed into stored procedures.

All right, so, uh, look at this. Ready? Yeah, yeah.

Do, do, do, do, do, do, do. Mm-hmm. Mm-hmm.

Mm-hmm. Mm-hmm. Mm-hmm.

Mm-hmm. Mm-hmm. Hello, hello, hello.

Hello, hello, hello. Hello, maybe. Weed.

Weed. It’s working, I think, kind of. It turns out scheduling these things is a lot harder. Or, like, starting up these things when they’re scheduled is a lot harder than just starting one up.

I have to do weird, odd, uncomfortable things to get this started up when I schedule them. So I think from now on, I’m just going to last minute go live. I’m just going to give whatever.

There are four of you in here, huh? We got a link to this one because the other one’s dead. That’s fun.

Happy Friday. Friday. Dancing on Twitter.

Did I promise dancing on Twitter? I forget. Now I’m too hungover to dance. I might yak on you if I start dancing. Sorry, Peter.

I apologize. So I have spent my morning messing with, oh, I had to set up an AWS account and stuff. And I had to upload the, oh, private dancing.

Yes. Well, there are six people in here, so it wouldn’t be very private. So I had to spin up an AWS account and set up an S3 bucket with permissions and all sorts of other goofy stuff this morning on top of trying to rattle off some blog posts and whatever before the weekend.

It’s going to be fun. It’s going to be a fun time. Of course I set up I am.

I followed Amazon’s best practices in that regard because leaving the root user is the only user seemed like a terrible idea. I did have a, actually it was kind of funny because I had to create a public bucket. And for a while it just kept saying like access denied every time I tried to make my public bucket public.

And the more I say public bucket, the more I regret saying public bucket. But yeah, it kept saying access denied. And it turned out that I had to like allow the making of, I had to allow making public buckets public, which is a weird thing to have to do first.

Like it should say that like this, like this bucket does not allow, is not allowed to go public. It’s goofy. Yeah, I have, I have a bunch of I am users now to confuse hackers.

So like they’ll get in, they’ll think they can do something, but they really can’t. It’s like dozens of, dozens of I am users that will lead them nowhere. Yeah, it was weird.

Like, cause I want to be able to share slides and stuff at the bits pre con and other training stuff that I’m doing. So I needed to have like a bucket that everyone could just freely download from, but not do anything else from. So, uh, there it is.

I don’t want to use Dropbox for that because, oh, I don’t know. I suppose there’s a reason. I don’t really know what it is.

Now that I got this fancy business Dropbox account, I don’t know. I don’t know. I just don’t know anymore. Man, I don’t know what other fun things have happened since last week.

I got business insurance. I got contracts from lawyers. I made, I had, I sent out my first invoice.

I don’t know. It’s been an okay week. It wasn’t a very big invoice. I probably haven’t made that little money in a week since. I don’t know.

Uh, I would say, I’d say when I, when I should have been in college. And I don’t know. It’s been an okay week. Uh, I feel like you got a lot of the, a lot of stuff figured out in here that was, that was kind of up in the air and unsure last, last Friday.

All right. There are five of you, but no one’s asking questions. So this might be quick. Like, I’ll give it another couple minutes, but then, then I have to go back to doing, well, I guess being unemployed.

Doing, doing my work. This woman’s work. It’s a good Kate Bush song. I wish that this thing would show me the list of users so that I could, like, why is there no list of users?

There we go. Oh, it’s just me and one of the, who are the other, who are the other people in here then? That’s ridiculous.

Yes. Josh is here. Thank you, Josh.

I guess, I guess, I guess by participant, it means people who have actively said something and not people who, um, I don’t know. I actually don’t even know. I don’t know.

I don’t understand this thing anymore. I seem to have, seem to have lost all touch with reality. If no dancing, what about whiskey? Uh, I have to go to the gym later.

Stupid. So, uh, I don’t think I’m going to start drinking whiskey this early. Uh, Peter asks, your post on fragment, or I guess says, your post on fragmentation was timely for frustrating reasons.

Trying to make the best of it, but would you say it’s worth my time to dig into fragmentation intervals? Uh, what do you mean fragmentation intervals? Like how often you run defrags or how often things become fragmented?

Or what exactly is a fragmentation interval? Not sure. Yeah. Not sure what that means. Internals.

Oh, no. Uh, I wouldn’t, I wouldn’t touch that. I don’t think it’s, I don’t think it’s really worth the effort. Um, you know, I, I remember, uh, every time I opened an internals book, I think the first, like, three or four chapters were just, like, like, uh, like a filler waste of time on, like, how things are stored.

And it’s good stuff to, like, know once or, like, you know, know, like, a little bit and then just kind of tuck away. But I don’t think it’s ever helped me solve much of a, um, much of a performance problem on a server.

It’s always one of those things where it’s just like, yes, it’s very neat to know that there are 8K pages and they get stored on extents. They get stored in data files and data files have these games and S games and PFS pages and all this other stuff.

But it’s not, I never, like, looked at that and went, oh, well, that’s why this query is slow. Because, um, this just never happened. It’s always been some other, like, you know, uh, well, sometimes basic reason.

Other times it’s more fun. I think, um, Adam Mechanic has one of my absolute favorite talks and it’s called, like, like, it’s like the five things that cause 95% of your query performance problems.

So it’s on YouTube too. You can find it if you look for Adam Mechanic videos on here. But, um, it, I think the, the real wisdom in there is that it usually is, uh, just one of, like, you know, one of a few really basic things that, uh, often causes the majority of performance issues and once you get outside of those things, that’s when, like, the really interesting stuff comes in.

Like, I’m gonna, I’m gonna write a blog post today, I think, about how when you, uh, it’s like when you write a query that will find data. Sometimes it’s a lot faster than a query that won’t find data.

Farah says, what monitoring tool do you wish existed but doesn’t? I don’t know. I guess mine. Uh, so the kind of interesting thing about, uh, not working for Brent anymore is, uh, you know, a lot of the work that I put into the first responder kit is now kind of, uh, I mean, I mean, it’s not off limits, but it’s, uh, you know, a bit awkward to go back to at this point.

So, uh, I’m, you know, gonna have to do some of my own stuff around that. Uh, as far as stuff that I wish, like, stuff that I, you know, stuff that I wish monitoring tools did that I don’t think exists very well is, uh, correlating, uh, or, like, capturing relevant details of, you know, um, catastrophic events.

And I don’t mean catastrophic, like, the server crashed or the server, like, failed over or something. I mean, like, you hit thread pool or, like, resource semaphore or, like, you know, just CPU pegged up to 100%.

I wish that when that stuff happened, there would be, like, this emergency mode for monitoring tools where they were just, like, like, like, like, okay, like, this crazy thing is happening. We need to focus on, like, these few things instead of just running the same dumb queries and be like, oh, 10TBs might be funny.

You know, like, stop looking at, you know, disk activity and just be like, no, we need to focus on this. Like, we need to, like, really zoom in on, uh, what’s happening there instead of, like, running the, like, like, like goofball template queries that kind of keep doing.

I’m flipping around, looking at stuff. Chris is on Twitter saying he’s watching. Why don’t you ask a question, Chris?

That would be really helpful. I don’t have anything to talk about. Oh, boy. Josh says, I have a parameter sniffing question from the Master Inquiry Tuning class that you maybe worked on.

Wow. Wow. Okay. How, you can ask me a very general parameter sniffing question, and maybe I’ll be able to answer that. Because if I have a stored proc that either gets a big plan, meaning parallel big memory grant, or a small plan, should I try to keep the big plan to avoid the small plan spilling?

Um, I think that that’s a pretty good band-aid. Like, that’s a pretty good temporary fix. Because a lot of the time when the big plan runs, unless the big plan runs very concurrently, and those additional resources become a point of contention, either parallelism, either via parallelism you hit thread pool weights, or via the memory grants you wanted to restore a semaphore, then generally, yes, that’s a pretty good band-aid until you can dig deeper into maybe getting a good plan for all variations of the query.

But that can be really tough to do, especially when, uh, you know, stored procedures, you know, especially, like, you know, when you’re learning about parameters sniffing, a lot of the examples are just like a single parameter that gets passed in.

And, like, that single parameter can cause enough variation in a plan. But then you get to the real world, you get back to your job, and there’s, like, you know, 5, 10, 15, 20 different parameters that can get passed in.

And some of them can be null, and some of them can cause real problems. So, uh, you know, if you need to, like, quick fix parameter sniffing stuff, then sometimes, yeah, like, getting and keeping the big plan can, you know, be worthwhile.

But sometimes, um, you know, you really, you know, it’s, it’s, it can cause problems downstream if, if it’s a highly concurrent thing. So, like, what I would do is I would test, if I get that big plan, how many of it can I run at once, right?

So, like, you know, use, like, SQL query stress or O stress or something and just run the big plan and, like, like, add threads to it until you either, like, run out of worker threads and hit thread pool or you run out of memory available and hit restore semaphore.

So, I’d probably like to do one of those two things and then be like, all right, well, like, this is, like, the limit for how many of this query can run. And then think about, like, as far as your workload goes, like, do you have a bunch of other queries that might be running alongside it?

How many copies of this query can run? How many, like, other, these other queries might come along? You know, there’s a, there’s a, everyone, when they think about concurrency, they just think about kind of standard, like, locking and blocking on the server, right?

Where, uh, you know. They’re like, well, I can’t run, you know, like, three of these updates at once because they’ll block each other.

Or I can’t run this update and these other select queries because they’ll block each other, right? And that’s when people fall into no lock or, you know, uh, maybe the slightly smarter decision to use an optimistic isolation level.

But, uh, you know, like, I think the, the next step in learning about concurrency is really, like, um, you know, understanding that queries interact with each other and the hardware in very particular ways.

And that it’s very easy to, um, uh, you know, become very, like, I would say, non-concurrent, uh, when enough, uh, when enough queries run. Peter says, could you get fancy with resource governor?

So resource governor has some really interesting stuff, especially, I think, for memory grants. But it, it shouldn’t be called resource governor. It should be called query thought throttler because all it really does is, uh, you know, like limit the resources that a query can use.

And often that has some rather bad side effects on performance. Yeah. Parameter sifting is hard. Um, and it’s one of those things where, uh, out of all of, I think the intelligent query processing stuff that Microsoft has been working on, I really wish that parameter sniffing were, you know, coming to the forefront.

Uh, because I think that is like, as far as like, you know, if you want to think about intelligent query processing, uh, you know, it’s nice that you can switch join types and it’s nice that you get memory grant feedback between executions.

And it’s nice that, you know, some stuff has happened with functions and table variables coming up in 2019, but man, parameter sniffing is still, I think like the biggest problem. And if you wanted to really have intelligent query processing, being able to, um, you know, store, I think branch iterations of the plan, like make some different plan choices along the way for things.

If, uh, you know, depending on the parameters that get passed in, like, I don’t think you should have multiple copies of a plan, but I think you should have multiple iterations of a plan and that, uh, you know, you could, you could do more to say, well, you know, instead of just reusing one plan, we’ll reuse like one of three or five plans depending on what parameters get passed in.

Uh, so I think, I think that might be like a good next step for the intelligent query processing crew would be to do something like that. You know, uh, it is just, you know, something that like every time, well, back, back when, back when I had clients, back when I had clients, that was always a big problem for them.

Uh, what day is your SQL bits pre-con? Uh, boy, that’s a good question. Um, let me check.

My SQL bits pre-con is, oh, it’s, you know what, it’s on the SQL bits website. I’m an idiot. I don’t know why I didn’t just look there.

I think it’s, it’s, it’s the second day. Sessions, training days. Uh, yeah, mine is on Thursday. Total server performance tuning. So Thursday, February 29th.

The training days are Wednesday and Thursday. So, uh, mine, mine’ll be Thursday. And if you’re going on Wednesday, you should definitely go check out Aaron’s session because, uh, Aaron, I think has a really cool one.

And, uh, if, if, if you, if it turns out that mine is sold out, uh, I think Penal’s would be a good, good choice to go to as well. Okay.

So that’s fun, fun, fun, fun, right? Good stuff. Uh, let’s see here. Uh, Farrah says, what were the most common query performance problems you ran across as a consultant? Were they mostly sniffing?

Uh, so I, I would say a lot of them would, a lot of them were parameter sniffing. Uh, other ones that were quite common were, were around indexing where it was either a general lack of indexing or a general, like abundance of indexing.

Like, none of the indexes, like someone added a lot of indexes a long time ago and just never revisited them. And it’s like code changed, queries changed, you know, uh, the way the app worked changed. No one ever revisited the indexes.

And we’re like, Oh, cool. Like we could, we could get rid of these five indexes that are somehow on the same single column over and over again. And we could like, you know, maybe add some other indexes. It’ll help the queries that we’re running today.

All right. So that, those are, those are very common. Um, stuff with functions was always very common. Uh, people selecting a lot of, or a lot more data. Actually, there were a number of really funny incidents where, uh, you would see, be like, people would be like, Oh, you know, we have this, you know, this query, these queries that run and they populate like, like an Excel file or a dashboard or something.

And they would just be like select star with nowhere clause. And you’d just be like, well, you know, you can limit the data that goes there because they would get it all into the application. And then people would do it like with the data that landed in the application.

That was very fun. Uh, Mr. Sick asks, did you get to keep that home, home server? I built. Yes, I did get to keep that. Uh, Brent was very nice about the things that I was allowed to keep, uh, in the layoffs, including, um, you know, a lot of the, the training material that I had worked on there.

I get to, um, you know, do, do my own thing with, because I mean, obviously I don’t, I mean, I don’t think Brent, I would imagine Brent wants to write his own material. Most of it, or he could, you know, he could, he could write, uh, things in a different way than I do. So I got to take the stuff that I worked on.

Um, and I will be using that for various stuff. Uh, I don’t know if it’s, I don’t know if it’s totally NDA. I don’t think it’s NDA anymore, but, uh, my, I put in an application to, uh, do videos with Pluralsight. And it was accepted and I’m just working on getting, you know, hooked up with someone over there.

And I’m going to be producing a couple courses for them, uh, at least, uh, fairly immediately. And then, you know, talk about some additional courses that I might do for them. Because it’s, you know, uh, I think, I think Pluralsight’s a pretty cool, uh, platform for that.

But, and, you know, as much as I would love to say, hey, like, I could, you know, like set up a, you know, whole video training thing the way, the way Brent has, um, I, that’s like a little bit beyond the scope of what I can do, uh, do, do quickly. So, you know, I’m going to say I’m going to do the Pluralsight thing for, uh, the foreseeable future. And then if, um, you know, that goes well, or if, you know, the, the consulting and training thing, uh, takes off in an appreciable way, then I’ll try to put in some plumbing to do, uh, videos through my own site.

Uh, oh, thank you, Mr. Sick. I’m excited to, uh, you know, I mean, I’m not excited to be unemployed, but I am excited to be able to do, be free to kind of, you know, uh, you know, shape my own stuff the way, the way I want to, uh, you know, that is kind of, that is a nice freedom to have. But we’ll, we’ll see how it goes.

I mean, I’m not, I’m not, I don’t think I’m going to become a suit and tie man anytime soon, but, uh, you know. So, uh, stuff like that is stuff like that is hard to forecast, uh, especially in January when there is not a whole heck of a lot of work. I’ve, I’ve been like, sort of like, just like, you know, talking to some other people who do independent consulting and I’m like, so how’s your January going?

And they’re like, we thumb twiddling. It’s like nothing doing. I don’t feel too bad.

I don’t feel like I’ve messed anything up yet, but, uh, I’m just waiting for, waiting for, uh, waiting for January to thaw out a little bit. So I, you know, maybe get some more action over on my end. It would be, it would be nice to, uh, have a positive income stream by February.

That would be a little, maybe a lofty goal, but it’s one that I’m going to go with. I don’t know. Who else?

Anyone else have anything? Anyone? Anyone? Anyone? Anyone? We could, you could have a, I have a brief discussion on what I should name the, the, the scripts that I work on. Obviously Blitz is taken.

So, uh, we’ll just call it SP Eric and maybe it’ll crash your server and maybe it won’t. Maybe it’ll run DBCC write page. Maybe, uh, I don’t know.

Maybe it’ll just change weird settings. I don’t know yet. We’ll see. I have, I’ve, uh, I remember at the DBA days that we wrote a chaos sloth or I wrote a chaos sloth script and it was pretty fun. And then it would go through and change weird settings.

It would change like max stop and cost threshold and max memory and some other stuff. And, uh, I always thought, man, it’d be fun to have one that did more stuff like, like, like change, like, like bigger settings or like restart SQL or something like that.

Or like, you know, just do crazy stuff. Like, you know, use like XP command shell or, uh, something else to like, you know, turn off the network or whatever. Yeah.

Random trace flags, but you’ll, you’ll have to send me your list of random trace flags. If you want me to do that for us, because you’ve, you’ve got a better list than I do. I have like, I have like a, uh, like a markdown file with a few found helpful over the years, but I don’t, I’m not. I’m not good at keeping track of all of the trace flags.

So you’ll have to send me your list of ones that would be especially nefarious to turn on and off. Tech news says, hello from India. Hello, tech news.

Uh, I am not in India, though. I wish I was because the food is wonderful. I think that’s about my favorite food in the world. So you’re thinking, thinking about other stuff that that script could do.

I don’t know. It’s like drop indexes. Where am I from? I am from America. I think sometimes. I’ve been to Canada a few times.

It’s pretty nice there. Kevin says, when query tuning, it’s easy to force a query to use a particular index, uh, using hints. But sometimes I want to see what the index optimizer would choose if the index it has chosen wasn’t there.

But I don’t want to disable the index because it could be large and take too long to rebuild. I wish there was a hint that allowed it to specify an index not to use. Oh, interesting.

So, uh, good news and bad news. When you specify indexes, when you specify indexes, you can actually give a list of indexes that you want it to use. Uh, I wrote the post over on Brent’s site a while back.

Um, let me see if I can find it quickly. With one of the bad idea, bad idea jeans posts. And as long as Brent’s sites up, you can do this.

There we go. Look at that. First try. It’s amazing the way that works. I’m going to stick that into chat. Um, so you can actually, you can specify multiple indexes for a query to use. So if you know, like, the names or the IDs of all the indexes that, you know, you’re okay with the optimizer having a choice of, and, you know, which ones you don’t want it to look at, is that you can specify all the ones that you say, like, are interested in seeing more from, seeing it choose from, and then not, like, not specify the ones that you don’t want to see.

So that’s one way to do it. I know it’s not the greatest thing in the world. Like, it would be much easier to say, no, just don’t use this one, rather than maybe you can use all of these.

But it could be worse. You could have absolutely no options there. That might be. I mean, the other thing is, well, I guess on a dev server, it still might be just as annoying. I don’t have a better idea on that, aside from the fact that you can specify all of the possible indexes that might be a yes, rather than specifying one that might be a no.

You know, I try to know a thing or two. I think that’s the whole point of consulting, is to know a thing or two. So sometimes, you know, like, actually, there are times when people, like, say something that’s so wrong, it makes me question whether I know what I’m talking about or not.

And that’s always a frightening moment, because in my head, I’m like, no, no, that’s wrong. And I know it’s wrong. And I know the right answer.

But, man, you are so convinced about this wrong thing that I’m trying to, like, doubt my own sanity. I’m like, I don’t know what to do right now. I’m, like, trying to, like, you know, like, low-key Google stuff and be like, this is right.

There’s no way this can be right. There’s no way. And, yeah, it’s always weird. It’s like brief losses of confidence when you’re like, wow, this person is so spectacularly wrong. They are making me question, like, things that I’ve known for a decade or things that, you know, I’ve, like, tested myself and looked at.

I’m like, what on earth? How could you do that? All right.

Any other questions? You funny, peanutty people? There are significantly fewer people here this week. There were 22 last week. But, you know, maybe that was, like, beginner’s luck. Now I’m down to eight people.

You will only do SQL types? Yeah, that’s all I really know about. I don’t know about C Sharp or anything else. So, unfortunately, I wish I knew more stuff.

I’m terrible at PowerShell. I don’t know. What else? What else is there? I mean, I can talk about hardware a little bit. I like hardware.

Things like that. Windabug. Yeah. Except you’re better at windbag than I am, Forrest. So, I don’t know why you’d want me to talk about it.

You have cracked some nuts in there that I haven’t been able to. What am I doing? Good question.

What am I doing? I think I’m actually going to go get lunch. Because there are very sparse questions. So, thank you for joining me. It was a fun-filled half hour of trying to figure out how to get this thing to work. I will most likely be back next week with another one.

We’ll see. Maybe. Maybe not. Anyway. Thank you. And see you next time. Bye. Bye.

Thank you.