ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I discuss how to improve query performance by addressing plan warnings and residual IO on index scans. I explain that such warnings often indicate the need for better-indexed columns or additional indexes to optimize data retrieval efficiently. The discussion then shifts to a lighthearted note about my current state—feeling unwell due to weather conditions, which are particularly bothersome as I age. Despite feeling under the weather, I engage with viewers by answering questions and sharing some personal music preferences, including “Procession,” “Your Silent Face,” “Age of Consent,” and “Bizarre Love Triangle” from New Order. The conversation also touches on Azure managed instances, expressing both enthusiasm for their potential and acknowledging current limitations, while emphasizing the ongoing improvements Microsoft is making in this space.
Full Transcript
I feel like crap today. No one shows up. I’ll be relieved. Oh, someone showed up. Now I have to stick around. Darn it. Darn it all. Two people showed up. Oh, God. Now I have to stay for twice as long. That’s how it works, right? You guys are exponential. Oh, all right.
You’re very quiet people, though. Don’t even say hello. It’s rude. Rudy, Rudy Poo. There we go. Finally. Finally. Four people. My goodness. I didn’t realize that many people were so thirsty for SQL Server. Who knows?
I’m a little bit more. I’ll take that back a step here. I have no idea how much of this content will be SQL Server related compared to other things. No idea. We’ll have to wait and find out. We’ll see later.
Lars asks, do your AG listeners use NTLM or Kerberosoth? Good news and bad news. The good news is I don’t have any AG listeners. I mean, good news for me. That’s great news for me because if I had AG listeners, I would be doing things I hate. The bad news for you. The bad news is that means I don’t know. And, you know, I’m going to go out on a weird limb here. I don’t think AG listeners use either one. I think they just handle where the request gets sent and the type of authentication is handled by SQL Server when it arrives there. I don’t think the listener does any of that. Listeners are very, they really don’t do a lot. Listeners kind of don’t do much at all.
Listeners are kind of like, I don’t know. Weird, dumb, lazy things. SPNs won’t create for listeners. I’ve never tried to create an SPN for a listener. As far as I know, they don’t, yeah, they don’t need them. Darren, Darren, thank you for, thank you for chiming in, Darren. Yes, listeners do not need, require, get SPNs. They’re quite virtual things. I forget what, I forget the conversation that I was having about listeners before my friend, Mr. Sean Gilardi from Microsoft. I forget, it was, like, someone had asked a question on Stack Exchange, like, like, how do I fail a listener over or something? And it was just like, you don’t, they’re not that real. They’re not, like, physical things. They are very virtual.
Seems like they should use Kerberos. Yes, again, as far as I know, they just direct traffic and then the authentication is handled by SQL Server, not the listener at all. I don’t like that. I think that they just, they do nothing except say, you go here, you go here, you go here, you go here. Or everyone goes here because we don’t, we don’t want to pay $7,000 a core for over there. We just want to pay over here and pay software assurance. Host names. Host names, yeah, host names indeed.
Oh, man. People wonder why I just stick with performance tuning. All this other stuff is hard. Hard. Too much for me. Stick with query optimization. The optimizers are much more simple. Dependable and reliable than all this HADR stuff.
People are crazy with it. Yeah, perf tuning is really easy. You just throw a no-lock hint on everything and stick stuff in a temp table and you’re done. That’s it. Add an index once in a while.
Look busy. Twiddle your thumbs. Yep. Waiting for that index to build. Let me back in a bit. Oy, oy, oy.
So Lee asks, what kind of approach would you take to getting rid of table spools? I know indexes can help alleviate these, but what if that doesn’t help? So spools generally happen on the inner side of nested loops because SQL Server is terrified of doing vicious repetitive work. Especially if you have non-sargable predicates that are like the result of, you know, like is null or date at or date diff or something.
You know, join or where clause. That can certainly contribute to it. So like you see a lot of like L trim, R trim, replace in your joins and where clauses and they end up on the inner side of nested loops.
There’s a pretty good chance that SQL Server is going to use a table spool. So two things that is a repetitive work. So obviously nested loops, right? So something’s going to happen a whole bunch of times and SQL Server is afraid that the work is going to be repetitive. So what you’ll often see is the outer side of nested loops.
Maybe sort data before the nested loops join. And what happens next is funny. So SQL Server will take the data from the outer side of nested loops. Maybe sort it to put it in order.
If it’s already in order, then it doesn’t bother. It just goes into the nested loops join. On the inner side of nested loops, you’ll have that table spool. And what that table spool does, if it’s eager, is SQL Server will sort the data from over here. Or sort it if it’s not already sorted.
So that repetitive values from the outer side will be in order, obviously, right? Order is important. So what happens is SQL Server takes them. And so that it knows if the value one comes out over here and the next 10 values are one, it can take that spool, go run the subtree, get all the subtree for the value one, bring it into the spool, and then use it 10 more times.
So when you go get that first initial value, that’s a rebind. And when you go reuse the spool for that initial value, it’s a rewind. So rebind on one, rewind 10 more times for one, and use the data in the spool.
The next value is 2, and let’s say that happens 5 more times. You hit this, you go get the data for 2, bring it into the spool, and then reuse it 5 more times. So a rebind and then 5 rewinds.
If you really, really want, one of the best ways to get rid of table spools like that is to tell SQL Server that the data coming from the outer side is unique. So you can do that with either a select distinct, you can do that by dumping something into a temp table and putting some sort of unique clustered index or primary key on it.
Sometimes you can simply select distinct values into a temp table and then use that instead. And that’s generally the best way to get rid of the table spool is to separate the repetitive work, or remove the repetitive work, rather.
So when SQL Server knows that it’s only going to see 1.1 and 1.2 and 1.3 and 1.4 and 1.5 and so on, then it stops. Then it stops trying to do the spool.
Yeah, unique. All right, so if you have a set of numbers that are, let’s say you have 1 to 10, and that’s you have 10 1s and 10 2s and 10 3s and 10 4s and 10 5s, that’s not unique. But if you select distinct, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, you have one value.
And you can go get just that thing for 1, and then we’ll get the thing for 2, and SQL Server won’t. Does select distinct into a set of uniqueness flag? Well, when the statistics get built on that, then SQL Server will probably figure out pretty quickly that things are unique.
If you do that and you still don’t get rid of the spool, then you probably will have to add a primary key or unique clustered index on that temp table. I generally tell people to avoid nonclustered indexes on temp tables because if they’re not wide enough for the query, then they’re not going to get used, and then you’re maintaining the heap structure of the temp table plus the nonclustered index structure on the temp table.
So if you’re going to index a temp table, please make it a clustered index. Otherwise, you’re just maintaining two temporary objects, which I think that’s pretty dumb.
I would never do that. But yeah, heaps get statistics too. Heaps are not opposed to statistics. The only thing that doesn’t is the old table variable. I don’t think you can add those to temp tables, Lars.
I don’t think you can add constraints like that to temp tables. I want to say tempDB as opposed to foreign key and uniqueness constraints like that. You can add a primary key because that’s an index on the table, and that’s sort of a different beast, but I think unique constraints and the foreign keys and stuff like that are off-limits for temporary objects.
I remember, or at least remember a few times trying to do demos for foreign key stuff and using temp tables. Yeah, you can’t foreign key in tempDB.
It’s just not a thing. Not a thing you can do. I remember one time a while back, I was trying to write a demo using foreign keys. By the way, Peter, very funny. YouTube asked me to approve that comment, apparently because it thought it was dirty words.
You were trying to tell me to FK myself. So I remember trying to do some demos in tempDB about foreign keys, and I remember getting a bunch of errors like, you can’t do that here, or I could set it up, but it wouldn’t work.
And I was like, what the hell is going on? I was like, oh yeah, tempDB. It’s always tempDB. Always, always, always. Always, always, always.
Damn you, tempDB. I would love to go foreign key myself and then take a nap. I feel like crap today. It’s hot, and there’s thunderstorms coming, and since I’m old, weather has a disparate impact on my sinuses.
And so whenever things get high pressure outside, they get high pressure in my head, and I spend the day wondering if I need to go to the hospital. I’m like, is my brain about to explode? What’s happening? I don’t feel like I’m here right now. My hands don’t work right.
So things like air travel and bad weather are a lot of fun when you get old. Lee, you can ask all the questions you want because I have approximately 17 minutes to kill, and no one else is asking them, so please ask away.
Ask away. Question is, the plan warning operation caused residual IO on the index scan. Would you say that this is an area I should focus on with a plan when trying to improve query performance? In my example, the actual rows read is far higher than the amount of rows returned.
So sure. I think you’re going to see that warning in Century One Plan Explorer. I don’t think that’s in Management Studio. And when the reads are far higher like that, I’m actually writing a blog post sort of related to this. It generally tells me that the index keys are maybe not in the right order for that, or we don’t have a good index to find that data.
So if, you know, just as a quick example, let’s say that we needed to find like where reputation equals two, but either we don’t have an index on reputation, or we have an index on like another column and then reputation, SQL Server can’t easily seek to just the values we need for that.
So that might be a case where the, what do you call it? The index columns are in the wrong order. So if you put reputation first, you could easily just find that. That starts to matter much, much more on larger tables because you would, you know, if let’s say you have a 500 row table and, you know, you have to read 500 rows to find 200 values.
So what? If you have, you know, like a 10 million row table and you have to read 10 million rows to find five values, that’s much less fun for everybody. So check your indexes, make sure that you have a good index to find that data, find the data for your predicates or joins or whatever that you’re looking for.
And then if you don’t, I don’t know, go, go create one with no lock middle of the day, maxed up 57, something like that. That’s usually how it works, right?
It’s usually how it works. Let’s see. Uh, the next question we have is a good question. Wow. What are your top four new order songs?
Um, let’s see, probably procession, I think is my first one. Procession is first. Uh, your silent face would be second. Right?
So procession, your silent face, uh, probably age of consent. And then bizarre love triangle. I think those are my four. I almost wish that, um, I almost wish that 24 hour party people was a new order song because that would have snuck into the list.
If it was just not happy Monday. Let’s see. Does the Mac behind me boot? Yes.
Yes. It does. It boots up. I can use it. I even have a bunch of dumb discs and games for it. It’s just, it’s just very slow to use. This is the ravages of time. Just try this. Uh, let’s see.
Let’s catch up on some comments and questions here. Um, uh, let’s see. Lee says in the example, the actual rose, right? Oh, we already covered that. Uh, SQL WB says, Eric, if you’ve done any dabbling in Azure managed instances, I’m still on the fence since they have so many restrictions as compared to standard Azure setups. Yeah.
So, um, I really love where they’re going with them. They’re sort of like brand, brand new. Um, you know, they, they just like pretty recently got general availability release. And so, you know, I, I really like where they’re headed with them. Like, uh, the, the PM for those Jovan was on Twitter asking about, uh, DTC and how people use it and how if they implemented it up there, like how they would want to use it and what they would want to see from it.
So, you know, it’s, there’s lots of, they’re making progress with getting things like more on par with the box product, right? They’re slowly adding in things. This is no small undertaking to get this going. So like the cool part about Azure managed instances is that you get Microsoft, I mean, for better or worse, right?
We don’t know, cause it’s the cloud, right? Which is like Airbnb for your, for your data, right? You’re, you, you, you paid for it, you paid for the space, but it’s still someone else’s apartment. And you have to like wonder if like, you know, I don’t know, just herpes on the toothpaste or something. But anyway, so like, uh, I love where they’re going with it.
I love that they’re coming out. So Microsoft manages your backups. Microsoft manages, uh, the high availability. It has the same restriction where like once data goes in, data, getting data out is not quite as good. It’s on that like weird internal version of SQL Server that gets used in Azure. So, you know, um, they’re, they’re, they’re getting better with it.
And I like, uh, I like what I like. I like the idea behind them. And I think that that’s what Azure SQL DB should have started out as, but you know, better late than never. And I’m sure that, um, you know, I’m sure that is they mature and become a more competent product that, you know, the, the issues that perhaps Lee is running into and the roadblocks that you’re, you’re finding for, uh, you know, getting your workload up there and running will diminish.
So, you know, give it time, you know, it’s like anything early adoption, early adopters always have the most pain, but they also get the best prices because, you know, let’s, let’s face it. You were like, I was with you when you were terrible. Like you owe me money.
It’s like my wife. Like I was with you when you were broke. Buy me a Louis Vuitton bag. FK to self. Also the initial setup time is, uh, a little painful for me.
Genses is right now. For some reason, the first time you create one, it takes like a week. I don’t like, I think they have an actual person like, like, like wiring things in and like going to do stuff. They have to like, I don’t know. I, I picture some like, some like weird Kung Fu master, like prove you’re a Kung Fu master thing where someone has to like, sit in the Lotus position where they get beat with sticks and not feel anything.
And then like ascend a mountain and pick a flower and then like go underwater for 30 minutes. And so like, there’s like someone must have to go through some real, real misery to get those things set up. And yeah, the amount of time it takes.
Someone’s, I think someone’s like, someone goes out and buys a computer. They’re going on Dell.com. They’re like, Oh, we got to figure this out. Like, what do they want? 512 gigs of Ram. Oh, it’s expensive. Charge them extra.
Yeah, it is. It is. It, it, it’s a, it’s a, it’s a union estimate is what it is, man. The trades are slacking in that data center. It’s like, we will show up.
We’ll probably be there. We’ll be there within this four hour window. And if for some reason we won’t be in the, there in that four hour window, we will call you at hour five and tell you that we might be four hours late. Yeah.
I think if you work in any data center, your, your title is order monkey, because you are trying to bring order to that chaos. I read someone on Twitter was saying that, uh, their data center went down because a drunk driver hit like a, an electric pole by there and knocked out electricity.
And the battery power did not last as long as anticipated. The battery power lasted for like two, two to four hours or something. And then it just by everything shut down. Yeah.
The other tough thing about, uh, the cloud and managed instances, um, is that, well, so like you have, if you’re on prem, right, if you are living on, on planet earth, like the Duran Duran song, and you have multiple environments, like you have a production environment that you do all your real work on, you pay, pay for that.
And then you have, you know, dev or UA, UAT or QC or whatever lower environments, uh, you can use, you know, developer edition for those and you can use whatever you want for those. And it doesn’t really cost you much aside from like the hardware. Up in the cloud, there’s no real classification for like, this is just a development server.
If you need to have a development server, that’s in any way lined up with the specs of prod, you could end up spending a pretty serious chunk of change. And that’s, that’s kind of a big, big stopper for a lot of people who are like, wait a minute, but where are we supposed to test this stuff?
Microsoft’s like, then just speeds off in the solid gold Bugatti. It’s a, it’s tough. It’s expensive. It’s like, Oh, you wanted to rent another Airbnb.
Okay. That’ll be twice the price. Sorry about that. Should’ve read the fine print. Oh, he says one final point. SSMS is still not up to speed with managed instances. Although the past month has been better.
Let me tell you about SSMS 18. I wish that I could do it. I’ll, you know, I’m going to, when I get done with this, I’m going to record a video because when I use SSMS 18, there are two things that constantly make it crash. They irk the hell out of me because it’s, they both have to do with extended events.
And it’s not because I use extended events a lot. It’s that when I do use them, I just want to be able to use them and get the hell. They’re not, they’re not fun to use. So if you open up the extended events GUI and you say, I want to have a new session.
And then you, let’s say enter a name for the session in that first window. And then you click on events, which is the next node down in that little left side piece over there. It just crashes. It goes unresponsive and crashes. Goodbye. That’s it. Done.
Done. The other thing is if you like say use management studio 17.9 and you create a session there and you get it running and then you say an SSMS 18.1 watch live data, you can do that, right?
And live data starts streaming in. But when you close that watch live data window, SSMS crashes. It just says goodbye. And so it, it, it, it makes me wonder. It really makes me wonder, I truly wonder if anyone tests this stuff. I get the feeling that they don’t because those, those are two pretty huge usability things.
And if you like, it doesn’t take much poking around to discover those. It takes nothing. Nothing. Ridiculous. Let’s see. Azure data studio.
Oof. I haven’t even downloaded it. I don’t, I don’t care. I’m going to be completely honest. I don’t care because all of the things that I do revolve around query plans and query plans in Azure data studio are garbage. They just, they, they, they don’t have anything anywhere near the level of detail that the ones in management studio do.
And I am not excited at all about the plan explorer add in because what the plan explorer add in doesn’t give you is a lot of the operator time stuff. And it doesn’t give you, I really, I, the way that you can, the way that plan explorer handles parallelism and showing parallel threads and row counts where it’s like in some weird tab four bars over and it’s like row by row.
And like, there’s like a column. It’s not, not easy to look at some things in plan explorer. There are some things it does quite well.
Um, but, uh, like, like if I had to, if I have to troubleshoot a long store procedure, uh, plan explorer is the first thing I’m going to go to. But with the operator time stuff and the, the parallelism stuff, most of, most of what I, what I’m doing these days is in SQL Server management studio 18, which, which, you know, it makes it frustrating because you have these awesome advances in one area, but you have these, you know, just terrible crashes in other areas.
So it’s like, well, why did I didn’t, does anyone over there use extended events? As I wonder, I wonder, I wonder, I wonder. Azure data studio was great for worksheets. I don’t even know what the desert worksheet is. Nope.
I like it. Oh, it’s too much. It’s not, it’s not my thing. Like, I’m glad it works for people and it solves problems for people, but it’s not, not my jam. Azure SQL serverless. Serverless is a lie.
It’s like saying this can is canless. It’s on a server. Good for demos. All my demos revolve around query plans. What am I going to show people? HTML, paste the plan.
There’s no, there’s no, there’s no, all the detail on that is not there. Not with the stuff that I want to show people. It does not have that in there. No, no, no. I would, I need, I need the good query plans. You put the good query plans in Azure data studio. We can talk.
Then we’ll talk. Then I’ll think about it. Well, it’s just, you know, different tools make sense for different people. You know, some people are very like, Oh, you got to use visual studio code for everything.
Oh, have you tried notepad plus plus? I’m like, doesn’t do what I need it to do. Different functionality. So I was just like, man, I really want to show you this execution plans. Like, have you tried power shell for what? It’s not what I need.
I want to show you this query plan. Well, have you tried DBA tools? I’m like, man, it’s not what I need. I need this stuff. Yeah.
That’s the other thing is I don’t want to have to learn a whole new thing right now. One of my favorite tools other than SSMS. I mean, again, plan explorer for, uh, for big store procedures where I, where like management studio is just an utter failure for displaying those.
Uh, but other than that, I don’t, I don’t use a whole lot. I’m pretty low fi. My stuff, you know, I, um, keep a lot of stuff in text files. Uh, you know, I, I w I was a big MS paint guy until I, um, I started using snag it for my screen caps and they have a pretty decent photo editor in there.
So, some people, some people, he just says, my wife is very judgy when she catches me in MS paint.
Well, you know, what, what does she use? but she has a Mac with $7,000 Photoshop product on it. It’s messed up. MS paint is awesome. Remember, good old days of MS paint.
She does have them. Of course she does. You know how I knew that? Photoshop may be involved. Yeah. You know how I knew that? Hmm. Very intuitive, very intuitive person. Know everything.
Know everything. Evernote. Evernote is good. Yeah. I use it. I, I use Evernote. I probably don’t like fully utilize it in the way that I should. Like it’s, it’s Evernote is kind of like a junk drawer for me. The only thing that I, uh, I consistently use it for is like workout stuff.
So I can just like put stuff in there real quickly, update stuff, make notes. But, um, yeah, aside from that, I’m, it is, it is pretty much just a drunk drawer of like, I like this picture. Sure. I would like to do something with this picture. Eventually.
Hmm. I haven’t used OneNote though. I know Buck Woody had a tweet or a blog post about how he uses OneNote for all sorts of crazy things. And I realized that my life is not nearly as complicated as his. And that I, I don’t need that level of functionality.
I just don’t. So, wake up, stave off the hangover. Eat some breakfast, work, continue working, go to the gym, come home, continue working, start drinking, eventually stop working because this is too much drinking.
Go to bed. Paints a fun picture of life as a, as a consultant, doesn’t it? I’m, I’m mostly, mostly joking. That’s most of my, most of my days and nights are not that, that difficult. I felt.
very, very rarely is there a weekend, weekday hangover rather. It’s not, not big on the, the week, the, the weekday drinking because, I don’t know. I, I feel like if I’m going to go to the gym, it should at least hang around for a while.
Right. At least give it a fair chance. And if I drink during the week, I don’t give it a fair chance. So don’t do that. Really special occasions. Once in a while, weekend warrior of sorts. Terrible word.
That is. All right. We are about, we’re a little over the half hour mark and that is all I am contractually obligated to deal with you people for. So I’m going to get going and open, open my door and let the air conditioning back in. Thanks for hanging out.
Thanks for all the great questions this week. And I will, I’m not sure about next week yet. We’ll have to wait and see. I’m doing a bit of traveling. So we’ll, we’ll figure out if I have the bandwidth to do this. Also might be at a slightly different time. It might be, it might be at a time that makes Europeans very happy.
We’ll, we’ll see. All right. Take care. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.