ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I dive into some of the exciting new features coming in SQL Server 2019, particularly focusing on batch mode memory grant feedback and adaptive joins. These features are only available if you’re using compatibility level 150, which presents a unique set of considerations for database administrators. I also delve into key lookups, explaining their role in execution plans and how to identify potentially expensive ones using the SQL Server Blitz cache procedures. The discussion then shifts to practical advice on running SSIS and Reporting Services (SSRS) services alongside the SQL Server engine, emphasizing the importance of keeping production environments as clean and efficient as possible. Throughout the video, I share some personal insights, including my ongoing journey towards achieving a long-held goal and reflecting on recent changes in my professional landscape, such as the exciting news about Zane Brunette joining Microsoft. The conversation also touches on the nature of working alone versus being part of a team, with a humorous nod to my gym routine and its impact on my current state of mind—hot, hangry, and ready for some serious database tuning challenges!
Full Transcript
It is so hot. It’s so hot and I’m so annoyed with all the heat. I finally got to eat lunch. Prior to that, I was both hot and hangry at the same time. My hair is sweaty, I’m sorry. I look like garbage. I look like garbage. It’s not fun. It’s not fun. I should probably mention that I’m doing this somewhere. I don’t think I did that. The one person who is here. Thank you for showing up. Thank you for having faith in me. One person who is here. Hopefully you’re not just some Russian bot.
Here to collude. Here to collude with me about things. Oh, and you’re gone. Goodbye, Russian bot. Goodbye forever. Do-do-do-do. There, I’ve tweeted about it now, so no one has an excuse for not showing up.
So, if no one shows up on this beautiful Friday, it’s a hot Friday, then I’m going to talk about something that’s been on my mind lately. And that something is the coming… Off to a good start. I’m not going to say the word coming correctly. The coming reckoning. That’s where a coming came from, was coming reckoning.
The coming reckoning that is due with SQL Server 2019. So, there were things introduced in SQL Server 2017 that I thought were really cool. And that was batch mode memory grant feedback and adaptive joins.
Of course, both of those things were only available if you had columnstore indexes somewhere in near or around the query. So, there were ways to get it legitimately if you had columns run. And then there were some hacky ways to get it regardless.
And those things are being introduced now in SQL Server 2019. And another cool thing that’s coming along in SQL Server 2019 that’s brand new to that product is called Freud. It’s the scalar valued function inlining that our buddy Karthik has been working on brilliantly and doing a brilliant job of.
So, there’s those two things coming. And they are only available if you are in compatibility level 150. Which of course means that if you flip to compatibility level 150, then you are also using that new cardinality estimator.
And that has worked out not so well for a lot of people who I’ve seen start trying to use it. Now, it’s not all the cardinality estimators. Well, they didn’t do testing really.
So, they should have tested before flipping that switch over. But there’s been a lot of consulting calls where someone has said, I’ve upgraded to SQL Server 2014 or 16 or 17. And performance is kind of tanked.
And, you know, when you hop on the phone with them, you start looking at stuff and you say, Oh, well, did you change the compatibility level? And I said, Yeah, of course. We always use the newest one. I said, Well, I have good news for you. With the flip of this one switch, I can restore performance to where it used to be.
Which might not be a good thing or a great thing, but at least you will have the problems that you’re used to on your server. You will not have these brand new problems. So, that’s nice.
But those two features being tied into Compat Level 150 give you a weird choice. It’s the choice between solving potentially a couple big problems with SQL Server. And then also introducing potentially a couple or many more problems by using the new cardinality estimate.
You have to do a lot of very thoughtful, very careful planning to get around that stuff. And what’s crazy is that this is going to be the version, I think, where people have to finally start making those choices. Because not everyone is available to make the kind of changes they need to to get performance right from other perspectives.
So, getting the batch mode stuff and the scalar UDF inlining stuff in is going to be pretty huge. Anyway, that’s it for me. So, Darren says, thanks for the stickers.
Yes, you’re welcome. My pleasure. If you, to anyone who sends me their address, I have Twitter DMs open. If you send me your address, I will send you stickers. I’m happy to do that. Postage costs almost nothing. And I got a very good deal on stickers.
Sticker Mule is a great website for stickers and they often have sales and other things. So, that’s no problem. Darren says, I haven’t looked at CTP3 yet. Did they make any significant changes or have you? I have looked. I did some tweets about new stuff that I found in there.
There was nothing that like blew my mind. There is a weird new use hint. And I’m going to go check on exactly what it’s called so that I don’t get, so I don’t misquote anything here. Excuse me, one of those days I can’t spell select right.
From valid. Is this not valid use hints? And it is called disable loop join avoidance. So, there is a new use like option use hint thing called disable loop join avoidance.
And I’m not exactly sure what it does yet. I have some guesses. I have theorized a couple things. But perhaps it is to make stuff like key lookups more common.
So, there are a lot of queries where you would expect a key lookup to occur and it doesn’t. You get a clustered index scan instead. So, it could be useful for those.
I haven’t put any of these theories to use yet. Mostly because I like having these theories. I dislike having them disproven. Because then I was like, I don’t know what the hell to do. It could also be for columnstore too. Because I know columnstore queries are very apprehensive about doing any kind of key lookups. So, I think it could be for that too.
I don’t know though. I just don’t know yet. I just don’t know yet. We will have to wait and see, won’t we? We will have to eventually wait for Paul White to break out the debugger and figure it out for all of us. Because that is usually what happens, isn’t it?
There is something new or there is something weird going on. And is there anything more comforting or is there anything more reassuring than the words, well, Paul White says. Because you just know that it’s going to be correct. You know that there is a sufficient level of research and care taken in the words that he chooses to share with the rest of us.
That you’re not going to be led astray. So, if there is anything, well, Paul White says. Well, Paul White wrote.
I heard from Paul White. I love it. I love it. It’s a great source. Do we have any other questions? There are eight of you here at least. Are there any other questions?
Nine of you. Wow. Totally watching while at work. Lee, you devil. You dog. Zane says, you may occasionally get your mind broken by Paul White. Yes, that happens quite frequently. It’s a strange thing. What happens when he starts talking.
It’s crazy. It’s like you think that he’s just really good at SQL Server, but he’s good at like many, many things that surround SQL Server as well. And it’s like the stuff that he can also do. So, I’m going to like relate this to like, there are some people who can get good at SQL Server, but it will occupy the entirety of their mind.
Like me, like this is all I can do at once. I can’t have any other hobbies. I can’t have friends.
I can barely walk. And then there are people who like, like SQL Server is like swinging like, like below their weight. It’s like T-ball to them. They can do SQL Server and other stuff. And that’s impressive.
Paul White is one of the people. He can do SQL. He can know a ton about SQL Server and other stuff. It has not pushed all other knowledge out of his brain. And that’s an impressive thing. I have to dedicate everything in this melon. It’s abused melon to SQL Server or else I will not. I will not know any of it.
I will have absolutely nothing. So, it’s always. It’s always fun to watch people who can do this and other things and be good at them. So, yeah, that’s fun stuff, too. All right.
There are a lot of you here. Holy cow. All you playing hooky. No one is at work today. What other questions do we have here? Dan says, are singleton lookups good or bad? I found some indexes that don’t have any seeks or scans in them, but they do have singleton lookups. Singleton lookups just mean that they’re used for key lookups.
They’re not really bad. They’re used for seeks. So, they don’t have any seeks or scans against them, but they do have singleton lookups. All right.
So, yeah. So, usually that just, usually that means that they’re the key, like a primary key or a clustered index or a non-clustered primary key. And they primarily get used for lookup values that are not present in nonclustered indexes. They’re not really a good or bad thing.
I would just peek through the plan cache and I would peek through the plan cache rather. And what I would do is, since you’re using the Blitz procs already, I would say that you should run SP Blitz cache and you should look for a warning called expensive key lookups, which will point to, it will point to if you have an execution plan where a key lookup operation is greater than like 50% of the plan cost, I think. And you could check those out.
So, there are two kinds of key lookups. There are output key lookups where you just go back to the clustered index or the base table if it’s a heap and you just grab columns to show people. So, those are like window dressing.
Those are like columns that you’re just selecting. And then there are predicate key lookups. And predicate key lookups are when you do the same thing except to evaluate a filter. So, if I had, let’s say, an index just on, let’s say I have a clustered index on column one and I have a nonclustered index on column two and my query is like select some column two where column two equals something and column one equals something. I might do a key lookup just to figure out that where clause on column one.
So, for me, fixing the predicate part of key lookups is usually much more important than fixing the output list part. So, unless I’m running into bad parameter sniffing, but working all that stuff out really turns into a very, very long conversation about what exactly is going on with the query. And it’s like, can we rewrite the query?
Do we really need all these columns? Like, is it any frameworks? Is it a sort of a ceiling? Like, there’s a lot of stuff that, like, you need to start working through when you see those things. So, what I would leave at is that there’s nothing inherently wrong with them, but you might want to take a peek through your execution plans and see if you have any expensive key lookups. Again, SV Blitzcache will warn you about that.
And that’s where I would go with it next. Maral says, thoughts on running SSIS and RS services on the same production box as the engine? Yeah, not a fan of that. They take up weird resources and do weird things. I always want to have a different, I always want to have those things on a different server. I’m not crazy about them being on the production box.
Those resources are precious, man. I’ve seen y’all’s production servers. They’re not beefy. They’re not big beefcake servers. And even if they are, you don’t want SSIS and SSRS gobbin’ up the works. It sucks from a licensing perspective, but from a keeping production safe perspective, it is much, much better.
How is my goal of smoking cigarettes in a French graveyard? I am slowly working client by client. I work towards that goal. Client by client.
We’ll see. We’ll see how close we can get. I just need to keep doing it. It would really help if I didn’t live someplace so damn expensive. Oh, man. Yeah.
Yeah. Me and Tara agree on a lot of stuff. That’s why we’re still buddies. At least I hope we are. I don’t know. She might hate me now that I’m consulting competition. She’s working at straight past SQL. And I don’t know. I don’t know.
Maybe we’re at wars. Start licking some shots. Dude, drive by or something. I’m kidding. No, actually, it’s been really nice. Mike Walsh has been great. He’s been offloading. When he has too much work, the poor guy, when he has too much work for his people, he’s been offloading stuff to me.
And so, you know, it’s really nice to have that sort of like, you know, if nothing else is going on, I can, you know, like, hey, Mike, what’d you got? And, you know, have, you know, a few days worth of work to kind of fill up the week, which is always a good thing. So no, no beef there.
No actual beef there. Tara is just very, very busy having to be a remote DBA now. And that’s the lazy consultant. So good for her. That landed in almost the same exact job. That’s a nice, nice touch. Zane posted a link.
Memory management with SSIS and SQL Server can be a large problem. You don’t want to drop pages out of memory because an SSIS package sucks. Yes. And from what I know, most SSIS packages suck. It’s one I’m aware of. Most SSIS packages suck.
Nature of the beast, I suppose. Nature of the beast. Yeah. Not Zane’s own. Zane’s SSIS packages are great. They’re wonderful. Unfortunately, Zane is probably not going to be writing too many SSIS packages anymore.
Mr. Zane Brunette has recently accepted a job as a PFE at Microsoft. He’s going to have the blue badge and the list of trace flags and access to source code and all sorts of other crazy cool things that I’m envious of except for the working at Microsoft part.
I’m very envious of many things that he has going for him except there was a way for me to get those things. And not go to jail, I would do all of those things. And I’m just too happy being by myself.
That’s the problem. I love being alone. It’s wonderful. Love. I love loneliness. Loneliness is great. A lot of people think loneliness is a dirty word. I love it. It’s the best thing in the world. I can do what I want. Wear what I want.
Not wear what I want. Sweatpants at the gym. No two to blame. Yeah. Yeah. Yeah. That’s true. That’s true. I do have to accept the blame for everything. But it’s okay because I’m the only one who notices. I’m very lenient on myself in that regard.
Very lenient. Oh. Yeah, that was fun. So yesterday I was at the gym and I was doing deadlifts because that’s what I usually do. And I was doing some, I was doing like singles, like 475. And I got, well, I was doing doubles at 475 and I got bored.
And I wanted to start doing singles at a higher weight. So I started doing singles at 525 and I videotaped one. And I think it went particularly well. It went like, it was just like, it went up like crazy. You can barely see what’s going on there.
But man, I was very happy with how quick it went up. It’s going, it’s going, it’s going. Ah, come on. Lift it dummy. Why are you so slow? Big fellow walking behind me.
Yeah, that went all up. That went all the way up. I was very happy with that lift. And then I did, I did a few more of those and I was dead. I still think I’m dead. That’s why I’m so hot and hangry today is because it’s tough to recover from doing that. It’s hard.
It’s hard. Your central nervous system is just like yelling at you. It’s like you are the worst. Why do you do that? I don’t know. I don’t know. Mostly just to impress myself. No one else is impressed.
Ah, man. All right. Come on. Give me a, give me a question here. People can’t not have any questions. Why else would you show up to Q and A? You didn’t have questions. You can’t just, you can’t just be here to watch me. I’m not that, I’m not that excited. I’m not that interested.
I’m not that interested. Come on. At least your forehead doesn’t explode like half the horn. Yeah, that’s true. It comes close though. If I, if I strain hard enough, I can get, so I can get, I get this like split vein thing right here.
It looks like, like the flux capacitor. It’s crazy. Uh, yeah. Rogue plates. Can’t do it. If I don’t have, I mean, what other plates do you buy? The only thing that sucks about the gym that I go to is they don’t have like the, like the, like real Ollie, like thin plates.
It’s all like the thick, the thick rubbery ones, not like the thin and ones. So like, if you have like six blue plates on the bar, that’s all you can do. Like, and then if you want to do like more, you have to like take off some of like the good rogue plates and put on like some of the, the metal octagon plates. And that’s, I mean, it’s, it’s not that they’re bad.
It’s just, you know, it breaks up. It breaks up how cool it looks. And when you’re OCD like me, and you just want to have a bar, same, like, damn it. Give me, do this. Let’s see the thoughts on diagnosing network connectivity issues other than trying to tell net via command prompt or using the deck.
So is it like you set up a SQL Server and you just can’t connect to it off the bat or like queries are timing out getting weird? Cause it’s like, you just set up a SQL Server. The first things I always do is check firewall rules.
If it’s a named instance, I make sure that the browser service is running. I’ve also learned to very, very, very carefully check the instance names. Because there, there was a, there was an, there was an instance around the time. Oh, I don’t know when CTP three came out when I spent 20 minutes trying to connect to the wrong server name.
So that was, that was great. Random users and apps can’t connect. The most common thing, most common reason that I’ve seen for random users and apps not being able to connect is thread pool.
Thread pool is when you run out of worker threads and you can no longer assign even like a session ID to a query. And that’s a pretty bad thing. So what I would do is if you wanted to run like SP blitz on your server, you might get a warning about thread pool weights.
If you don’t have that or can’t run that, then you can, you can run pretty much just any weight stat aggregating script and see if you have thread pool weights on there. Like lining up because that’s usually a sign that bad things are happening. Thread pool generally happens if like you have a bunch of parallel queries coming in and trying to run stuff at once.
Or if you have like a big blocking chain or something and you know, stuff like if you have a monitoring tool, you might see big blank spots in the monitoring tool too, because that thing can’t, that thing needs threads too. And it can’t connect. So there’s a lot of stuff, a lot of reasons why that might happen, but thread pool is among chief among them.
Stop naming your instances. Oh, yeah. Woo woo. Stop naming your instances. Weird things. I named my instance CTP and I thought that I had named it CTP two in the past.
And so I was trying to connect to CTP two and I couldn’t. And I guess kept trying to like, damn it, what’s going on? And I was like, oh yeah, it’s just CTP. No two. And I did that.
Everything worked and it was magical. I felt it was a big, big, big win for me there. It’s like amazing. Let’s, let’s sanity check. I never sanity check first. It’s always like the fourth or fifth thing I do. And then I feel dumb because I’m like, through all these years, why isn’t that the first thing that you do? Like, I’m not crazy.
I can’t be crazy. I know I did this right. Oh, yeah. Dan has a good point. I do have a script. Now you’re going to make me go find that. Let’s see if I can remember how to type.
There we go. I can even get to, I can even get to my own website. Paul Randall even said, this is a nice script.
If you check the comments, you check the comments. There’s a thing that says from Paul, comment from Paul Randall that says, nice script. And then I felt bad because like a week later, someone posted a question about wait stats on the DBA stack exchange site. And they were like, I ran this script and they paid they pasted the script in but it was like just the script was like just the queries from the script and they were like, I ran this script.
And it’s telling me that I have like 86% CX packet or something. And I was like 86% of what in my head. I’m like, damn it.
Why do you do? And so like I write this answer. I’m like, the reason why that script stinks is because it doesn’t tell you like what the uptime of the server is. You don’t know what 86% of, you know, like, like if you have like, you know, like three days of CX packet weights, but your server’s only been up for a half hour. That’s way different than if like you have three days of CX pack away.
So it serves enough for a month. And I didn’t like, cause you know, working for Brent for the last four years, I didn’t, I haven’t spent a lot of time looking at other people’s scripts. It was always very much like, you know, write scripts from scratch, like do stuff on your own. Don’t look at other people’s stuff.
You don’t want to like get, get caught like influencing or have anyone say you stole that script I posted on the blog. You suck. And so I was like, okay, no problem. I’ll write everything. So I don’t remember what, what Paula scripts looked like. And so like, like a few hours later on my answer, there was, there was like, there was a comment from Paul.
And that’s when, that’s when I started thinking, I was like, why would Paul randomly comment on this, this answer? And then I went and I looked and I was like, oh shit, that’s a script. It’s like, eh.
So I know he’s not watching, but sorry about that, Paul. Honest mistake. It wasn’t, it wasn’t really, I wasn’t trying to make fun of you. I was just trying to help someone. Anyway, I can’t remember if I’ve told that story on here before.
I remember telling someone that story before. I forget, I forget when it was. Anyway, could have been here. It could have not been. Don’t tell me if it was, cause I don’t want to get embarrassed. Don’t tell me. Don’t tell me anything. Don’t tell me when I forget things. There’s nothing more embarrassing than forgetting things.
Let’s see. Anything on Twitter. No. So I, on Twitter, I made a prediction that. If SQL Server 2019 does not increase the RAM cap. It really should increase the RAM cap to 256 gigs of RAM.
128 is nothing these days. Nothing. My desktop, this thing that I can kick. Willy nilly has 128 gigs of RAM. There’s no reason to expect. A.
Production SQL Server instance, even standard edition to be capped to 128 gigs of RAM. It also helps because there are very strange instance sizes up in the cloud. They’re very oddly sized with the CPU to RAM ratio. And I think.
There are, there are many situations. There are many servers that’s see that standard edition would make sense on if it could have 256. Gigs of memory, but don’t make sense. Um, because it only accepts 128. Like there are some 16 core instances up there. When, or like 16 to like 24 core instances up there that have.
Like 256. Or like 200 and some 66. Some like weird number gigs of memory, which is, which sounds great. And you’re like, man, standard edition would be cool to put on there, but. I would have.
A hundred or so gigs of RAM. Sitting around. No, I, I, I get that. The differentiation, but they’re like that number is the cap on the buffer pool and you can use stuff above the buffer pool. I just don’t see a lot of people who are like, I only need to cash. 228 gigs of memory of stuff in the buffer pool, but boy, howdy.
I sure wish I had a hundred gigs for memory grants and the plan cash and other stuff. I just, I don’t, I don’t meet those people terribly often. So I think that having standard edition go to 256 gigs of memory would be an excellent thing. But anyway, I was saying that my prediction was if it doesn’t have, if they don’t increase the RAM cap or if they don’t make the scale or UDF inlining or what do you call it?
Batch mode for rowstore available on standard edition, then I think adoption is going to suck. Going to continue to stifle adoption because the salespeople are jerks.
They really got to cut that crap out. Not helping anyone. Not doing yourself any favors. Not, you know, you’re not selling more enterprise licenses by doing that. You’re keeping more people on the old licenses as they already have.
People just refuse to upgrade. I know I knew people on SQL Server 2008 standard edition who would not upgrade to 2008 R2 because in 2008 there was no cap on memory. 2008 R2 is when standard edition got the 64 gig cap. So there are people who, clients who I knew stayed on 2008.
Probably still on 2008 because there was no cap on memory. They could put in as much as they wanted. No. I don’t know. That’s what I do.
But, you know, I’m free spirited. Might not fit in well with that corporate culture. Anyway, we’re about at the half hour mark. It’s very hot. It’s very hot.
Very hot in here. And I want to stop being hot. So I’m going to get these headphones off and step away and sit down and drink some water. Anyway, thanks for joining. I hope you at least had a good time. Anyway, I’ll catch you next week because as far as I know I’ll still be here.
It’s amazing. Friday after Friday. It’s nuts. Goodbye.
Video Summary
In this video, I dive into some of the exciting new features coming in SQL Server 2019, particularly focusing on batch mode memory grant feedback and adaptive joins. These features are only available if you’re using compatibility level 150, which presents a unique set of considerations for database administrators. I also delve into key lookups, explaining their role in execution plans and how to identify potentially expensive ones using the SQL Server Blitz cache procedures. The discussion then shifts to practical advice on running SSIS and Reporting Services (SSRS) services alongside the SQL Server engine, emphasizing the importance of keeping production environments as clean and efficient as possible. Throughout the video, I share some personal insights, including my ongoing journey towards achieving a long-held goal and reflecting on recent changes in my professional landscape, such as the exciting news about Zane Brunette joining Microsoft. The conversation also touches on the nature of working alone versus being part of a team, with a humorous nod to my gym routine and its impact on my current state of mind—hot, hangry, and ready for some serious database tuning challenges!
Full Transcript
It is so hot. It’s so hot and I’m so annoyed with all the heat. I finally got to eat lunch. Prior to that, I was both hot and hangry at the same time. My hair is sweaty, I’m sorry. I look like garbage. I look like garbage. It’s not fun. It’s not fun. I should probably mention that I’m doing this somewhere. I don’t think I did that. The one person who is here. Thank you for showing up. Thank you for having faith in me. One person who is here. Hopefully you’re not just some Russian bot.
Here to collude. Here to collude with me about things. Oh, and you’re gone. Goodbye, Russian bot. Goodbye forever. Do-do-do-do. There, I’ve tweeted about it now, so no one has an excuse for not showing up.
So, if no one shows up on this beautiful Friday, it’s a hot Friday, then I’m going to talk about something that’s been on my mind lately. And that something is the coming… Off to a good start. I’m not going to say the word coming correctly. The coming reckoning. That’s where a coming came from, was coming reckoning.
The coming reckoning that is due with SQL Server 2019. So, there were things introduced in SQL Server 2017 that I thought were really cool. And that was batch mode memory grant feedback and adaptive joins.
Of course, both of those things were only available if you had columnstore indexes somewhere in near or around the query. So, there were ways to get it legitimately if you had columns run. And then there were some hacky ways to get it regardless.
And those things are being introduced now in SQL Server 2019. And another cool thing that’s coming along in SQL Server 2019 that’s brand new to that product is called Freud. It’s the scalar valued function inlining that our buddy Karthik has been working on brilliantly and doing a brilliant job of.
So, there’s those two things coming. And they are only available if you are in compatibility level 150. Which of course means that if you flip to compatibility level 150, then you are also using that new cardinality estimator.
And that has worked out not so well for a lot of people who I’ve seen start trying to use it. Now, it’s not all the cardinality estimators. Well, they didn’t do testing really.
So, they should have tested before flipping that switch over. But there’s been a lot of consulting calls where someone has said, I’ve upgraded to SQL Server 2014 or 16 or 17.
And performance is kind of tanked. And, you know, when you hop on the phone with them, you start looking at stuff and you say, Oh, well, did you change the compatibility level?
And I said, Yeah, of course. We always use the newest one. I said, Well, I have good news for you. With the flip of this one switch, I can restore performance to where it used to be. Which might not be a good thing or a great thing, but at least you will have the problems that you’re used to on your server.
You will not have these brand new problems. So, that’s nice. But those two features being tied into Compat Level 150 give you a weird choice.
It’s the choice between solving potentially a couple big problems with SQL Server. And then also introducing potentially a couple or many more problems by using the new cardinality estimate. You have to do a lot of very thoughtful, very careful planning to get around that stuff.
And what’s crazy is that this is going to be the version, I think, where people have to finally start making those choices. Because not everyone is available to make the kind of changes they need to to get performance right from other perspectives. So, getting the batch mode stuff and the scalar UDF inlining stuff in is going to be pretty huge.
Anyway, that’s it for me. So, Darren says, thanks for the stickers. Yes, you’re welcome.
My pleasure. If you, to anyone who sends me their address, I have Twitter DMs open. If you send me your address, I will send you stickers. I’m happy to do that. Postage costs almost nothing.
And I got a very good deal on stickers. Sticker Mule is a great website for stickers and they often have sales and other things. So, that’s no problem.
Darren says, I haven’t looked at CTP3 yet. Did they make any significant changes or have you? I have looked. I did some tweets about new stuff that I found in there. There was nothing that like blew my mind.
There is a weird new use hint. And I’m going to go check on exactly what it’s called so that I don’t get, so I don’t misquote anything here. Excuse me, one of those days I can’t spell select right.
From valid. Is this not valid use hints? And it is called disable loop join avoidance.
So, there is a new use like option use hint thing called disable loop join avoidance. And I’m not exactly sure what it does yet. I have some guesses.
I have theorized a couple things. But perhaps it is to make stuff like key lookups more common. So, there are a lot of queries where you would expect a key lookup to occur and it doesn’t.
You get a clustered index scan instead. So, it could be useful for those. I haven’t put any of these theories to use yet.
Mostly because I like having these theories. I dislike having them disproven. Because then I was like, I don’t know what the hell to do. It could also be for columnstore too. Because I know columnstore queries are very apprehensive about doing any kind of key lookups.
So, I think it could be for that too. I don’t know though. I just don’t know yet. I just don’t know yet.
We will have to wait and see, won’t we? We will have to eventually wait for Paul White to break out the debugger and figure it out for all of us. Because that is usually what happens, isn’t it?
There is something new or there is something weird going on. And is there anything more comforting or is there anything more reassuring than the words, well, Paul White says. Because you just know that it’s going to be correct.
You know that there is a sufficient level of research and care taken in the words that he chooses to share with the rest of us. That you’re not going to be led astray. So, if there is anything, well, Paul White says.
Well, Paul White wrote. I heard from Paul White. I love it.
I love it. It’s a great source. Do we have any other questions? There are eight of you here at least. Are there any other questions? Nine of you. Wow.
Totally watching while at work. Lee, you devil. You dog. Zane says, you may occasionally get your mind broken by Paul White. Yes, that happens quite frequently. It’s a strange thing.
What happens when he starts talking. It’s crazy. It’s like you think that he’s just really good at SQL Server, but he’s good at like many, many things that surround SQL Server as well. And it’s like the stuff that he can also do.
So, I’m going to like relate this to like, there are some people who can get good at SQL Server, but it will occupy the entirety of their mind. Like me, like this is all I can do at once. I can’t have any other hobbies.
I can’t have friends. I can barely walk. And then there are people who like, like SQL Server is like swinging like, like below their weight. It’s like T-ball to them.
They can do SQL Server and other stuff. And that’s impressive. Paul White is one of the people. He can do SQL. He can know a ton about SQL Server and other stuff. It has not pushed all other knowledge out of his brain.
And that’s an impressive thing. I have to dedicate everything in this melon. It’s abused melon to SQL Server or else I will not. I will not know any of it.
I will have absolutely nothing. So, it’s always. It’s always fun to watch people who can do this and other things and be good at them. So, yeah, that’s fun stuff, too.
All right. There are a lot of you here. Holy cow. All you playing hooky. No one is at work today. What other questions do we have here?
Dan says, are singleton lookups good or bad? I found some indexes that don’t have any seeks or scans in them, but they do have singleton lookups. Singleton lookups just mean that they’re used for key lookups.
They’re not really bad. They’re used for seeks. So, they don’t have any seeks or scans against them, but they do have singleton lookups. All right.
So, yeah. So, usually that just, usually that means that they’re the key, like a primary key or a clustered index or a non-clustered primary key. And they primarily get used for lookup values that are not present in nonclustered indexes.
They’re not really a good or bad thing. I would just peek through the plan cache and I would peek through the plan cache rather. And what I would do is, since you’re using the Blitz procs already, I would say that you should run SP Blitz cache and you should look for a warning called expensive key lookups, which will point to, it will point to if you have an execution plan where a key lookup operation is greater than like 50% of the plan cost, I think.
And you could check those out. So, there are two kinds of key lookups. There are output key lookups where you just go back to the clustered index or the base table if it’s a heap and you just grab columns to show people.
So, those are like window dressing. Those are like columns that you’re just selecting. And then there are predicate key lookups.
And predicate key lookups are when you do the same thing except to evaluate a filter. So, if I had, let’s say, an index just on, let’s say I have a clustered index on column one and I have a nonclustered index on column two and my query is like select some column two where column two equals something and column one equals something. I might do a key lookup just to figure out that where clause on column one.
So, for me, fixing the predicate part of key lookups is usually much more important than fixing the output list part. So, unless I’m running into bad parameter sniffing, but working all that stuff out really turns into a very, very long conversation about what exactly is going on with the query. And it’s like, can we rewrite the query?
Do we really need all these columns? Like, is it any frameworks? Is it a sort of a ceiling? Like, there’s a lot of stuff that, like, you need to start working through when you see those things. So, what I would leave at is that there’s nothing inherently wrong with them, but you might want to take a peek through your execution plans and see if you have any expensive key lookups.
Again, SV Blitzcache will warn you about that. And that’s where I would go with it next. Maral says, thoughts on running SSIS and RS services on the same production box as the engine?
Yeah, not a fan of that. They take up weird resources and do weird things. I always want to have a different, I always want to have those things on a different server. I’m not crazy about them being on the production box.
Those resources are precious, man. I’ve seen y’all’s production servers. They’re not beefy. They’re not big beefcake servers. And even if they are, you don’t want SSIS and SSRS gobbin’ up the works.
It sucks from a licensing perspective, but from a keeping production safe perspective, it is much, much better. How is my goal of smoking cigarettes in a French graveyard? I am slowly working client by client.
I work towards that goal. Client by client. We’ll see. We’ll see how close we can get. I just need to keep doing it. It would really help if I didn’t live someplace so damn expensive.
Oh, man. Yeah. Yeah.
Me and Tara agree on a lot of stuff. That’s why we’re still buddies. At least I hope we are. I don’t know. She might hate me now that I’m consulting competition. She’s working at straight past SQL. And I don’t know.
I don’t know. Maybe we’re at wars. Start licking some shots. Dude, drive by or something. I’m kidding.
No, actually, it’s been really nice. Mike Walsh has been great. He’s been offloading. When he has too much work, the poor guy, when he has too much work for his people, he’s been offloading stuff to me. And so, you know, it’s really nice to have that sort of like, you know, if nothing else is going on, I can, you know, like, hey, Mike, what’d you got?
And, you know, have, you know, a few days worth of work to kind of fill up the week, which is always a good thing. So no, no beef there. No actual beef there.
Tara is just very, very busy having to be a remote DBA now. And that’s the lazy consultant. So good for her. That landed in almost the same exact job. That’s a nice, nice touch.
Zane posted a link. Memory management with SSIS and SQL Server can be a large problem. You don’t want to drop pages out of memory because an SSIS package sucks. Yes.
And from what I know, most SSIS packages suck. It’s one I’m aware of. Most SSIS packages suck. Nature of the beast, I suppose.
Nature of the beast. Yeah. Not Zane’s own. Zane’s SSIS packages are great. They’re wonderful. Unfortunately, Zane is probably not going to be writing too many SSIS packages anymore.
Mr. Zane Brunette has recently accepted a job as a PFE at Microsoft. He’s going to have the blue badge and the list of trace flags and access to source code and all sorts of other crazy cool things that I’m envious of except for the working at Microsoft part.
I’m very envious of many things that he has going for him except there was a way for me to get those things. And not go to jail, I would do all of those things. And I’m just too happy being by myself.
That’s the problem. I love being alone. It’s wonderful. Love. I love loneliness. Loneliness is great. A lot of people think loneliness is a dirty word. I love it.
It’s the best thing in the world. I can do what I want. Wear what I want. Not wear what I want. Sweatpants at the gym. No two to blame.
Yeah. Yeah. Yeah. That’s true. That’s true. I do have to accept the blame for everything. But it’s okay because I’m the only one who notices. I’m very lenient on myself in that regard.
Very lenient. Oh. Yeah, that was fun. So yesterday I was at the gym and I was doing deadlifts because that’s what I usually do. And I was doing some, I was doing like singles, like 475.
And I got, well, I was doing doubles at 475 and I got bored. And I wanted to start doing singles at a higher weight. So I started doing singles at 525 and I videotaped one.
And I think it went particularly well. It went like, it was just like, it went up like crazy. You can barely see what’s going on there. But man, I was very happy with how quick it went up.
It’s going, it’s going, it’s going. Ah, come on. Lift it dummy. Why are you so slow? Big fellow walking behind me.
Yeah, that went all up. That went all the way up. I was very happy with that lift. And then I did, I did a few more of those and I was dead. I still think I’m dead.
That’s why I’m so hot and hangry today is because it’s tough to recover from doing that. It’s hard. It’s hard.
Your central nervous system is just like yelling at you. It’s like you are the worst. Why do you do that? I don’t know. I don’t know. Mostly just to impress myself.
No one else is impressed. Ah, man. All right. Come on. Give me a, give me a question here. People can’t not have any questions. Why else would you show up to Q and A?
You didn’t have questions. You can’t just, you can’t just be here to watch me. I’m not that, I’m not that excited. I’m not that interested. I’m not that interested. Come on.
At least your forehead doesn’t explode like half the horn. Yeah, that’s true. It comes close though. If I, if I strain hard enough, I can get, so I can get, I get this like split vein thing right here.
It looks like, like the flux capacitor. It’s crazy. Uh, yeah. Rogue plates. Can’t do it. If I don’t have, I mean, what other plates do you buy? The only thing that sucks about the gym that I go to is they don’t have like the, like the, like real Ollie, like thin plates.
It’s all like the thick, the thick rubbery ones, not like the thin and ones. So like, if you have like six blue plates on the bar, that’s all you can do. Like, and then if you want to do like more, you have to like take off some of like the good rogue plates and put on like some of the, the metal octagon plates.
And that’s, I mean, it’s, it’s not that they’re bad. It’s just, you know, it breaks up. It breaks up how cool it looks.
And when you’re OCD like me, and you just want to have a bar, same, like, damn it. Give me, do this. Let’s see the thoughts on diagnosing network connectivity issues other than trying to tell net via command prompt or using the deck.
So is it like you set up a SQL Server and you just can’t connect to it off the bat or like queries are timing out getting weird? Cause it’s like, you just set up a SQL Server. The first things I always do is check firewall rules.
If it’s a named instance, I make sure that the browser service is running. I’ve also learned to very, very, very carefully check the instance names. Because there, there was a, there was an, there was an instance around the time.
Oh, I don’t know when CTP three came out when I spent 20 minutes trying to connect to the wrong server name. So that was, that was great. Random users and apps can’t connect.
The most common thing, most common reason that I’ve seen for random users and apps not being able to connect is thread pool. Thread pool is when you run out of worker threads and you can no longer assign even like a session ID to a query. And that’s a pretty bad thing.
So what I would do is if you wanted to run like SP blitz on your server, you might get a warning about thread pool weights. If you don’t have that or can’t run that, then you can, you can run pretty much just any weight stat aggregating script and see if you have thread pool weights on there. Like lining up because that’s usually a sign that bad things are happening.
Thread pool generally happens if like you have a bunch of parallel queries coming in and trying to run stuff at once. Or if you have like a big blocking chain or something and you know, stuff like if you have a monitoring tool, you might see big blank spots in the monitoring tool too, because that thing can’t, that thing needs threads too. And it can’t connect.
So there’s a lot of stuff, a lot of reasons why that might happen, but thread pool is among chief among them. Stop naming your instances. Oh, yeah. Woo woo.
Stop naming your instances. Weird things. I named my instance CTP and I thought that I had named it CTP two in the past. And so I was trying to connect to CTP two and I couldn’t.
And I guess kept trying to like, damn it, what’s going on? And I was like, oh yeah, it’s just CTP. No two. And I did that. Everything worked and it was magical.
I felt it was a big, big, big win for me there. It’s like amazing. Let’s, let’s sanity check. I never sanity check first. It’s always like the fourth or fifth thing I do. And then I feel dumb because I’m like, through all these years, why isn’t that the first thing that you do?
Like, I’m not crazy. I can’t be crazy. I know I did this right. Oh, yeah.
Dan has a good point. I do have a script. Now you’re going to make me go find that. Let’s see if I can remember how to type. There we go.
I can even get to, I can even get to my own website. Paul Randall even said, this is a nice script. If you check the comments, you check the comments.
There’s a thing that says from Paul, comment from Paul Randall that says, nice script. And then I felt bad because like a week later, someone posted a question about wait stats on the DBA stack exchange site. And they were like, I ran this script and they paid they pasted the script in but it was like just the script was like just the queries from the script and they were like, I ran this script.
And it’s telling me that I have like 86% CX packet or something. And I was like 86% of what in my head. I’m like, damn it.
Why do you do? And so like I write this answer. I’m like, the reason why that script stinks is because it doesn’t tell you like what the uptime of the server is. You don’t know what 86% of, you know, like, like if you have like, you know, like three days of CX packet weights, but your server’s only been up for a half hour. That’s way different than if like you have three days of CX pack away.
So it serves enough for a month. And I didn’t like, cause you know, working for Brent for the last four years, I didn’t, I haven’t spent a lot of time looking at other people’s scripts. It was always very much like, you know, write scripts from scratch, like do stuff on your own.
Don’t look at other people’s stuff. You don’t want to like get, get caught like influencing or have anyone say you stole that script I posted on the blog. You suck.
And so I was like, okay, no problem. I’ll write everything. So I don’t remember what, what Paula scripts looked like. And so like, like a few hours later on my answer, there was, there was like, there was a comment from Paul.
And that’s when, that’s when I started thinking, I was like, why would Paul randomly comment on this, this answer? And then I went and I looked and I was like, oh shit, that’s a script. It’s like, eh.
So I know he’s not watching, but sorry about that, Paul. Honest mistake. It wasn’t, it wasn’t really, I wasn’t trying to make fun of you.
I was just trying to help someone. Anyway, I can’t remember if I’ve told that story on here before. I remember telling someone that story before.
I forget, I forget when it was. Anyway, could have been here. It could have not been. Don’t tell me if it was, cause I don’t want to get embarrassed. Don’t tell me. Don’t tell me anything.
Don’t tell me when I forget things. There’s nothing more embarrassing than forgetting things. Let’s see. Anything on Twitter. No. So I, on Twitter, I made a prediction that. If SQL Server 2019 does not increase the RAM cap.
It really should increase the RAM cap to 256 gigs of RAM. 128 is nothing these days. Nothing.
My desktop, this thing that I can kick. Willy nilly has 128 gigs of RAM. There’s no reason to expect. A.
Production SQL Server instance, even standard edition to be capped to 128 gigs of RAM. It also helps because there are very strange instance sizes up in the cloud. They’re very oddly sized with the CPU to RAM ratio.
And I think. There are, there are many situations. There are many servers that’s see that standard edition would make sense on if it could have 256. Gigs of memory, but don’t make sense.
Um, because it only accepts 128. Like there are some 16 core instances up there. When, or like 16 to like 24 core instances up there that have.
Like 256. Or like 200 and some 66. Some like weird number gigs of memory, which is, which sounds great. And you’re like, man, standard edition would be cool to put on there, but.
I would have. A hundred or so gigs of RAM. Sitting around. No, I, I, I get that.
The differentiation, but they’re like that number is the cap on the buffer pool and you can use stuff above the buffer pool. I just don’t see a lot of people who are like, I only need to cash. 228 gigs of memory of stuff in the buffer pool, but boy, howdy.
I sure wish I had a hundred gigs for memory grants and the plan cash and other stuff. I just, I don’t, I don’t meet those people terribly often. So I think that having standard edition go to 256 gigs of memory would be an excellent thing.
But anyway, I was saying that my prediction was if it doesn’t have, if they don’t increase the RAM cap or if they don’t make the scale or UDF inlining or what do you call it? Batch mode for rowstore available on standard edition, then I think adoption is going to suck.
Going to continue to stifle adoption because the salespeople are jerks. They really got to cut that crap out. Not helping anyone. Not doing yourself any favors.
Not, you know, you’re not selling more enterprise licenses by doing that. You’re keeping more people on the old licenses as they already have. People just refuse to upgrade.
I know I knew people on SQL Server 2008 standard edition who would not upgrade to 2008 R2 because in 2008 there was no cap on memory. 2008 R2 is when standard edition got the 64 gig cap.
So there are people who, clients who I knew stayed on 2008. Probably still on 2008 because there was no cap on memory. They could put in as much as they wanted.
No. I don’t know. That’s what I do. But, you know, I’m free spirited.
Might not fit in well with that corporate culture. Anyway, we’re about at the half hour mark. It’s very hot.
It’s very hot. Very hot in here. And I want to stop being hot. So I’m going to get these headphones off and step away and sit down and drink some water. Anyway, thanks for joining.
I hope you at least had a good time. Anyway, I’ll catch you next week because as far as I know I’ll still be here. It’s amazing.
Friday after Friday. It’s nuts. 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.