ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I found myself reflecting on the challenges and frustrations of working in IT, particularly when it comes to database management and moving between different systems. The conversation touched on a range of topics, from the excitement surrounding SQL Server 2019 to the difficulties of convincing users to upgrade from older versions. One of the key points was the disparity between the features available in newer versions of SQL Server and the reluctance of many organizations to move beyond their current setups. This led to discussions about memory management, trace flags, and even the transition to alternative databases like PostgreSQL on Linux. Despite these challenges, I shared my experience with a specific instance where setting up lock pages in memory on a laptop helped silence error logs but also highlighted the complexities involved when managing resources across different environments. Overall, it was an engaging session that underscored both the benefits and pitfalls of working with complex IT systems.
Full Transcript
I’m live. I bet no one’s going to show up this week because I missed two weeks in a row, which is terrible of me. Terrible of me. What a horrible person I am. Let’s see here.
Uh, yeah. Still no one. Not to. I’m going to wait one minute and then I’m out of here and I will delete this from memory. Oh, there’s one person.
All right. All one of you. I hope you have lots of good questions. Otherwise, otherwise we’re going to be very happy to be able to do this. I’m going to be very lonely. Oh, God, it’s you. Oh, man.
It’s going to be like insider chat when I just talk about funny stuff that happened in Madison. All right. Two of you.
Two of you. Not bad. All right. Not bad. Not bad. All right. Man, this is what I get from missing time. Messes and messes everyone up. That and my camera refuses to focus. Why are you so close?
I’m back here. Oh, God damn it. There we go. All right. Camera’s sort of working now. Maybe. Back to one. Crap. Someone ask a question.
Someone say something. I’m going to be like this is getting out of hand. Getting way out of hand. Ba-bum.
Ba-bum. Ba-bum. Ba-bum. Ba-bum. Ba-bum. Ba-bum. Ba-bum. Ba-bum. Oh, man. All right.
This is dull. Forrest is here and not asking questions. No one else is showing up. Actually, I can’t even see who’s still here. That’s the depressing thing. I don’t have like an attendee list. All right. This gets one more minute.
Not even a minute. This gets 30 seconds. And then it’s dead to me. Dead to me. Uh… Fifteen. Ten.
Uh… Two people. Is one of you going to do something interesting? I don’t know. I don’t know what’s going to happen. What’s going to happen? I’m not a squirrel to sit here.
I’m tuning the worst door procedure in the world right now anyway. It is like eight CTE chained and joined and bound and gagged. And the query plan is like this big about. Is that a hole in my shirt?
Oh, it is. Man, I look terrible now. I didn’t know that was there. Anything over here? No. All right. We’re getting rid of this shirt when we’re done. Now you can all see the one spot on my body that I don’t have a tattoo. Sad. We have this like just running this thing to like get a plan for it and compare other things to it is absurd.
It takes, I don’t know how long. It just runs for a very long time. Doing a lot of dumb things. Doing a lot of things that I don’t necessarily agree with. I was in Madison last week.
And I had a client thing the week before. And thankfully I have a client thing. After this. At one. So I’ll be bothering.
Bothering people for money instead of bothering you for free. It’s always a good call. And then. Yeah. So Madison was a lot of fun. I had like 50 people. When my, my pre-con.
It was sold out. Wild. A lot of people in there. And the event. The event itself was pretty fun. I got to see.
Joe Obish talk about columnstore. Which is a treat. Got to see. I don’t know. I forget. I forget what else happened. Yeah. Selling stuff out is a, it’s a fun, fun feeling. But then it makes you think like, man, what if there were, what if there were 10 more seats?
You don’t get greedy, but you’re like, oh, what if? What if? What if, what if that room was a little bigger? What could, how many more people could we have taught about SQL Server and taco consumption? And the, the flight back.
Well, I, before I, before I talk about anything else, if, if you’re able to attend SQL Saturday Madison next year, I highly recommend it. Especially if they invite me back. Yeah. The flight back was, was weird.
Because what happened is. They, they, they, they thought that there would be unexpected, they expected unexpected turbulence. And they stopped drink service.
Yeah. So that was, that was a bummer of a flight. Even, even sitting in the, in the upfront section, I couldn’t get, I couldn’t get another bottle of, another little mini bottle of wine. And I was very sad about that. And so I was, I was sitting there just waiting.
That was, that was fun. Farah says, if you were a client, how would you pick a quality consultant? I would go to ericdarlingdata.com. And I would click on the, the contact form. And I would, I would send an email to a blurry, a blurry gentleman whose camera refuses to focus.
Well, I mean, really, it depends on what you, what you want out of consulting. There are some people who want, you know, sort of long-term remote DBA work. And, you know, someone to provide on-call support.
And, and for them, I think Mike Walsh over at Straight Past SQL is a pretty good choice for that. There are some people who want advice about their SQL Server. And that’s where someone like me comes in. I’m very good at giving people advice. I’m not good at giving myself advice.
Clearly ended up with, with this, with neck tattoos and this haircut. So I don’t really know that I would give my, I would take advice from me about me. But advice about SQL Server for someone else, I’m, I’m pretty good at. But then there’s people who want like, you know, sort of someone to jump in and start tuning things. I can do that.
And I’m, I’m, I’m perfectly comfortable doing that. You know, if you have a performance tuning stuff, I’m happy to do that work too. But if you need to like advice about H A and D R, I would probably choose someone other than me. Because I’m going to tell you the same thing I tell everyone else.
Get a failover cluster and get log shipping and don’t think about anything else. Zane says that he would take advice from me about lifting large objects. The funny thing is, is I lift fairly small objects that just happen to weigh a lot. So if I had to lift something big, I don’t know.
I don’t know how I do. I don’t know how I’d handle that. Peter says, why even fly if there’s no booze? I feel the same way. Man, when I, when I, when there’s a, when there’s a plane trip involved, it’s me. Like I get to the airport like two hours early.
I’m hanging out in the lounge. I get on the plane and I’m like, like slam the car down. Like give me all the red wine you have. I’ll give you back whatever I don’t finish. And, and that’s pretty fun. Let’s see.
I don’t know. Let’s see. Peter says, how, how is it settling into being, being your own CEO? Pretty good. Pretty good on that. Look, I got, I got, I got a thing. Yeah. Look, it’s got my name on it. So I’m pretty happy with that. The card number isn’t on the front. So don’t bother trying to pause that to figure anything out.
The card number is on the back. I checked. So you can’t tell anything from that, but I’m, I’m, I’m doing, doing pretty good as my own boss. At least I’m, at least I’m pretty happy with my performance as my own boss. I don’t know how other people feel about it. We’ll have to wait and see.
I got, I got my first wireless phone charger. Cause the battery port on my like thousand year old phone is busted. They’re not busted. It’s just very flaky. So it’s a lot of like me just like wiggling wires to get things working. Anyway, should probably answer a SQL Server question at some point.
If anyone has one, I don’t know if anyone has one. What do I think about SQL Server 2019? Yes.
I know who you are. You’ve been, uh, from what I’ve seen of it, I like it. I know. I mean, it’s only in, it’s only in CTP and it’s only CTP 2.4. And you know, it’s still good. It’s still good. It’s not even like a, it hasn’t even hit release candidate yet. And I have a feeling that it’ll be at least one more, if not two more CTPs before we hit release candidate.
But so far it’s very promising. You know, um, it’s, it’s got some nice steps forward in a lot of ways for things. And it’s going to be interesting to see in, in six years, how those steps forward finally pan out when people start moving to 2019. Because damn you people can’t even get off 2008 and 2008 R2.
How am I supposed to get excited about 2019? How am I supposed to get excited about it? What demos? Man, it’s rough. I’m like, look at all this cool stuff you have. Look at all this cool stuff you have in 2019.
People are like, eh, I got 2008. I’m like, man, it’s brutal. It’s brutal out there. Like, I don’t know how, I don’t know how to talk people into doing something smarter with their, with their lives and with themselves.
It’s very frustrating. Very frustrating. But no one listens. No one listens to consultants anyway. It’s like, what do you want me to do? What do you want me to do? Let’s see.
Peter says, I’m moving from SQL Server to Postgres on Linux starting Monday. Please tell me why I’m insane. I mean, it’s, it’s crazy because, you know, uh, you know, I actually, you know, I don’t know. You, you, you, you might be really into Postgres and you might be really into Linux. I, um, I once took a job very briefly administering.
Well, the, the job description was like, it’s Postgres SQL. And I was like, cool. Well, I I’ve heard of that. It has some neat stuff in it. It’s developer friendly. I’ll work with that.
And then I, then I got in there and it was red shift. I was like, oh, that’s a little different. It’s like a, like kind of a fork of an older Postgres, but I was like, I’ll, I’ll stick with it. Let’s see what happens. And then it was just like, well, we’ve got these consultants. And I was like, oh boy. And the consultants are like, we’ve built an ETL process.
And I was like, sweet. I don’t have to do any work. And then I was like, is there any documentation for the ETL process? And they were like, no. And I was like, could you make some? And they were like, we’re not full-time employees. I was like, what? And I was like, well, can you, can you please just like, give me like a brief overview of how to use the ETL tool that you built?
Because it was, it was not like a command line tool where you could do anything and things were exposed to you. It was a command line. I mean, sorry, it wasn’t a GUI. It was a command line tool. And you had to like do a bunch of stuff to the files to get it to work. And none of it was like, like written out.
And so I’m sitting there and I’m like, all right, look, you need to document. This in some way that I can at least like get off the ground with it. And they were like, okay, fine. And they made a video. It was a 20 minute video.
And there was no voiceover. There was no voice explaining what was going on. There was just a mouse pointer circling things and like pointing agitatedly at things. And the audio was like a country, like, like diddly guitar loop was like, or something like that.
But it was like that over and over again. And I’m sitting there listening to this and watching this like frantic mouse over. And like these, like the video was on like, like this big, and I couldn’t even see what was being written into the command line. And I quit soon after that. I got a new job after that. It was like immediate.
It was like, I was like, like, as soon as like I’m watching this video on one screen and like working on my resume on the other screen. And I think I lasted like, I think it was like a month and a half, maybe two months there. And it was just like, this is like, and I would have been happy. I’m not happy.
I would have been okay working with like Redshift and doing stuff there. Cause it’s a pretty cool tool, but man, it like the, everything else that was going on around, around that, that project was. I don’t know a lot of German curse words.
So I don’t know. Let’s see. Laura says thoughts about memory management. In other words, enterprise edition and eight gigs more of memory plus lockpages of memory. Wait, eight gigs of memory. Why do you have eight gigs of memory on enterprise edition? That’s like, like my, I think my phone has most of eight gigs.
Why? That’s not, not, not rational. It’s not sane. Don’t do that. Yeah. That was, that was the hamster dance song. Why do you, okay.
I don’t understand what your question is. And what you have a lot more memory than my eight gig minimum. I don’t care. That’s fine. Does it, does it never bothered me any minimum memory, setting minimum server memory to eight gigs.
I guess. If you have lockpages and memory on, you shouldn’t need to set minimum memory. Uh, and, uh, yeah, I, I, I’m, I’m trying to figure out a way when like lockpages and memory would be a worse choice than setting min server memory. And the only time that it’s like, either the only time, so be fair, either of those settings will make things go kaboom.
If you have an overcommitted VM and that memory balloon driver starts freaking out. So be careful with that. If it’s a VM and the VM host is severely overcommitted, then, uh, I would, I would avoid, I would avoid either of those settings, but setting min server memory to eight gigs. I don’t think is gonna really do much of anything.
Do you use trace like eight 34? Uh, no, not really. Um, no, uh, I’ve never really found, I’ve never really found a, a, a, a use case for, for me anyway, where it made things significantly better. Um, so I’ve never, I’ve never like really set it up and kept it.
It was, it was like, like sometimes I tried stuff and I couldn’t, I couldn’t find a tangible difference. There might’ve been some like cool spin locky under the, under the covers difference that, you know, um, uh, I don’t know. Maybe, maybe someone else found something cool to do, but, uh, I could never figure out what, what would be fun or interesting to do with that.
So, sorry, I wish, I wish I had better advice about trace like eight 34. Uh, I know that there was a bunch of, um, bunch of guidance about when to use it. And for what, like a while ago, I just don’t know.
I just don’t know that it’s terribly relevant anymore. See, Peter says, I set lock pages of memory on my laptop just to shut up the error log. Uh, I set lock pages in memory too, but, uh, then sometimes I have to do stuff that requires memory for other things. And I have to like restart SQL.
So it gives up the memory. So like, oh, my, like on my laptop, I have 64 gigs of memory. Uh, my local server instance can get like, I think 50 gigs or something or 55 gigs. I forget what I haven’t said to. But then like, if I have a, I have a VM that I set to get like 16 gigs or eight gigs or something, eventually I can’t start my VMs or like the VMs start freaking out because SQL Server is just like, nope, that’s my memory. You can’t have any, you don’t get any of that bad DBA.
Okay. Let’s see. Uh, all right. Nothing fun going on there. Uh, so I’ve, I’ve started taking, well, I’ve started offering to take questions for office hours via like Twitter and email and stuff. And, uh, so far I haven’t really gotten any.
The one, the one question that I got was like, like, like, like, like, like a page long thing. It was like, you should tell me how to like do this very specific thing. I was like, oh yeah, that’s consulting. You should, should pay. Can’t answer that in a half hour of free webcast.
Let’s see. Uh, he just says about half the time I have SQL Server as manual start. No, I have it as manual start too, but then I start up and I do stuff with it. And then I have to switch to use a VM to do something. And I hit VM problems. Is there a hashtag or something?
No, just tweet me, whatever it is, whatever you want to call it. I don’t know. Whatever, whatever you call the form of communication of, on Twitter, where there’s an at sign and then my Twitter handle, it would be a fine way to get to things. I think.
I think anyway, I can’t, I can’t say for sure for everyone that that would be a good thing. And some of you might not even be on Twitter. I don’t know. Some of you might, might do other things that, um, that like, I don’t know, make you happy in life. Or I don’t know.
What else? What else? What do people do who aren’t on Twitter? Like what, what’s your internet? Like, what, like, what do you, what do you do with your internet existence? I’m curious. Like, it’s stuff that like, I would be interested in. It was like, cause like I need, cause now I need stuff to do that’s not Twitter.
And that would be, I think appropriate for me to have like, just non-Twitter things going on. So if you have, if you have non-Twitter things, that would be great. Like, like going outside.
So, yeah. So, but see, the thing is, every time I go outside, it’s expensive. When I go outside, it’s like, uh, I, I go eat food. And when I go outside and eat food, that’s expensive.
Cause I have a wife and two kids and I got to drag them everywhere. Or if I, even if I go, if I go up by myself, I’m like, well, I’m going to go someplace real nice. Now they don’t have, I don’t have those losers. But no, I was kidding. Uh, but yeah, going outside. And then like, I go to the gym that’s going outside, but the gym isn’t far away. I, I, I’ve done an admirable job of, of going to the gym in the laziest way possible, where the gym is like a three minute walk.
So Laura says I uninstalled Twitter on the phone to let go of it. Sometimes that’s a good, that’s a good call. Um, for the, for the first like month or so, I was just like, you know, I’m only going to, uh, have, I’m only going to use Twitter on my phone, like through the website.
And then I realized that like, sometimes that was just a really, really bad way to be doing things. Like I spent longer wrestling something on the website than is this really quickly, quick to do with the app. And so I don’t know. That was it.
It says what technical awesomeness did attendees here? Madison precon most appreciate. I don’t know. I haven’t gotten session feedback yet. Um, I think some of the stuff about how SQL Server allocates threads and memory to queries went over pretty well.
And I think, uh, people’s people get a really eyeopening, um, experience from, uh, when I show them exactly how contention works with, uh, sans. So like showing them like that, the wire is a limiting factor and that like, like contention there, like the, uh, like the, the competition for a shared resource is what often makes sans feel slow when it’s not, but I shouldn’t, I can’t give away too much.
Then, then no one will ever pay to see it again. I’m surprised they paid to see it in the first place, but if I get, if I get to have that happen again, that’s wonderful. So there’s that, I don’t know. There was at least one attendee of my precon in this room. So maybe they can, maybe they can tell you more about what they, what they liked about it.
Yeah. Dang wires. I hate those dang wires. Uh, is the three minute walk to the gym, my car. Yeah, basically. Sometimes I, uh, yeah, that’s about it. Yeah.
Yeah. I don’t know. Occasionally I wrestle with my humanity. That’s a, that’s a, that’s a, that’s a quick fight. Humanity never wins. Uh, let’s see here. Darren said, uh, wait, wait, there’s another, there’s actual, see what’s it?
Uh, actual last query plan. Thoughts. Um, no, I, mixed feelings. Uh, one, one of those mixed feelings is like. All right. If you’re going to invest in that, put it in query store, because that’s the kind of thing that would make people turn query store on.
But at the same time, if it’s in query store, most people are going to go, I’m not turning that thing on. Um, but I think it would, it would talk some people into it. I think it would be, uh, work. I think that would be a worthy thing there. Um, it doesn’t really collect actual, actual stuff that I would want inquiry plans like operator times and like weight stats and stuff like that.
So then that, that, that, that would be really compelling for me to start using it. Uh, you know, it, like, um, like as soon as I saw it, I was like, man, it would be like awesome. If I could like, you know, tweak blitz cash to look at that DMV instead and get all this actual query plan information out.
And then I saw what was in it and I was like, eh, that’s not actual enough. Not actual enough for me to get into GitHub again. So yeah, that was that. Uh, let’s see here. Any SQL Saturday pre-cons, any other ones this year? Um, no SQL Saturday pre-cons lined up yet, though I am open if anyone out there is listening and, and is in need of, uh, uh, uh, a funny performance pre-con, uh, at their SQL Saturday event, hit me up.
There’s many ways to do that. Um, let’s see. Uh, I don’t know how to say that. Uh, do you listen to music or podcasts while gymming? No, I, uh, headphones just get in the way. Uh, if you, if you get, if you’re, if you get bored at the gym, you’re doing cardio and I have no sympathy for you.
If you’re lifting weights, you don’t get bored. If you’re lifting weights here, you’re too, too encumbered to get bored. You gotta, you gotta really do something weird with weights to get bored lifting weights. He says, what percent is of your clients don’t use query store so you can’t convince them to turn it on?
Uh, I don’t know. Um, I can convince most people to turn it on, on a dev server and collect information on things. Uh, but, you know, in prod people are a little bit more prickly about what they turn on and what they use.
Josh says, I wonder if more people would turn on query store if the default capture mode wasn’t all. Uh, so maybe, well, no, no, cause when most people turn it on, they have no idea that there’s a default capture mode. Problem is it like no one ever, it’s like, it’s like nothing about when you go look around at stuff, you’re just like, oh yeah, turn on query store.
There are options. I’m not gonna, I’m scared of those. Um, but even if it was auto, I mean, like people turn it on and they have all and it’s like collects everything and maybe burns a bunch of CPU and everything stinks.
And then they turn it off and they’re like, well, that sucked. Never turning that thing on again. Tech news says, where are you from? I’m from America. Where are you from? Tech news. Tech news.
Anyway, like query, and query store is one of those things that I’m afraid is just gonna like hit a wall. Cause this query store DMVs are terrible. Like the information they collect is great, but querying them is a nightmare. The built-in reports are janky as hell.
They take forever to run. And I don’t necessarily blame like whoever wrote the reports, but you know, you’re, you’re, you’re hitting these like, you know, I don’t know what you want to call them. Um, base views or tables or table type functions or whatever. And it’s just like, Oh, it’s a very, very slow sometimes.
And you’re churning through a lot of data and you’re, you know, you’ve got query plans coming up. You’ve got query texts coming up. And I just feel like these are things that are not as difficult to, to deal with in the plan cache.
But for some reason, query store made them even harder to deal with. Right. Tech news is from India. I’ve never been to India. I might go there someday. I should, I should someday go to the homeland of my favorite food on the planet. Which David Lynch Dune version?
Are you, do you mean like, like between like the regular version and like the extended cut and the director’s cut or like the TV show? Like which, which versions, which versions are on, which versions are in play here? Yeah.
Yeah. I like it. I like, I like it for as long as I can get it. And I’ll tell you why. Most of the time these days when I get to watch Dune is when my wife and kids aren’t home. And I usually end up taking a nap while I watch it. And I feel like if I fall asleep at the beginning of Dune and I wake up at the end of Dune and it’s like the director’s cut, I’ve taken a good nap.
That’s like a solid, like, like two hours. Of nap time. So I prefer the director’s cut for that. If I, if I wake up, if I use the regular version and I wake up and Dune is over, I have no way to gauge how long I slept. But if I wait, like fall asleep, like when they’re, when they’re doing like the intro stuff.
And then I, I, I wake up when like the, like when Paul is fighting, whatever Sting’s name was, I forget now, whatever Harkonnen that was. Then I’ve had a good nap. Uh, TZH says being able, unable to do things like click on a query in the query store UI and saying, I really don’t care about this really limits its useless.
I’ve tried to find the DMVs instead of the behind it, but as far as I know, they’re really not friendly. I mean, they’re all, they’re all available to you. Like you can find them on, on books online, but yeah, they’re, they’re really unfriendly to query. Um, and the, the, the way the data is stored is kind of annoying to deal with.
Cause if you want to put like any of the metrics in human consumable form, you have to like do some math to convert stuff from like microseconds or from 8k pages. And you’re like, man, just tell me what’s bad. You have all my queries.
Tell me what sucks. You have all my query XML. Like, like tell me what’s bad in it. Why, why do you make me go jump through all these damn hoops to do things? And I feel the same way about monitoring tools that, you know, uh, grab like endless server information and then like give you no guidance about what to do with it.
They’re sitting there watching your server. They’re taking information constantly from your server. They’re collecting metric after metric. They’re offering you absolutely no solutions. Just sitting there watching they like, and they have, they can like, they can asynchronously same thing with query.
So can asynchronously just look through like a little bit of XML at a time. Look for like some bad stuff in there. Look for non-parallel plan reasons. Look for implicit conversions. Look for missing indexes. Look for, for anything. Go in there and show me what’s wrong. Tell me what’s happening.
And it drives me nuts. It’s software that makes your life not any easier. Software should never, never like leave you like where you are. Use software to make your life better, to make your life easier. Using software that makes your life harder or doesn’t actually solve a problem for you. Especially when you have to pay for it.
Sucks. Sucks. Fix your software. Sons of guns. Maybe, maybe there are some daughters of guns too. I don’t know. I have a whole family of gun relatives. Fix your stuff.
Get your act together. People aren’t going to pay 1200 bucks per instance for garbage for long. Tell you that much. Let’s see. Peter says, I was told by a guy with neck tattoos and implicit conversion warnings might be a red herring. Yeah, that’s true. Some of them are. And there’s very easy ways to tell the difference between which ones are and which ones aren’t.
So seek affecting, which is in the XML. You can see when it’s seek affecting. Then, perhaps you have an issue. But if it’s just the cardinality estimation one, that happens when you select stuff and it converts and you’re like, wow, how is selecting something is a different data type going to mess up cardinality estimation?
You know damn well how many of that is coming out no matter what. It’s like the no join predicate warning. It’s absurd. There’s no join predicate, but there is.
And it’s just because there’s not one at the join that it freaks out. It’s like, okay. You really got me there. It’s solving a big problem for me. Oh, man.
All right. I got a client thing starting in a half hour, so I got to go. Thank you all for showing up and asking me questions. I will be back next week. I hope. You know, assuming good health and lack of death continue to shine upon me. I will be back next week.
And yeah, I promise I’ll actually even do some promotion for it. Who knows? It’ll be crazy. Anyway, thanks. I’ll see you next time. Goodbye. Goodbye. Thank you.
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.