00:08:53 – Identifying Performance Issues with Wait Stats
00:11:16 – Underestimating Row Counts in SQL Server
Full Transcript
Erik Darling here with Darling Data, and we are going to have ourselves an office hours in which I do my Darling Data darndest to answer your very, very important SQL Server questions. We have a nice time with this, don’t we? We enjoy ourselves here. If you would like to ask your questions for office hours, there’s a link down in the video description where you can do just that. That is free, that is online. the house. But if you’re going to do that, you should also do this and make sure that other people get to see your great question and get a great answer, right? Get a top answer. There are all sorts of other helpful links down in the video description as well. You can hire me for consulting, buy my training, at a discount, right? We offer coupon codes down in the video description for people who love me enough to visit this channel, and especially ones who watch the intro here. And you can also, if you feel like you’re getting like four bucks a month worth of entertainment information, knowledge, anything like that, you can also choose to support the channel up there. If you are in the market for free SQL Server performance monitoring, and I’m only lying to you a little bit here because it’s not just performance monitoring anymore. I got suckered into adding in AG monitoring, and I didn’t like it. I’ve got, I’ve got, I’ll show you in a minute, but oh God, it’s a annoying. AGs are the worst, man. But if you, if you want free SQL Server performance monitoring, I’ve got it open source. You can see everything it does, doing everything that, you know, you would care about knowing how a thing works.
It gets all the interesting performance metrics for your servers. And the new version that is a headless Windows service backed by a Postgres database is available now. It can, it monitors an entire fleet. I think up to like 500 servers was my benchmark test on it. So that all went pretty well. And that’s, that’s, that’s, that’s up and running now. It’s a replacement for the old full dashboard that would like create a SQL Server database and you have to like put it on your production server. That stunk. I didn’t, I didn’t love that. I thought that it would be cool to like, hey, back up your data and send it to someone to analyze. It turned out that wasn’t a thing. So I was wrong there, but I think I’m right about this one. And, and, um, you’ll see, uh, if you, if you go get it for free, uh, that it can easily monitor many, many servers, but let’s go. I’ll show you that real quick. Uh, so I’ve got Docker down here and Docker has, uh, my AGs in it. Uh, and boy, that’s annoying. Uh, but here’s the, here’s the, the, the, the, the refreshed, uh, monitoring tool surface, uh, for, um, for the, the, the, the darling, uh, performance monitor.
And, uh, here is our availability groups tab, right? And that’s, this is, I mean, there’s not a lot going on with my local availability group because it’s a tiny little thing in Docker, but, uh, it is up there and working. So, uh, we, you, you do have, we do have that going for us. Oh, that’s a fun time. Anyway, let’s answer some questions here. That’s what we do. And number one, had a situation where hundreds of spids ended up in a sleeping state with one open transaction.
Hmm. This turned out to be a underpowered app server. What was happening here? Uh, it sounds like you figured it out. It was an underpowered app server, uh, app, just not picking up the thread again to close it off. Uh, yeah, it could be that. Um, you know, uh, I suppose like you could have like the application, uh, server version of like thread pool. A lot of the times when this happens though, you’ll also see async network waits pile up because sometimes it’s the app server being really overloaded by the results that SQL Server is returning to it. Um, you can do a bit to sort of help your app servers out, right? Uh, if you control the application enough, you could add in sort of like a rate limiting and maybe like cap off result set sizes.
So they’re not blowing your app servers to smithereens, uh, whenever queries run, uh, there can, there can be two useful ways of, of sort of, uh, making sure that they stay happy and healthy. Um, but yeah, that’s, that’s a fairly common thing. I, there was one consulting call I was on where, uh, this is a long time ago. This is a bare metal days of things, uh, where there was an app server and balanced power mode and turning it into normal power mode instantly just fixed all the SQL Server problems.
So that was a good time. Uh, is there a reliable way to, to, to detect parameter sensitivity automatically? Uh, not built in, you would have to do the automation yourself. Um, I would look in query store. Uh, you can either group stuff up by like a plan ID or query plan hash. Uh, you, I mean like you could try query ID or, or, or query hash, but, uh, if you, if your query is producing multiple plans, then, uh, you know, you’re not really getting like, uh, like a, an accurate picture there. Um, you know, it could like, it might, there might still be parameter sensitivity, but if your query is generating all sorts of other execution plans, then at least sometimes it’s, it’s, it’s just like, it’s recompiling or something.
Right. And you’re getting different plans for, for different things, or even maybe very similar plans for, for different things. Who knows? You might not have a good plan to begin with. Uh, but I would probably want to look at, um, you know, the plan ID or a level or the, the, the query plan hash level. And what I would want to look at from there is if there are really wild swings in, um, in like min and max for like things like CPU and duration, not logical reads. Cause those are, those are a stupid proxy metric and you’re, you’re not a stupid proxy person. Uh, you look directly at the things that matter like CPU and duration. Uh, so you, you, you do that and then you might be able to detect pretty easily if one plan is sometimes running very, very long and one plan is sometimes running very, very quickly.
Um, averages might not be a great thing to look at there cause it would just skew things up for you, right? Or rather it would just not, it might just smooth things out and you wouldn’t see like the, the wild, uh, variations in performance. All right. What’s the most common reason good indexes still don’t get used. It’s always costing. Every time someone asks a question, it’s like, it is always costing.
Uh, SQL Server looked at your index and said, I think you’re too expensive for me. I’m going to use a different one instead. Uh, and then that’s when you have to step in and tell SQL Server you are wrong. Your estimates are incorrect, but you, you know, of course, careful testing there. Um, you know, the thing is I, I don’t love index hints so much. Uh, you know, like you, you name an index and all of a sudden, I don’t know, maybe someone renames an index or does something to the index.
And now you’ve got this query using this index, you know, by hint that it can’t, uh, do any, you can’t do anything useful with anymore. Um, my, my stronger preference is, uh, you know, if you’re, if you, if it, if it gets you the correct plan shape and the correct index usage, there’s something a bit looser, like a force seek hint. Uh, because then, you know, you’re just saying, Hey, SQL Server, you, you, you should force, you should seek really here by promise you.
Uh, and then, um, you know, uh, hopefully it’s still use the index you want, but that, that’s an adventure for another day. All right. Uh, how do you prioritize tuning work when literally not figuratively, literally everything looks slow? Um, well, there are a few, there are a few different ways to think about this, right?
Um, you know, you could start with the things that users complain about the most, um, because once you get the users off your back, you can, uh, certainly make time for whatever pet peeves you, you, you have derived from your, uh, performance analysis. Um, but sometimes it’s not like, sometimes it’s not directly what users are doing.
Sometimes there’s like other orchestrated tasks in the background that are, uh, that are just impacting user workloads. That’s another thing that can happen quite often in SQL Server. Um, and then another way to think about it might be like, uh, if you, if you’re trying to like, you know, just unclog a server, uh, you might look at wait stats and you might look for, um, you know, probably your most prolific wait stats that, uh, that are related to, to query execution.
Uh, and you might try to tune around those. For example, if you have, um, like, so like for the CX waits, SOS scheduler yield generally also has to be very, very up high with those. Because if you just have a lot of parallelism, like, you know, it’s like, okay, there’s a lot of parallelism, but is it causing CPU pressure?
And SOS scheduler yield is a good pair, uh, thing to pair that the CX, CX waits with to make sure that, uh, you actually do have some CPU pressure on there. Uh, another one that I would look at are the page IO latch waits. If you have IO bottlenecks, um, you know, sometimes compressing indexes or adding in better indexes can get you around, uh, some of that stuff.
Um, but you know, uh, like those can also be signs that like the hardware isn’t effective for the workload as well. So for, as far as prioritizing tuning work goes, uh, it really depends, it’s, it’s for me, that’s very situational. Um, and for me, you know, a lot of the time it, it sort of depends on what signals I’m hearing from people, uh, that I’m working with about what, uh, what their priorities are.
And, you know, it’s not like you, it’s not like you always have to agree with them, but, you know, um, you know, like still, of course, do your analysis and, you know, uh, you know, point, point out thing, point out things that they might not have, might not be aware of that they, they might not be able to get to with their knowledge of SQL Server. Uh, but, uh, that, I think taking that signal into account is, is certainly worthwhile. So, um, for me, uh, you know, if like you just stuck me at a server and you said, tune whatever you want, um, I would probably look, I would probably look around wait stats.
If I got like, like high, like lock waits, if I had a lot of those, I’m going to start going after modification queries. Um, if I have, you know, really high SOS scheduler yield waits, whether it is accompanied by CX packet or not, I would go after CPU dominant queries. Uh, if I had high page IO latch waits, I would look for queries that could benefit from indexing or just an environmental change to page compress my indexes.
So that I had a smaller data footprint, both on disk and in memory, and I could fit more of my indexes in memory and, and maybe even clean some indexes up with a wonderful store procedure like SP index cleanup. There are, there are so many ways to approach this. The mind boggles occasionally.
Why does SQL Server sometimes underestimate row counts by orders of magnitude? Uh, well, I mean, you know, you, you’ve got a list of things there that could be, um, you know, statistics being out of date could be one, right? That’s a very easy one.
Um, you know, you could have some sort of, some non-sargable predicate in there. That’s throwing up the, throwing up, throwing, uh, gasoline in the face of the optimizer, trying to make estimates for things. Um, you could be using some other, uh, thing in SQL Server that just generally makes cardinality, cardinality estimation difficult, if not impossible to derive.
Uh, local variables and table variables certainly contribute to that. Local variables because of the density vector estimate and table variables because they do not, they do not carry a statistics histogram on their columns the way regular temp tables do. Uh, that could certainly be it.
Uh, another thing could just be cardinality, cardinality estimation model. Um, if you’re on newer, a newer version of SQL Server and you are using a, uh, newer compatibility level, you’re using the new cardinality estimator, not the default one, and you might end up in a place where you are just kind of stuck, um, getting strange estimates from across, uh, many queries.
So, uh, that’s usually it. Um, you know, there, there could be other odd, odd reasons that you might run into something. But in my experience, those are usually, that’s, that’s where I’d start and that’s where I’d start.
Uh, I think, you know, updating your statistics is a nice thing to do for SQL Server. All right. Anyway, that’s it.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about some T-SQL-y stuff. All right. Thank you for watching. All right.
Thank you for watching.
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.
So that… Welcome to the Bit Obscene radio program. I am joined by two of the final remaining humans on the planet, Joe Obish and Sean Ghilardi. And we are here today to have a roundtable discussions at the AI Complaint Department, because I think despite having at least some moderate success in using my robot, companions to help me build some things, I have many frustrations with the robot companions. And Joe and Sean correspondingly have many, many more complaints in their corporate dwelling with the robots. So, gentlemen, I don’t know who wants to get started here. I refuse to pick. I love you both equally, so I will let you fight amongst yourselves for first dibs. Well, first, I have a point of order.
Ah, there we go. I noticed that all of us have a bit of beard, or I have a bit of gray in our beards, and this is what… A bit? This is what working on SQL Server does to you. For all the young viewers who are still considering their career options, you gotta be okay with gray if you’re gonna pick the SQL Server lifestyle. Yeah, I am fully… I am more pepper than… Well, no, I’m more salt than pepper these days, I think. I’m really… Like, at some lights, it looks like I just have a beard that goes like this, because the gray is just taking over most of my face. It’s a sad state of affairs.
Maybe next time we’ll have some AI overlay, and I’ll uncover the damage. I’ll get some just for men. I’ll tidy myself up a little bit. Yeah, I think, you know, you started, Eric, and said you had some success. I mean, why don’t we start out with kind of some of the positives, right? Because everyone…
No, I was gonna start with the negatives. Oh, well, I mean, look, the positives far under underway, the negatives, so we can start with those. All right, Joe. Let us have it.
Yeah. Well, like, the weirdest thing to me about the proliferation of, like, AI agents, and you’re just doing your normal job and using tokens or whatever, which actually isn’t something that I do. I live a token-free lifestyle, so some would think I’m unqualified for a discussion, but you can’t stop me from talking. Only Eric can. He’s not going to. I do have a mute button, but I am not a censorious person, so I will not.
Because some, like, you think of all, like, the bad stereotypes of companies being, like, penny pinchers. Obviously, it varies a lot, but I mean, I’ve had some experiences where we had some very important server used for development, and it had a 50 gigabyte hard drive that would fill up every day, and that would cause problems for the developers. And IT said, well, why don’t you just redesign the whole process? Oh.
Instead of, like, adding 50 gigabytes of hard drive space, because, you know, the only options are, like, the main development environment goes down daily, or we do a huge development project. And, you know, clearly adding 50 gigs of space. I mean, space is expensive. 50 gigs of it, Joe.
Well, and despite all the gray, like, I’m not, like, I didn’t work in the days where that was actually true. This is, like, 2012. Really, like, space was not expensive. Or I can think of another example where I was going to present at SQL Server or SQL Saturday in New York City, and I asked the company to, like, reimburse me on an airfare, and they said, oh, that was totally impossible. Impossible request. Impossible.
Yeah. $300. Can’t be done. Uh-uh. Well, and, like, I think of all the experience that you have with various companies, you know, getting in and out, and I’m sure you’ve seen a lot of stupid penny-pinching things over the years. I don’t know if you want to share or not.
Well, I mean, as far as penny-pinching things go, mostly it’s people asking me for discounts. And I don’t understand why, given my incredibly reasonable rates. But, you know, you can’t blame them for trying to save a buck. You know, I guess, you know, it looks good, makes you look like a real company hero when you save the company money and all that stuff. Well, I think the thing that I find just really weird about AI use at these companies is that, like, now you’re paying a tax on someone doing their job. It’s like, not only are they doing their job, but now you have to pay for them to use tokens to do their job. And it’s like, that’s just a weird thing to me. It’s just like, are you doing more job? I don’t know.
That’s what I was going to say. Like, you said you can’t blame them, but I can blame them because, you know, you’d say things like, oh, well, Eric’s training. That’s too expensive. That’s not in the budget. But apparently, spending tokens all day, every day suddenly is in the budget. And, like, speaking for myself, like, I didn’t know I had the option to just spend company money all day to have some tool do my job for me. I mean, like, you know, like, like, like, like, like, tomorrow is going to be pretty nice weather. Like, can I just, can I just have like, you fill in for me?
Yeah. We’ll invoice it, right? Like, is it really that different from this token nonsense? So like, like, that’s very, like, that’s the first thing that’s changing to me. Like, like, the very embedded frugalness of so many companies and, like, not willing to invest in their employees for small things like getting a second monitor, productivity software, or training.
All right. Like, like, how many people have you met that really need training who just can’t get it? But, yep, it’s perfect. Everybody.
That’s why my, that’s why all my training is so reasonably priced so that the ordinary work a day individual can can purchase it without straining their lifestyle too poorly. Maybe you need to sell the training to the AI agents directly. And then maybe that way.
Jokes on you. It’s already been. They can, they can, yeah, it’s probably been stolen already, but, you know, everyone can, and that’s been tokens, and then you can get, like, 10% of the token to, you know, tell me what to do. No, but let’s talk about that for a minute, right?
Because, Joe, that’s one of the things that bothers me as well is, you know, oh, we can’t, for example, I would, a long time ago, I was in charge, well, not in charge, but I took it upon myself to kit out everyone with the correct hardware. So this means not giving them pieces of shit, like super ultra light laptops, and then telling them to go look through 100 gigs worth of, you know, logs and figure out what’s wrong and why can they do that in two seconds?
So, you know, I made the hardware accordingly and got yelled at when I said, well, this laptop is $1,800. I’m like, that’s too much. Like, but when you take into account that it’s supposed to last for minimum four years, that if you were to do, you know, a cloud box or something like that, right?
Like, if you had the same specs, as a, like a, like a, you know, every company or every major cloud provider has some type of desktop, you know, remote desktop option. If you would do the same specs in there, you’d be at $2,000 in, I think, four months. It was like $500 a month.
Well, if you left it on all the time, most companies will cheap out and put some, like, shutdown policy on it. Oh, you idled for 30 seconds. Boom. Like, all right.
Yeah. Well, it’s worse than that. But yeah. So, so you do that, right? So you’re talking about something that’s, we’ll round up $2,000 for four years. It’s $500 a year. And we’re coming out and saying that’s too much budget. We can’t do that.
But then as Joe said in the same token, we’re saying, but you can use $500 worth of tokens a month, which by the way, has no idea. It doesn’t remember anything. So when Joe asks it, hey, you know, how do I do X, Y, Z, or can you do this?
And then it does it, assuming it does it right, which we didn’t get into quality yet, assuming it does it right. Joe didn’t learn anything. Joe didn’t necessarily get in better.
No offense, Joe. You do get better. You do learn stuff. But Joe doesn’t need to get better. He’s already the best. Right. But the hypothetical Joe won’t get any better. You know, he’s not learning anything.
So when you talk about it, you’re not even upping your efficiency. You’re going to pay for the same tokens and the same thing. Again, the same exact thing. It just it doesn’t make sense to me where the I’ve given up trying to make sense of corporate spend a long time ago because none of it makes sense unless you factor in kickbacks and other things.
But yeah, so well, I think right now, there’s just so much pressure on every company to talk about how AI driven everything is, which which is just like a it’s like a weird thing. That’s Klarna about that, right? Yeah, it’s like Starbucks.
Yeah. Uber or pick your favorite company because they all come out and said, we’re totally doing the thing. And yeah, and then a year later, we totally failed it.
Yeah, it’s like, man, we spent a lot of money on that thing. And I don’t know. I don’t know if that worked out. Yeah, it’s it’s it’s just I don’t know. It’s it’s just weird.
But, you know, like, like I said earlier on, though, like I have been able to do some stuff with it. Like I wasn’t going to learn how to do in the first place. Like, you know, I put together a SQL Server monitoring tool, a plan analysis tool. And like, I’m not I’m not a C sharp person.
I am not a front end person. And there was no hope of me being able to build those things. But, you know, it started like with like a sort of centralized or like a yeah, I guess I guess like a fairly specialized bit of knowledge around like this thing. And like, like, that’s why like me building those things, I don’t I don’t feel too guilty about it, because it’s not like I’m building a thing that relies on AI to exist, right?
Like, you don’t need AI to do the plan analysis, you don’t need AI to do the monitoring, they’re just building a tool that goes and does that. If the robots go and die tomorrow, I still have this thing that I can do stuff with. So I don’t feel too, too bad about that.
And, you know, like the development process has been like screaming at like an idiotic child for many months now. It’s not been like like a less like, you know, better roses, right? I wake up every day and I’m like, I can’t can’t wait to have the robots do something new for me.
I’m like, oh, what’s going to go wrong this time? It’s like it’s not great. It’s not a happy place. What you’ve said, though, is a great use for it, right?
You’re bringing the specialized knowledge. In fact, this just happened to Ford. So Ford, you know, they did all the stuff and then they realized that they fired everyone who had all the specialized knowledge. They’ve actually saved money by hiring back all the people.
And it’s not by kicking AI out. It’s by saying, look, there’s a lot of things that especially process driven items that we don’t need, right? So if it says, hey, here’s the 10 tickets that had similar issues, I’ve already collated them for you.
You can now go take a look. And I put all your data over here and I did all this stuff over here. That’s what it should be.
So you, Eric, you know, you have the knowledge, Joe. You have the knowledge, right? So it should be bring the stuff to you. Get some of that mundane crap out of there. Just like you were saying, it built the front end. It did the stuff.
I’ve done some stuff recently where, you know, I’m not a big, like you, I’m not a big front end UI person. I’m a back end person. You know, jokes accordingly. You are a back end person.
And so, you know, it’s like, ah, I really want to show this. I have this great data structure. I’ve gathered all this information. I really want to have a nice way of showing this. And then the engineer in me goes, great.
Where’s the closest spreadsheet that I can put this up to, right? You know, where’s the perfect grid view? Like, but people don’t like that. So, yeah, I’ve used it for front end stuff too. But as you said, you’re not asking it to be the crux of the knowledge.
You’re the crux of the knowledge. And it’s enabling you to do that. It just builds a fort around that. That’s great.
Yeah. That’s great. But Joe, I think, had some different encounters. No, like, that strikes me as one of the few good use cases. Because Eric and I were talking about doing some open source stuff like a couple of years ago.
Yeah. And the thing that stalled us was, well, someone’s got to do all the front end stuff, right? Like, all the back end stuff is fine. All the expert knowledge is fine.
But, like, you know, and Eric does a lot for the community. He’s great. He’s a generous guy. But, like, I don’t know, you know, like, paying five or six figures to, like, have someone do hundreds of hours of development for some tool.
Like, it just wasn’t going to happen. And, you know, you try and do it yourself. Like, I remembered asking on Stack Overflow, like, what’s the secret to try to, like, get an SMS plug-in to interact with query plans.
And then, like, heroic Martin Smith, like, a year later, like, wrote some great, awesome answer, finally. And then Eric was able to use that.
But, you know, like, that would have been our journey trying to do it on our own. Like, it just wouldn’t have happened. Nightmare.
And, like, you know, it’s not like we’re not friends with developers. It’s just finding people who are equally sort of cool with the idea of donating a lot of their time to an open source project.
Most people are just like, no. Like, let me do the math on that. Zero, zero, carry the zero. No, it’s a lot of zeros in it.
One might say. Nope. One might. Yeah, it’s interesting. What I will say, though, is on the, I guess, the antithesis of that, right, on the other side is the people who, you know, if we thought the Dunning-Kruger effect was bad before, oh, shit.
There’s some real, well, I asked AI to fix the problem. And here we go. And you look at the PR and you just want to, hmm.
Yeah. It’s, yeah, there’s a lot of stuff, you know, because I do try to, I try to keep using it because I want to know when it actually gets good.
And, like, I, you know, like, for me, especially with, like, query tuning stuff, like, like, the stuff that it says about it is, like, just, like, absolutely miserable. Like, like, like, like, like, foundationally just incorrect things about everything along the way.
You’re just like, no, just stop doing that part. Just do the parts that I tell you to do. But, like, the things that I, the thing that I do like about it quite a bit is, and it’s one thing that I, that I often kind of struggle with a little bit, is the sort of, like, logically equivalent query form thing.
And it’s very good at looking at a query and me saying, like, there’s got to be, like, a different form of this query that would be logically equivalent. And it can, like, give me a bunch of those.
And they’re, they’re usually about right, like, with the logical equivalents. But then, like, you know, like, anything that it says about, like, the actual, like, process or of query tuning or, like, like, looking at a query plan or, like, wait stats or anything, I’m just like, no, no, you just put, put that down.
Like, you, you’re going to, you’re going to hurt yourself, people around you. To quote a famous, to quote a famous author speaking about the, the newspaper. Often the article is so wrong, it actually presents the story backward, reversing cause and effect.
I call these the, quote, what streets cause rain stories from papers full of them. And if I had to summarize the, like, total available knowledge on the internet about query tuning, I think that description works pretty well.
And that’s, it’s really, like, all the AI can, can do, right? Like, like, it’s, it’s not gonna know that some people like Eric actually understand it could cause an effect.
And then, like, the 90% of others are just, like, randomly guessing or, like, there’s, like, they just don’t know that rain actually makes the street wet and it’s not the other way around.
The streets are not out summoning rain gods to pour down upon them. Never know. And it’s the same thing with AI generated T-SQL code. But at least for me, like, maybe my style is kind of peculiar, but, like, the stuff it generates, it’s certainly, like, I mean, like, I would never expect to, like, open, like, a random blog post and find T-SQL code that’s good.
I don’t even mean in terms of formatting, but, but just, like, the way that the code is organized, like, not copying and pasting the same stuff everywhere. There are, like, passing data between procedures correctly, working with temp tables correctly.
I mean, like, there’s just so much stuff and. No, I mean, when you, like, AI, to me, with a lot of stuff. So, like, the, like, the two, like, it’s, like, a very generalized experience, but then some very specific experiences are, like, the generalized one is that AI has been trained on a lot of, like, just bum-ass SQL on GitHub or wherever it’s, like, picking stuff up from.
And it has no problem just, like, repeating a lot of that stuff. But that, that to me is, like, an almost perfect corollary to, like, when you see developers and now AI working with, like, very specific, like, like repos that are, like, you know, code bases that they have to deal with because, like, like, you know, I, I’ve been saying for years, uh, code is culture and, like, you know, if you have any bad code in your environment, it quickly becomes, like, the standard for everyone else to follow.
So, people will copy and paste patterns out of, like, store procedures or other queries into their queries because that’s the way it’s done. And if they don’t do it that way, it might be wrong and something might get screwed up and they’re all just terrified of, like, you know, trying something different.
But, but AI has almost the same thing where, like, like, like, if, if, if it’s, like, trained on a code base and that code base is full of real crappy queries, uh, it’s just going to keep repeating the same things over and over again.
And it just, like, things just don’t really improve with it. And I think they actually tend to get worse because at some point they even, like, uh, uh, like, contextually lose whatever standards might have existed before.
Well, it can, it gets even worse too, as you were saying, depending on what the code base is from. I’ve worked on code bases that are very old.
You know, some of the code is from the 80s, 90s, to be clear when people watch this. The 90s. We, we, we know you’re talking about SQL Server, Sean. It’s fine.
And some of it is very new. I mean, some of it’s, like, very, very new. Brand, brand new repositories. And the stuff that’s old, it definitely does not like it, does not do well enough. Because, as you were saying, that’s not what the mass of the training data is.
So when 60 million people have forked the same project on GitHub, and they all have the same queries in it, and the same code in it, it’s no wonder that that’s what you get. Because it’s, like, I know people are going to throw hate, but it, you’re just generating the next character, and the next character.
What, you know, what’s the statistical chance of that being there, and it’s a little bit of stuff in. So, yeah, it makes sense. If that’s what 99% of your training data says, that’s what’s going to come next.
And when you don’t have it, you know, you still, that’s where your kind of hallucinations come in. I don’t, I don’t like that.
I don’t think you should anthropomorphize the thing. But you get really bad items, and then you get the sycophantic behavior. Oh, no, you are right.
No, see, your idea was the best, but you are the great. Oh, and so going from those old code bases to the new ones, new ones, it does epically, epically better, especially if it’s smaller.
The context window issue is huge. And I just, I don’t know, right now I see it as good still for small things, but it reminds me of quantum computing or fusion energy, right?
We’re always, we’re always just, like in quantum computing, we’re always 10 years away. In 1990, we were 10 years away from cracking every password, right? Encryption’s not going to be a thing.
The whole internet’s going to be a thing. We’re still just 10 years away from that. Same thing with fusion. Oh, we’re getting close. You know, we’re 10 years from having a viable small reactor. Okay, well, we’re still 10 years away.
That’s just kind of how I look at it, right? Oh, AGI, we’re, it’s going to be 2024. No, sorry, 2025. I mean, six. No, wait, I mean seven.
No, now 2028. Well, it’s more like 2030. Yeah, I think at this point, we might need fusion to get actual AI because there’s, I don’t know if, I don’t know how we’re going to generate enough electricity to keep that boat going.
It’s kind of wild. It’s similar. I was told at Oracle Open World in 2017 that the DVA job wasn’t going to exist in a year. In a year.
That’s been in Microsoft Docs since 2005. I was working at a company and we had a, back when they were called TANs, technical account managers, back when they actually were sort of technical, not super, but sort of.
And I will never remember, or I will never forget, I will always remember that she came in and said, this was 2010. And she said, did you hear about Microsoft Azure?
And I said, yeah. Oh, well, you’re not going to have a job in three years. Everything’s going to be in the cloud in three years.
Everything’s going to be in Azure. This is 2010. And I’m looking at it. And it’s, what is it, 2026 now? It’s hard to keep track of things.
But so we’re 16 years in the future. You have a lot of places pulling out of the cloud. You’ve got some places moving into it, right? It’s just a constant, constant feel there.
And it’s just like, this is, it’s the same thing, right? It’s the hype cycles for me. If someone comes out with a screwdriver and they’re like, oh my God, it’s a screwdriver.
It’s so amazing. Look how good it screws these screws, right? Awesome. First, if I have screws, I want that screwdriver. But we need to stop with, a screwdriver can also make you breakfast in bed.
Oh, really? Well, yeah, because you’re going to use the screws to put together the robot. Oh, I thought you meant like a drink. I was like, Sean, that is breakfast in bed.
I don’t know why. I should have. I chose my items well. I knew my audience. I was like, wait a minute. Yeah, it’s the right tool for the job. I think that’s what I’ve always come back to, spoken on.
Yeah, I mean, there are certainly neat things about it. Like, you know, I mean, I understand that like context windows are sort of like the AI equivalents of humans getting tired and just being like, what was I doing?
Like, there’s a missing parentheses where, but like, it is cool that like, you know, you have these things that can sort of like autonomously just like, like, like pick at and iterate on a task that would not be fun for you, right?
Like, just stuff that you just absolutely don’t want to do. Like, it’s cool to have these like sort of like robo servants that like, you’d be like, like, I don’t know what I’m doing with this. Go mess with it for a while.
Like, tell me what you come back with. Like, I don’t know what to expect, but you know, you can go do stuff. So like there are neat aspects to it, but I don’t know. Pete, I feel like, and this is going to sound weird to say, I feel like people are offloading the wrong parts of their life to it.
Um, like, uh, you know, I feel like people are offloading the stuff that they enjoy doing to it and, and, and doing less of that, uh, and like being less involved with that and using their brains less with that. And, uh, they are not using it to do the stuff that you’re like, man, I, I have, I have no interest in doing that. Uh, so like that, but that’s the pattern that I see a lot of people are like, oh, like, I don’t like, I can use it to do this part of my job.
I’m like, isn’t that the part you like? And they’re like, yeah, but this is great. I can just blah, blah, blah, blah. I can do so much more of it.
I’m like, but you’re not doing any of it. I don’t know. It’s. Well, the. It’s been shocking to me, like how quick and eager people are to just like mortgage away their job. And so like, for example, like, you know, take someone like Sean, obviously, obviously a very, very top man.
His, his time is very valuable. So for someone like Sean, like, let’s say he has to make some PowerPoint deck for some internal meeting and he has to do it, but it’s really not that important. You know, it’s not like the, the success or failure of the product is going to depend on this PowerPoint.
Right. The honor of the country depends. I hope not. So, so like, like if Sean uses AI to do 90% of the PowerPoint and he fills in the main 10% himself, and then he goes and tries to restore temp DB for some very important client. So, you know, like, like Sean can use his time, like more of like, that feels like a good use for AI.
Where, and like, some of the things that suits is like, like the core thing you do, the thing, the thing that the company pays you to do, the thing that you, that you interviewed for, the thing that you studied for, the thing that, that you got certified for. You, you’re just giving up your agency and telling the AI to think for you and do it for you. Like that part is just unbelievable to me.
I want to bring up two things first. All right. I’m counting. I’m pretty sure Joe has been watching me my life because both of those things that he just said have actually happened. Number one is I did recently use AI to make a PowerPoint.
It was, the content was horrible. The formatting, the background choices, the little bubbles, way better than anything I could have done. I’m not artistic at all.
I failed what, whatever art gene there is. Not only did I lose it, but I lost whatever was next to it. Yeah. I mean, you’re talking right now, you had that plain green background, man. See, that’s what I think.
So number one is, yes, I did use it. It was more like the formatting was great. The background was great. And all the content I had to absolutely, because it was horrific. So I actually did about 80% of the work, but the 20% that I would have had to do with the formatting, make it look nice and pretty for people who don’t understand technology.
That was actually the hardest part for me. Yeah. So it did help.
You would have given up. Exactly. And number two is, I actually did, I actually did, when I worked in CSS, when I worked in support, had a ticket that I worked on. And the complaint was that the consultant wanted them to back up and restore TempDB, and they were getting an error, and they think it’s a product bug.
The consultant told them it was a product bug, backup and restore TempDB. So they were opening a case on behalf of the consultant so that we could fix the problem. Well, and the thing is, if you ask the AI, like, should the customer be able to restore TempDB?
I’m sure you could make it say yes pretty easily, right? Yeah, because, like, I mean, you know, so, like, actually, it’s been a while since I really tried to get it to do something stupid, because I’ve been trying to get it to be smart with me. But I remember very early on, like, just asking it basic questions about SQL Server.
Like, it was fully made up backup and restore commands to, like, restore tables and, like, do other weird stuff. You know, it was just, like, I don’t know how, like, I don’t know how much of that stuff has sort of been, like, whittled out of the low-hanging fruit pile of just, like, idiot AI. Like, oh, yeah, of course you can restore a table in SQL Server.
What mature database product wouldn’t have such a basic feature in it? You know, like, ah, I got news for you. But, yeah, like, I mean, at least for a while, relatively famous for that stuff. But I’ll admit, I got tricked by Google, where I inquired if SQL Server had the ability to add a column and add a low priority.
And it said yes, and I thought, finally, those clowns of Microsoft are finally adding the good features we need. And funnily enough, Sean, you were talking about how you can use that, too. But it turned out that the AI just hallucinated the whole thing, and it just didn’t exist.
It might exist in some RDBMS code that it ingested somewhere. Maybe that’s DuckDB or CockroachDB or MemDB. They don’t have a weight at low priority.
Nothing. Top priority or butts. That column’s there or it isn’t. That’s it.
Schrodinger’s column. No, actually, the only database that’s weird with that is that I’ve come across. I’m not going to say the only. But the only one that I’ve come across that’s weird with that is CockroachDB, where what they do is they, like, they, like, add it sort of, like, in the background. And they have all these different workers.
Like, because CockroachDB is sharded, right? So, like, your table lives in, like, 50 bazillion places. Yeah, so, like, you have one table, but it’s not one table. It’s, like, all KV paired across, like, the nodes and stuff.
And they’ll send up workers to, like, add the column and backfill it and do all this other fancy stuff so it’s, like, all fully online. But I won’t get into it with my local testing rig, but there were some interesting side effects there. But anyway, that’s the only one I know that feels like it has a weight at low priority at a column.
But, you know, I do want to point out that I find myself saying this constantly. And I get that I’m now the old person yelling at me to get off their lawn. But I was, it was brought up at one point that there’s now SpecKit, which is specification-driven.
Because, you know, if you just tell, if you just give a generic prompt of, hey, go fix this problem, your results are going to definitely vary. And there’s things that won’t be taken into account in this manner. And I saw that message, and I wanted to reply, but I didn’t, which was, so writing specs are a thing again?
You come full circle. Like, interesting. We went from, we should write an actual good spec so that it can be implemented correctly, and we know what’s going to, you know, everything ahead of time, and we’ve gotten customer input, we’ve done all these things, that’s the spec.
So now, we’re going to write a spec. This is a new novel idea, and we should totally do it. Yeah, but people are just going to use AI to write the spec.
It’s, yeah. No, no, I’m going to tell you exactly where that comes from. I’ve been to a lot of conferences this year, and I’ve seen a lot of talks on AI, and some of the ones are, like, where, like, they’re talking about, like, how to use AI, blah, blah, blah, blah. And they’re like, you have to write a really good prompt.
One way to write a really good prompt is to ask AI what a really good prompt would be. So you’re just, like, the whole advice is, like, use AI to build the prompt that you pass to AI so that it starts with a good prompt. And I’m like, so we’re just out of everything.
We’re just hands off the whole deal. I mean, that’s a great prompt. What did they miss? I don’t know. I would be happy with the Star Trek future of nobody has to work. Yeah.
Everyone just has everything. Nobody wants for anything. Everything’s great. If you want to go do something because you’re interested in it or whatever, right? Great. I, for one, I look forward to that.
You’ll find me on, I’ll still do some type of farming somewhere on some amount of land, whatever. But then I can come home and replicate a steak and potatoes or something. Ah.
Although if it tastes like store-bought. Sean is desperate to stop the SQL Server work to get to his true passion of farming. I think that’s true. I think it’s very clear.
True. Should have taken that retirement package, man. I didn’t hit 71. Oh. I guess I’m not that old that I can yell at people. But no, I’m all for that future.
I just don’t see that in the next 10 years, right? Maybe in 60 years, 50 years, conservatively. Maybe.
I don’t know. And that would be great. I mean, I’m happy to, but it’s always, there’s always something, right? You were saying, you know, you can use the AI to feed into the AI to do the AI. Even when you go, I think it was, I forget who the auto workers, they just got all those like multipurpose robots.
So not just the generic ones, but the multipurpose ones and the unions are all up in arms. Like, but someone’s still going to have to fix the robots, right? Unless you’re getting to a point where the robots fix the robots, but we’re robot doctors.
You know, it was the same thing with the, with any tech, any large technology jump, right? It doesn’t. Yes.
Some things are eliminated, but some things are. Yeah. Like, like having calculators didn’t destroy mathematicians. Well, that’s what I was saying earlier. So yes. It just made math a little bit easier for idiots who can’t do math.
Right. You had the slide rule and there were great with it, but the calculator made the slide more or less. I’m pretty sure that like spreadsheets got rid of tons of jobs.
Right. The amount of people who like to flex their Excel skills. I don’t know.
Maybe. I’m really cracking down on the world’s first database, Joe. That’s rude. Did you know there’s an actual Excel competition? Yeah. Worldwide Excel competition. Yeah.
I heard about that. Maybe you don’t want to tell me about it. There was a guy who made a whole like RPG game in Excel where you could like go through different things. Yeah. People do crazy stuff with Excel. It was amazing.
But yeah. So the whole like calculator thing, plus the calculator didn’t do it for you and it didn’t lie to you. You still had to put in the data. Right.
Yeah. Now you’re saying what’s two plus two and it comes on. It’s like it’s totally seven. You’re like, oh, okay. Well, it told me it’s 17. I mean, I guess I’ll just go ahead and copy pasta that the spec thing you brought up was interesting to me because it feels like these are pretty old lessons that were known by some people, right?
Like you’re trying to get your brand new hire out of college to do something good. Oh, we have to give them a very detailed spec or you have some offshore developer. Oh, you need to give them a detailed spec or you have a consultant and so on. And it was easy.
Well, you know, like it’s not possible for a client to give you a spec for a complicated thing and the spec is 100% perfect and you never have to ask follow up questions. Right.
Yeah. Like I feel like this has been known, but so I don’t know if like if the barrier to entry is gone because right, because like, you know, there’s that idea of like you to have a business person and they claim to have like a really good idea for an app and all they need is for someone to code their really good idea for free, right?
That’s all they need. Then they’ll be the best app of all time. So you used to have that like barrier where they’d get frozen out if they couldn’t find a sucker to work for free, right?
And now these people have infiltrated the industry. They’re walking among us everywhere, right? Like.
I don’t have a problem with that. Like you said, I don’t, I don’t like barriers to entry. I think everyone should have the ability to enter, right? But you’re not going to be able to continue after that. I mean, that’s on you.
Look how many, I used to work for a hospital system. I won’t name those. And the entire patient care was done by a visual basic for application that ran on a 1990s computer that had a sticker on the monitor that said, do not turn off.
Well, I hope you didn’t turn it off. I mean, no, but I’m just saying like that is, it had the windows 95 logo burned into the monitor.
Nice. And, and this is in the late 2000s, right? I had worse things burned into my monitors. They probably didn’t have the budget to buy a monitor, you know? My point is if someone had an idea or said, Hey, I could have done better.
I could have done that. I’m all for it. But when, why I use that example is, and this is going to come up to a question that I actually had for you, Joe, later.
So if you remove the barrier of entry, you’re still going to have crap come through. The thing is, are you able to keep that crap running? Will it go anywhere?
So ideas, I heard someone talking a couple of weeks ago and it was essentially about, um, they were, they had ideas for the stories and they just, they weren’t a great writer and they needed help writing and that’s when I, you know, I started kind of eavesdropping and listening.
It was interesting how the conversation went and everyone has an idea. There’s no lack of it. And if you take the barrier to entry away, then there’ll be no lack of people being able to get in and do it.
And you can actually then have winners. I just don’t like, I personally just don’t like, so I do see it from a gatekeeping point of view as a good thing.
You know, we were just talking at the beginning that there’s things, you know, especially UI base that I just, I can’t stand to do. I don’t like, not great at it. I’m not artistic. You know, it was like, I don’t know.
Like, I think like one thing that used to strike me and that I used to be kind of jealous of is, uh, well, I mean, there’s actually two things. Uh, one is, uh, like a lot of the consulting clients that I’ve worked with over the years, um, have had the, uh, dual luxury and problem of like the person who founded the company also being the person who built the software originally.
Um, and it’s like, like, like they know, like, you know, they, like, they started it and it got good enough that they realized they weren’t good enough to handle it themselves. They started hiring people and, you know, like, like the two things that happen, of course, it’s like, you know, you have like the technical founder who’s like now, like at least for like some period of time up everyone’s butt about the code, but you have someone around who like knows where all the treasure is buried.
Like they, like it’s their code. Um, like maybe they have an ego about it. Maybe they don’t. Um, but they start hiring people who start working on it and they, they, they hand it off and like that, that’s cool.
Not everyone has that, the technical founder ability. You might have someone who has a great idea, just like, you know, they don’t have a way to like get it off the ground. So make, maybe, you know, like maybe we’ll see more of that, um, in the, in the next few years, uh, where, you know, non-technical people who have like a, who do have a great idea can get something to a point where maybe it starts making money and they can start hiring people to like take care of it and, you know, actually build it into a cool thing.
Cause like, like you’re just not going to have someone who can like, you know, just continuously sit there and, you know, let’s just say run a company while they also, uh, you know, have like, just like robots working on the software 24 seven, like eventually you, like you get like, trust me from, from building the monitoring tool and stuff that gets real old, real quick.
Like, I don’t want to say like, if I, if I had someone else who could just sit there and monkey with prompts for, and like build stuff, I’d be much happier, but then I’d have to pay a person in that, that, uh, I tell you, there’s no money in open source software. Um, and then, uh, I forgot the other thing I was going to say, but that was probably good enough anyway.
Well, my question to Joe and you to a, an extent to Eric, because you do go in a lot of places, but Joe, I know you’ve seen some AI code come around and I’ve seen some AI code Eric obviously has.
So I I’ve seen it and it does remind me of kind of back in the day, you would have these generally pretty smart people and they would write code that is not readable or vegetable, but they thought, and I’ve seen it in T-SQL too, right?
Oh, that’s a really interesting trick. Nobody can read it and understand it, but that’s awesome. Right. That’s where I I’ve seen a lot of generated code.
Sure. They’ll put in random comments of, well, this does a sort by whatever. And then it’s, it’s all some very, you look at it and it’s not just looking at it. You go, I don’t know if that’s going to do what it says it does.
So that gets me to the question of given this, are we going to see five, 10 years of cleanup or any type of, you know, we complain about technical debt. Well, yeah, I can help with technical debt.
Are we going to see a new type of technical debt come up, but it not be technical. It’d be, we can’t find people now who can actually understand what the code is doing because the code is so convoluted because it doesn’t, I had some generated the other day, just, I was just trying to be weird and see what I could get it to do.
It wasn’t actually solving or fixing anything. I was just interested and I will say that it generates some code where the variables looked like I was getting it from, uh, Ada or something like that.
Like I disassembled some source code and it gave me the assembly back out. And the, the variables were X, Y, Z, A, B, C, D, X, X, X, Y, X, Z. So, I mean, I wanted to open up that to you guys, you know, do you see that in the TC board?
Do you see that in the code you’re getting and do you think that’ll be, you know, an issue that, like I said, the stuff is just changing now. You might not be going in and, in solving the same problem, but you’re actually having to go and fix the AI generated stuff.
I’ll let Joe start with that one. Before I start that one. Um, well, when you talked about lowering barriers to entry, my issue is bad ideas have a cost.
They have like all kinds of costs. And I remember seeing a quote, I thought it was pretty good. It was actually from someone in charge of one of these AI companies.
And I went slipping on the lines of most of your ideas are bad. It’s good for there to be like some type of costs with respect to like presenting your idea. And if AI lowers all those costs to zero, you’re going to have like a, well, this is like, I’m not quoting anymore.
But if AI lowers all those costs to zero, then you’re going to have like a flood of bad ideas. Right. Like, like, like imagine getting RFCs all day written by AI and like, well, like, yeah, you don’t have to imagine, but you know, you get like, you get like five paragraphs as to why letting customers restore attempt to be from backup would be like the best idea ever.
And you have to spend your time, like refuting that. Like, it’s probably not a single sentence, unfortunately. I don’t know.
Like, I mean, maybe it is, but it probably isn’t like, uh, speaking for myself, I had a AI thing got sent my way. It was like four pages and it took me like, like a literal three pages to explain like all the problems with it and why we shouldn’t do it and why it was a terrible idea and so on.
And like, you know, like it was, it was like hours of work to refute something that was probably created in like 10 seconds. Yeah. Um, so like, that’s the thing that really gets me. Um, even in the pre-AI days when I, when I’d be in meetings with people, back when I had those and people would like confidently say things that were wrong about like, about like SQL Server, for example.
Um, I didn’t like working with those people, especially cause they weren’t really like that, that teachable either. Um, so with that out of the way, with respect to your question, I mean, if there’s no money in open source and there’s no money in like cleaning up technical debt, right.
Uh, I thank God don’t have any experience with the companies that are doing like tens of thousands of committed lines of code per day. Cause apparently that that’s something that that’s out there now.
Like I, I can’t imagine what it would take to have a human work on those repositories again. Um, I thought you had something I could come across your desk recently. Well, it’s, it’s not the kind of company where it’s, we’re committing like 10,000 lines of code a day.
Like, like, like the, like the amount of work is still within like what humans can do. So it, uh, is reviewable for now. Um, yeah, like there’s definitely weird stuff and I try to clean it up as it comes in.
Like there’s recently a, a table valued function with this was an inline one that, uh, had a comment at the top that in order to get like bulk loading or like bulk operations that it was important to have a table valued parameter as one of the input parameters for the, uh, TVF and it was of course like not, and it was like totally useless.
And I ended up like rewriting a thing and just have like normal parameters, but you know, like that’s the kind of, well, and again, like the, I mean, that isn’t even like that bad of, uh, of a thing.
Like it was a new function. It was only used in, uh, in, uh, two different places. I, I caught it right away. Like that’s not even like a real problem compared to, you know, just like, just like doing the wrong project or having a startup and UI get hacked and all your data gets leaked or, you know, like you assume that, that the SQL Server is a real database and it does fast IO.
Yeah. Yeah. Right. You assume SQL Server has fast IO and you build your application under that assumption, but then you find out the truth and then now you’re totally screwed. Cause like one of your core assumptions is wrong.
Like, you know, uh, the fast IO is an actual real thing. Yeah. I know it is. And I know SQL Server doesn’t have it. It’s no, it’s an, if you do a device, if you do some type of, uh, there’s a fast.
Ah, well, why are you using it? Cause you know, the, if you just take a minute to slow down, smell the roses, you’ll find that it’s a better quality item.
Okay. That explains it. What about you, Eric? Um, you know, uh, for me, so like, actually, let me, I’m going back a little bit to, uh, like not, not code, but just like people like putting together very long documents of stuff is very demonstrably wrong.
Uh, you know, like a couple months ago and I realized this is all stuff that can eventually be corrected. And I’m so like, I’m not saying like, this is just how things are forever. Like it’s stupid, but like, I got a 17 page document from the VP of this company talking about how they couldn’t turn on change data capture because they’re using accelerated database recovery.
It was just impossible. It can’t be done like all this other stuff. And I’m, I’m reading through it and like, like the entire document is based on this and like, like, like went through the whole thing, all the reasoning behind it, like, like just like laid it all out 17 pages long.
And he’s like, we need to have a call about this. So get on the call. And I was just like, all right, first things first, the document is wrong. You, you can use it.
Uh, there is, there is an interaction between them around aggressive long drugation. And so like, like there is like a slight incompatibility, but it’s not a wholesale incompatibility. You can, you can still turn them both on. And like in SQL Server 22, I think that even gets it like, like some of that is alleviated.
And it was just like, oh, and I was like, yeah. And I was like, it was like 17 pages on that, man. That’s a real waste of time. It’s just like, that’s like dumb.
And then like, uh, just actually just yesterday, um, I was like, uh, I wanted to, like, I, I, I had to tune a query where there was like a whole, like a where clause where someone um, it was, it was like, it’s not kind of absurd-y where, but it was like the where clause was like, where is no column, some replacement is not equal to is no column, some other replacement, but over like 40 columns.
And like, I, I love the Paul White trick of like, and like exists, like select, uh, except select, which like gets you around all that. But then I was like, oh, like a SQL Server 22, 2022 has like the distinct from clause in it.
And I was like, Hey, can you give me a version of that using distinct from just so I can AB test them? Cause like, like just rewriting that massive block of code is something that is great for the robots, right? Like, Hey, just mechanically do this thing.
Like just change this to, to this, like how hard could it be? Right? Like something, all that typing I don’t want to do, like, like control F is no parenthesis, uh, comma, other thing.
Uh, I don’t want to do that. So, and the, but, and then like the first thing was just like, Hey, like, like I can write that for you, but SQL Server doesn’t have to have distinct from in it. And I’m like, uh, buddy, like SQL Server 20, 2022, that’s, it’s like four years ago.
Now they’re like, it’s in there. And it’s just like, Oh, my bad. I’m like, all right, just do it then. So like, it’s, it’s still like, I don’t know.
There’s still, you still have those rough, like these really rough edges. And, you know, like, I know I talked earlier about how it just spews absolute nonsense about query and performance tuning and stuff like that. Like, like, it’s just like flat out dumb about so many of those things.
Um, that, you know, like for, for me, like, like you really, like, it really does take a domain expert to point it in the right direction and get it to do the right thing for a lot of stuff.
Um, I’m sure that a lot of the code that I’ve had to produce for the monitoring tool is not what an actual developer would do in a lot of these cases for like for, for, for a lot of it, but I don’t have the domain knowledge to say, Hey, we should have coded it this way.
Instead, it would be better for X, Y, and Z reasons. But when I, when I pointed at like SQL Server stuff and I’m like, let’s party, let’s, let’s have it, let’s, let’s have the talk.
Like, you know, like I, I, I feel like, um, like I’m qualified to like, you know, get it to do the right thing because I know what, I know what right looks like, but if you don’t know what right looks like and you don’t, you don’t know anything about it, you have no sort of foundations in something, uh, you’re gonna have a real hard time with it.
Like, like, like, like you, like you, you might get it to eventually spit something out that’s like functional, but, uh, it’s going to be, it’s going to be a rough road for you. Yeah.
I wanted to also touch on something that Joe said, which was about, uh, security and stuff. And there’s obviously that’s a, a big point of contention for a lot of places, right? Because not only did you then have, like you were saying before, uh, some of these places have where they have the technical founder, where the person was, they were the founder, they, they did everything that was technical and now you’re worried about maybe security that they might not have as much in or this or that, or you have the people who are now saying, I’m going to take these models against whatever.
And I’m going to have this model decompile it on this model, try to find whatever issues. And I’ve seen some of the reports that come out of that. And I know this isn’t new, but I did want to hit it because it is such a big thing.
I’ve seen some reports come out of, out of that, uh, specifically for database stuff. And it’s, it’s, some of them are interesting, uh, but I would say most of them are hot garbage. Yeah, most of them are, if you have sysadmin on the box, you, you took the words out of my mouth.
The, when I look at the repro section and the repro says you’re a local administrator on the box. And I mean, in, in the OS, right, pick your OS, you can, you can attach a debugger. Like, yes, yes, you can.
Anything you say after you, you said you have sysadmin, I am not listening. If you start with U of sysadmin, it’s, it’s, it’s, it’s getting to a lot of slop. And so the thing that Joe was saying about it’s right now, he can work on a lot of it.
It’s coming in manageable sizes. I would say any place that is large enough that they’re getting that kind of stuff. It’s not niche at all, just you’re in your, as you stated earlier, you’re taking out the good parts, right?
You want to say, Oh, I want to go write that code or I want to look at that. Or that’s an interesting problem. And now you’re going to, I guess I’m just Joe, you know, I’m guessing, I’m just going to look over this PR and rewrite it and then approve it.
I guess that’s my job now. Uh, I don’t actually work on databases. I not actually a DBA. I’m just a PR button presser.
I seems wrong. It’s a little depressing. You think of it that way. Well, that’s what I mean. It seems wrong because you’re not using Joe’s expertise.
Right. Correct. Area. So yeah, that, that’s getting into the other stuff. Um, in a comment you said, uh, just a minute ago, yes, it might not have generated the most beautiful or the correct code or anything like that.
Um, but how many applications do we see on a day in and day out where you do then get to see the coding? Oh my God.
How did this thing ever work? How do you guys make money on this? Yeah. I actually, that, that, that does actually jog my memory on something else that like for years, like in, in the consulting work, I would see like the worst applications built around databases.
I mean, like, no, I don’t know about the application code. I know, uh, nothing about that, but I would just see like the store procedures and the queries and like the table design and everything. And I would just be like, oh, you’re a mess, sweetie. Like what, what happened to you?
Uh, and, and for years I was just like, you know, if, if like, let’s just start companies that make better versions of this software, like, and like, like data, like I know so much about databases.
If I started this from the ground up, I would not screw this stuff up the way they have. And like, like now it’s just like, I could do that. But I like, I’m like, where’s their money in that now? Cause everyone can do that.
Like, ah, I happen to be clicking around in the Azure portal. Oh, well, yesterday. And like, there, I noticed there was like, what are you being punished for? There was like all this red everywhere.
Like, you know, like red, the bad color for like security recommendations. And I was curious and I clicked on one, this was just SQL Server on a VM. And, and one of the ones that stood out to me is CLR was enabled and, and, uh, that was bad.
And the recommendation was to turn CLR off because if you don’t turn CLR off, then someone might create a CLR assembly that like does bad things. And, you know, that, that, that, that was a dark red security recommendation.
Not untrue, Joe, not untrue. Well, like, you know, like there’s some guy at Microsoft who has a lot more gray in his beard than Sean and, you know, CLR and SQL service, probably his, his like a magnum opus is his big project.
He left his mark on the product and now there’s some shitty, probably AI generated slop security thing telling everyone, well, Hey, you know, like you should just turn this off. Cause it’s a security thing.
Um, it’s funny that you say that though, uh, the, some of the security items that come up, I had, I was talking with someone recently about specifically securing some database stuff. And one of the recommendations they had was that triggers are, this also came from an AI thing that was given to them, uh, that there should be a way to turn off all triggers everywhere in the database, because having the ability to create triggers, someone could create a trigger and get a sysadmin to run it and it add, you know, a new login and something.
And I, part of me wanted to just yell. And part of me is, you know, this is where we’re at where, well, but, but you see, I could exploit it.
Yes. You could. There’s a reason why there’s security in general. And you don’t just give everyone sysadmin. I, it’s, it’s, and it, and some of these places are not small. Some of these places are, I’m sure you’ve seen it too, Eric, very large where you think this person’s making multi hundreds of thousands a year.
And this is what you come up with. Yep. What the. Yeah. It’s, it’s, it’s interesting. Uh, it’s, it’s like, like, like the last thing we needed was a way for stupid people to feel smarter and we got it.
Like, we just got, got so much of it and it’s, it’s, it’s, it’s rough sometimes like seeing stuff out there. Uh, I, and I, I, I, so actually this, this is more, more of a generic question for the two of you.
Uh, uh, is AI a bubble and what form will this bubble take? Like what, what will, what will its burst look like? Cause it’s like, like, like a lot, like a lot of this stuff is, is it can’t, it can’t go on forever, ever because, uh, there, there’s a, I don’t want to say it’s Ponzi ish, but there’s certainly a lot of like circular monetary, uh, patterns forming.
And, uh, a lot of the, like a lot, all this token stuff is very, very highly subsidized. Like, like when I use Claude, I can, I can hit usage and I can see how much my session would really cost.
Like if I didn’t have the max plan and it’s like 3,200 bucks and I’m like, okay. All right. That’s a little token inflation there, but all right. Uh, like that’s interesting.
So where, where does, where does it all end? Where, where does, where, where will this naturally lead us to? Well, maybe it’ll stop when someone gets sued and I’m kind of shocked that the lawyers aren’t helping us here.
Cause like, well, like I’ve never had, uh, work on software like this myself, but I assume we’re something like, if you have to do GDPR compliance, that’s like very important to your business or doing business in Europe.
Right. And I just can’t comprehend now if you have like AI agents writing a hundred thousand lines of code a day and committing it. Like, how do you know you’re compliant with anything? GDPR, PCI, like government saying you have to store data in certain ways in certain locations.
Like it doesn’t. Um, I’ll tell you, Joe, people barely know now and it’s, it’s not always the fault of the people.
A lot of it is the fault of the auditors who cannot give a clear answer on anything. Well, I mean, maybe that’s true, but you could at least like pretend you’re trying. Like if you just say, yeah, you’re just saying like, oh, like, like, like we have a million lines of code generated per week and there’s no human ever looking at it, but we, we’re definitely GDPR compliant.
We have promise, or we’re definitely not using any copyrighted code in our million lines of code written over it. It just seems like totally impossible to, and I thought these things mattered. Maybe they actually don’t and no one actually cares, but.
It’s not that it’s impossible as Eric was saying, you know, it is up to the auditors a lot, and this is the same way that everything gets swept under the rug. I used to do PCI used to be involved in, I was the one getting audited, but you know, on one of the checklists, it would be, this needs, this needs to be behind the firewall.
That’s it. Right. Because someone in Congress, like you can look up the, the, where the PCI stuff came from. You have people who are not technical making up rules about technical things.
As technical people know, there’s no better person to get around. Un-technical rules than technical, the, you know, the well-actually crowd. So love them or hate them.
The technically correct crowd. You’re a technically correct. So as Joe would say, the best kind of correct. So literally went in and enabled windows firewall. Granted, this is circa 2007 and got a check mark, got a check pass on the audit because it was technically behind a firewall.
One of the worst firewalls in the world then at that time, but technically behind a firewall. So yeah, Joe, I mean, it, a lot of these places, and if you look, there’s even the, uh, there’s actually a company that doesn’t even exist anymore.
They were an audit and they got caught passing people that shouldn’t have been passed and getting money for it. And they’re no longer around.
So yeah, a lot of this is underhanded. I guess, even to go into Eric’s question, there’s a lot of different things going on right now. And just as the, uh, I don’t know if y’all remember it, but the machine learning craze and the blockchain craze, right?
Blockchain didn’t, sorry, the blockchain didn’t go anywhere. It’s still around. Now it’s just actually being used for things that should be used for rather than being put in cereal and toasters and everything else.
There is a lot of circular money in AI. There’s a lot of very good write-ups already on it. So I don’t want to rehash that. But as long as, uh, as long as that continues, having said that to Joe’s comment, a lot of the companies are starting to get sued.
There’s a large, there’s multiple lawsuits against DRM manufacturers right now with very, in various different forms. And if people don’t know the DRM used to be cheap, but they’ve also been.
Caught being cartel twice already in history with the 2000s being the last time and, uh, a failed, I think it was around 2016. There was another lawsuit against them for cartel like items.
So I think it will come to a head, but it’s going to do the same thing that machine learning did, which is AI and what the blockchain did, which is you’re still going to have it. It’s actually going to be used for things that make sense.
And as Eric pointed out, aren’t going to cost you $3,200 to say, please summarize my calendar and leave out half my meetings. But at the same token, I think it will be more judiciously used where it makes sense.
We will actually start to see good benefit from it because it won’t be replacing the things that you’re not going to have it. It’s just making hundreds of thousands of random lines of code and just committing it.
You are going to have it maybe help in areas, but the domain expert, as we’ve seen with Ford, as we’ve seen with all the other, I mean, Starbucks was losing how many millions per month because the AI tool refused to.
And what does Starbucks need AI for? They were using it for inventory. It’s actually, it’s a hilarious story. I would encourage everyone to go read it. I would encourage everyone to go look at the Ford story, the Starbucks story, and the Clark.
The Ford one I’ve seen, the Starbucks one I haven’t. I was like, what are you just throwing crappy frittata recipes at the wall? It sounds like we need some links in the description, Eric.
You’re going to ask your AI buddy to research those? Whatever links you send me, I will put in the description. But to summarize, the TLDR is, it did optical inventory scanning because humans are very bad at that and it’s tedious.
And it refused to classify things. It would just skip over stuff and just not even care. And then it would say, you don’t have any of this. I mean, that sounds pretty human-like to me.
Yeah. That sounds very human, especially for Starbucks inventorying. You’ve got to get meth somehow. Yeah.
I guess so. Because actually, that is a problem that I frequently have with my robots. Well, apart from that, is whenever I set them onto a task, they really love deferring and downscoping and skipping over stuff that was really explicitly laid out as like, this means success, without this, we are not successful.
And I’ll be like, that’s a little hard on this pass. I’ll get to that later. And it’s just like, here’s what I skipped. And I’m like, it’s half the stuff I asked you to do. What the?
It’s shocking when you train it on, it’s not shocking. I’m being facetious, but when it’s trained on human behavior, then people are shocked that it exhibits human behavior.
Like the chatbots, right? Tay and all the other crap. Well, it became super, super, what was it? Nationalist.
Yeah, I started quoting Hitler and stuff. I was like, damn. Well, you trained it. You let it go crazy on the internet. The internet is, you know, the cesspool of everything. Like that is what the internet does.
Yes, literal backwater. You’re shocked that it did the thing that you told it to train yourself on. That’s what, that these people are still so, it’s, you’re still so shocked by it. And I don’t know if it’s fake shock or real shock, but I just want to punch those people.
Well, speaking of being shocked, going back to your bubble question, I thought everyone had learned by now that like big companies aren’t going to take care of us or offer like a really good cheap product that lost forever, right?
Like I was, I was trying to think of all the ways like even if it’s a good now, which is debatable, like what are the ways that it could be made more shitty in the future? Because it’s obviously going to happen, right?
So, so I was thinking of things and probably all of these things like already exist in some form too, because I’m not nearly as creative as like, you know, the army of people trying to make things more shitty for us.
Like, like, like imagine having to watch an ad between every prompt, right? Or, or, or you’re being throttled or like things unavailable or the, where the price goes up, like, like a hundred acts, like all those things feel inevitable to me, right?
Like we’re supposed to believe that there’s some, like, it’s like Eric said, oh, well you should have been charged some huge bill, but we’re actually not going to charge it to you because we’re like so generous and this is definitely a thing that’ll, that’ll, that’ll remain this way forever.
Um, uh, you know, like it wouldn’t surprise me if there already was some company that, that made you watch ads, like, I mean, in between your prompts, right? Like, and that, I’m, uh, I don’t know if I hate ads more than, more than AI, I don’t know where those two rank, but let’s stick to my, my, my token free lifestyle with the exception of generating dumb images on occasion for free.
That is something that, uh, that I use tokens for all, I confess to that. All right. Well, I, we can forgive you that now that we’ve, now that we’ve beaten that confession out of you.
I think it would be interesting for the, sorry, just real quick. I think it would be interesting for the people who are watching this shout out to my grandmother. Nana Ghilardi out there.
Yeah. Represent. She, uh, it’d be interesting to get other people’s takes that don’t, that really don’t use it because I think that’s like why, not why in, in a bad sense, but what interactions you had good or bad, I think if we looked at everyone’s interaction, I don’t think it would come out.
No, probably not. No. Um, like, you know, I, I think about like my mom using AI and most of it is her yelling at Alexa for, for setting timers wrong.
And I don’t know, like, I imagine there’s a lot of people floating out there in the world where it’s just like, like, like, why are you listening to me? Like, uh, I mean, AI seems to offer a revolution for the scamming industry.
Yeah, that’s true. So, you know, and like, like really like, Oh, what does any new technology good for if not scamming more and more people, even more efficiently than ever before?
Right. Yeah. Just robo call everyone. I get about 17 calls a day from fake numbers and people telling me that my loan has been approved.
And I’m like, I, I don’t know how to get off this merry-go-round. Like my phone number is just out there and just a lot of me, like blocking reports, bam. Oh, there we go.
Okay. Are we going to do this again? Yep. 20 more calls today. I did that with a, with a text, right? So I got the, you know, the spam text, but I responded as an LLM would, and I went round and round with it for probably a good 20 something minutes.
And, uh, I eventually had it come back and say, Oh, I did. Cause I said, sorry, you know, our volume is high. Please choose from the following menu items.
And then it would say, no, I’m, I’m conducting a survey. I’d like to know. And I would just send the same thing back. And it said, um, I guess I would like to be removed from this list. And I said, great, you’ve been removed.
Please, please reply back to be added again. And then it replied back. Thanks. I said, great. You’ve been added. It just kept it going. Is this on Microsoft company time, Sean?
I gotta ask. What’s. Yeah. This was like a random sat Saturday. Uh, I was, it was, it was on Saturday company time. These days too.
No, it was farm time. I was. Okay. Yeah. One, uh, maybe warning to end the thing. If Eric’s ready to end the thing.
Hopefully AI doesn’t cut into like human relationships and communication too much. Uh, just, just to share a very low stakes example that I had personally, uh, I finally communicated to a famous SQL Server, open source project.
I’m not going to name names, you know, but it’s something I can, I can cross it off. Drag me out here, man. Can cross it off my SQL Server bucket list. And so I, I did the thing.
I made my PR and submitted it. And I, I got an AI code review in response to my commit. And it really felt, you know, they say you should never meet your heroes.
Like that really felt like so depressing to me. Like, man, like I’m finally contributing. And now I’m reminded of like, all of the, like, I don’t want to spend my free time, like arguing with some shitty AI about like, Oh, well, you’re actually not logging additional debugging info, which, which wouldn’t be written to the table anyway.
Like, this is like, it was very, uh, it was very disappointing, but later the maintainer did stop by and presumably everything he wrote was, what was his own words, but it’s, it’s I know, I know that, so I, at first I thought you were going to talk about something that happened with, with performance studio, but I realized now this is a completely different repo.
And I was going to give you a thoughtful, heartfelt explanation as to what happened. No, I’m not going to hear that. I’m not going to hear that. It was not Eric’s tool. It was someone else’s.
All right. I will say, I will say to that, Joe, uh, I had made a change and I had a small PR and then the AI bot went over it and said that my change was incorrect.
And then stated in the next five paragraphs that it wrote about why it was incorrect, that the end result is actually I’m correct, but it should still be undone so that it can be redone because redoing it would be the correct thing that I just there just for shits and giggles too.
I did a, I did a quick submitted a quick, uh, AI generated PR and the AI bots argued with each other and absolutely nothing got done, but it was hilarious. You got, got to use your, uh, your, uh, daily token budget somehow, right?
Yep. It’s a KPI. Gonna, gonna fire the bomb 10% of token users any, any day now. Oh, sorry.
Lay off because I’m sure no one gets fired. Right. Voluntary. Voluntarily fired. I’ve realized the error of my ways.
I should not be employed here. All right. I’ve, I don’t know how long we’ve been talking. It feels like hours, uh, maybe days. Have I eaten?
I don’t know. But, uh, I think, I think we’ve covered enough, not enough ground on this one. It’s been a pleasure as always. Uh, we should do this more often. Maybe if you think of anything else to talk about.
Just get some, uh, I’ll, I’ll ask the AI for some topic. See, that’s a great idea. But then, like, there’s like, just what, what should we talk about next? And then you can just say no to all of them and be fun.
Uh, but, uh, thank you to, uh, uh, Joe Obish and Sean Ghilardi for joining once again, the Bit Obscene radio program. Uh, you can find them, uh, nowhere.
Uh, they’re not, not, not on the internet, really. That’s, that’s probably good. Uh, and, uh, I will see, we will see you in the next episode, uh, at some undetermined date and time.
All right. Thank you for watching. Thank you for listening to the radio program. We’ll see you in the next episode.
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.
Erik Darling here with Darling Data, and we are so excited to be talking about how to deal with DDL in CDC. We are just really beyond thrilled. So I’ve had to deal with this with some clients recently, and the problem with CDC, of course, is that if you change, add, drop columns from CDC tables, or tables that are covered by CDC, rather, the current change capture table does not reflect those changes. You have to do some work to figure it out. What I’ve got in this video is I’m just going to, a script that I can walk through, I can hit F5 on it. At least I think I can. At least the last time, I believe it is idempotent, so we will hit F5 on this, see what happens. We hit errors, so be it. It wouldn’t happen, it didn’t happen the first time. Sneaky. So I’m going to show you a couple, well, I guess walk through the problem first, and then show you a couple ways of handling it, and I will make this script available in a GitHub gist, because it is not worth canonizing in the Darling Data repo in any way, shape, or form.
So in the video description, way down here, next to me, you will see all sorts of helpful links. If you would like to hire me for consulting, perhaps you are having CDC problems of your own, you can do that. You can also purchase my training. I’ve got coupon codes and all sorts of stuff down there if you want to save some money on high quality SQL Server training, performance tuning stuff, things like that. You can become a supporting member of the channel if you so wish. If you feel like what you get out of here is worth like four bucks a month, you can do that. You can also find a link to ask me office hours questions, which I will do my best to answer. And of course, as always, if you enjoy this content, please do like, subscribe, tell a friend and all that, or else I’ll have to bring the gaffer tape to your house. I don’t know, maybe that might be fun. If you like free SQL Server performance monitoring, and how could you hate free, right? I’ve got my free SQL Server performance monitoring tool available. It’s over on GitHub. It’s at this link. That link is also down in the video description. And I’ve got some big changes coming out for that. I hope to be done. Hope to have some stuff out this week for you. And we will, we will, we’ll talk more in depth about that when it when it finally comes out.
But with that out of the way, let’s talk about this CDC business, because that is absolutely important. That is the that is the crux load bearing and arting its keep in this video. So this is the script that I’ll be handing off. It’s got some stuff in here to create tables and whatnot and enable CDC. And it’s got a description of some of the problems that you’ll run into. For example, if you add a column to the post table, it will not immediately show up, even if you force a CDC scan, it will not show up in the CDC table. However, we will have some columns in the DDL history table. And the DDL history table will will tell us what DDL occurred on the table that we are CDCing. So we’ll at least be able to figure that out. But we will not see the results of that DDL show up.
The other problem that we have is that if we create so something that I learned as I was going through this, and I’m a little surprised, because I’ve worked with CDC a lot, and this has never come up. I’d always done things in one way, and I didn’t realize there was another way is that CDC tables support up to three capture instances. So at any given time, you can be CDC capturing to three different places. This is wild. So like, but the problem is, if you start a new one, it only starts from like, it only starts getting data from when you turn it on, and it will reflect the new state of the table, but it won’t have any CDC data from before.
So depending on like the cadence of how you’re pulling data out of CDC, that could be no good, right? Because you need to make sure that you got everything you needed before you start pulling from a new capture instance. And like the thing with a lot of third party tools, and even scripts that, you know, people use out in the wild that sort of drain data off of CDC tables, put them in another source, like, I don’t know, Snowflake or something, is they sort of hard code the change capture instance, or they’re not wired up to look for like multiple change capture instances for a table. And so like, even like the metadata discovery is a little sloppy on those, like they don’t, like, it doesn’t always work the way you want it to.
So like, you know, like, you really have to be careful about juggling the two capture instances, because at any given time, you can switch capture instances, and that might not be fun. So you do have to be careful in there. But you like when you start the new one, you don’t get stuff from the old one. So you basically have two strategies that you can use to backfill or to port data over, you could very easily start the second change data capture instance, and then move data into the new one.
But one thing you’re going to have to do is make sure is get the start LSN from the current capture instance, and then update the change tables table to make the start LSN for that for the new one match the start LSN for the old one. So that you like the next time you like run one of the CDC functions to get it, you actually get all the all the data that you want. Now, depending on how your tooling or scripting works to get this, you might want to do this.
And you might want to rename the capture instance from the new table to that of the old table, and then get rid of the old cap, the original capture instance so that there’s nothing, there’s no two capture instances for things to get confused about. That’s something that I’ve run into and something that I’ve had to protect against. So it’s out here for you as well.
And then another thing that gets or another strategy for that would just be to so like what like the simple sort of like the simpler way of doing that, depending on depending on your preferences and that this one, this one has downsides to which we’ll talk about. So one thing that you could do is instead of starting a new capture instance, moving the data over, all that stuff, you could disable and re-enable CDC, but you have to save your data off. And if you have a lot of data stored in CDC, that could not be fun.
And the other kind of annoying one about this is that like you do need a transaction. You do need to make sure that like you don’t allow changes to the base table that you might miss while it’s disabled. So what you do in this one is a little bit different where you would sort of create a backup table of your CDC contents.
You would grab the start LSN from the current table, then start a transaction and you do something like this, right? Select top one from comments with tablock X serializable to make sure nothing changes your table. Disable CDC for the table, add the column that you want, and then re-enable CDC with that column in there.
And then you would have to insert the data from your backup table. You would have to get all the data from your backup table into the re-enabled CDC table and then update the start LSN to the original LSN that you pulled up before that. And then you would be free to commit the transaction and move on.
So once you do that, though, everything is back in a good state. Again, the approach that you would take here does depend a bit on exactly how your ETL tooling or scripting, how much data is in exchange data capture, things like that. So there are some things to think about, but you at least have two approaches.
Choose the one that best suits you. That’s my advice there. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you were just as thrilled to hear about CDC as I was to talk about CDC.
Anyway, thank you for watching.
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.
Erik Darling here with Darling Data, and it is another fine Tuesday here in the Darling Data household, the greater Darling Data area, and we are going to answer five user questions, because, you know, what else, what else are we going to do on a Tuesday? One of the most useless days of the week, right? Until you get to Wet Wednesday and Thirsty Thursday, you are just struggling. Down in the video description, you’ll see all sorts of helpful links, where you can hire me for consulting, buy my training, support this channel with as few as four buckaroos a month, ask me office hours questions, just like we’re doing today, you have the power to ask me questions of your own volition, your own, whatever, your imagination is the limit, my friends. And of course, if you enjoy this channel and the things that we get up to over here, please do like, subscribe, tell a friend.
Or punish a foe, I don’t know, whatever, whatever you want to do here. My free SQL Server performance monitoring tool, which has gone Enterprise Edition, we now offer a headless collector with a separate viewer and all sorts of other good stuff, so you can monitor millions of servers all at once without having to worry about it. Totally free, totally open source, no commitments, no emails, no phoning home with metrics.
I don’t want your metrics unless you’re giving me money. That’s money for metrics, I’m not looking at your crap. I have enough crap to look at.
All the valuable stuff that you would ever want to collect to monitor performance on a SQL Server. And there are built-in read-only, heavily contracted MCP tools, if you want to let your robot friends talk to your monitoring data and give you some phony baloney. That’s where we’re at in the curve again.
We are circling all over the place here. But let’s answer these questions. Let’s get these out of the way so we can carry on with the important work that we have to do in our lives. And here we are.
Why does SQL Server sometimes ignore indexes that seem perfect for the query? Every time I get a question like this, the answer is always the same thing. Optimize our costing, right?
It thought that using a different index would be cheaper. There’s no real reason for it aside from that. The optimizer looked at your indexes, thought about them, and said, I think this one’s the cheapest to go with.
And that’s what it chooses. I suppose, I’m trying to think. RavenDB gives you more specific reasons why.
An index was chosen, but SQL Server doesn’t have that. And I get it. Because the reason is always costing.
So what is valuable for you to do in these cases is take the query and test it, forcing the index that you think would be cheaper. You’ll see that the cost is higher if that index is chosen. I suppose there might be some narrower reasons where maybe the optimizer didn’t work.
I suppose there might be some narrower reasons where maybe the optimizer didn’t work. There might be some narrower reasons where maybe the optimizer didn’t work. Yeah, run the query, force the index, you’ll see that the cost for using that index is going to be higher, for one reason or another. And look at the ensuing plan shape, see where those costing goes.
But more importantly, more importantly, most importantly, for you, run the query and get an actual execution plan testing with the the query plan that SQL Server naturally chooses and where your index is forced. And if if forcing your index results in a faster query plan, that just might be the fix you need to do. Because SQL Server is pretty unlikely to change its mind about that.
Now, one thing that I will point out here is, you know, of course, forcing indexes is not always possible for people, right. But, and one hint that I am, so in the case where maybe, let’s say you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, nonclustered indexes on a table and one of them there’s one that you think would really help the query and when you force that index you get a seek with some other query plan and it’s faster and in the alternative version the one that sql server naturally chooses there is a scan and the the query is slower you might be more comfortable using a force seek hint on the table than choosing a specific index often the force seek hint will evaluate to the correct index or another competitively structured index and use that one to to seek into rather than the scan plan that’s that’s my advice there all right oh man how can logical reads go down but total runtime still go up logical reads is a stupid metric you i i’ve never tuned a query based on logical reads and uh well i want to say i have not tuned a query based on logical reads since probably 2014. uh i care about cpu and i care about duration right cpu is a good measure of how much effort is going into the query and duration is a great measure of how long the query takes right and there are all sorts of interesting things that you can look at within that right if you have a series of queries that you can look at within that right if you have a series of queries that you can look at within that right if you have a series of queries that you can look at within an editorial query and uh an editorial query and uh duration is much higher than cpu there’s some other work going on in an editorial query and uh duration is much higher than cpu there’s some other work going on in there right either maybe reading from disk uh physical reads not logical reads uh you might be getting blocked in some way there might be some other resource that uh is getting chewed on uh on your server right um in a parallel plan if cpu and duration are neck and neck then you might have very ineffective paralysm but to me cpu and duration are the two most interesting mechanisms using a compute and a dummy i flipped that i’ve used it for decades as a method to run real-time ыш that a query emits. Now, you might be tuning for other things, right? Like memory grants at some point, like memory usage might at some point come into play when you’re tuning a query. If you have to make sure that this query no longer asks for a giant memory grant that blows the server up when it’s run concurrently and wipes out your buffer pool and all that. But to me, just for like find queries that need help, CPU and duration are it for me. Logical reads, not in like over a decade.
I don’t think. It’s old folks home stuff, right? Antiques village metrics. In your experience, what are the hardest performance problems to diagnose? Well, I mean, I think maybe diagnose is not the right way that I would go with this. My main problem, the hardest performance problems to figure out are the ones that go unobserved.
If you can’t see a problem, you can’t solve a problem. And like diagnosing things once you can see a problem becomes light years easier. But I think, you know, things that are hard to diagnose are things that, you know, like feel like the database, but aren’t the database. So like a connection timeout or like an underpowered app server where like the queries and SQL Server are constantly, but then like people on the application side are just sitting there like, what’s going on?
I’ve been waiting 30 seconds. And you’re like, the query’s done. It’s sitting there waiting on async network IO. This is nonsense, right? Like what’s wrong with you? Stuff like that. You know, there’s also maybe some like, like really internals-y stuff. You know, like if you get really, get into some really weird situations around like latches and spin locks, those can be like, those usually aren’t first on the checklist. So you go through a lot of other stuff before you get to them. But as far as like diagnosing, really, it’s like, I feel like that’s pretty easy once you have the correct things observed. I think the, like the question for me is always, what do I need to observe in order to diagnose this? And sometimes figuring out what you need to observe is the toughest. Especially if a problem happens like randomly and rarely, and you’re like, oh, wait a second. This is all going horribly.
Um, I had a client recently where, um, they had an AG and the primary node in the AG would, uh, like abruptly, uh, just like, like bankrupt on memory and performance would tank and they would fail over to the secondary and the secondary would be fine forever. And they were like, oh, okay, I think we’re safe. We’re going to fail back over to the primary. Primary be cool for like a few days a week. Then out of nowhere, tank, right? And I was looking at the two certs. I was like, oh, you know, there are two servers and the primary had in memory, temp DB, metadata on and the secondary didn’t. And I was like, well, you should change that. And I bet your, your stuff goes away.
No, this isn’t a knock against in memory temp DB metadata. There are, there are bugs and there are weird things that can happen with it. Most people don’t run into them. These folks just happen to have quite a lot of memory and have quite attempt DB heavy workload. And, um, they, they ensure they have a Microsoft support ticket now.
Uh, like, like I could die. Okay. diagnose a problem but i can’t solve that problem i could say use less temp tables or use fewer temp tables but you know who wants to hear that like i gotta rewrite all this stuff bug report right which is which was the right thing to do because that shouldn’t happen you should performance features should not cause performance problems so there we go um let’s see here when does query store become more dangerous than helpful uh you know there’s some workloads that the query store like you have to have a really crazy batch request a second or even be like like compilation batch request would probably do it uh workload for query store to um to beat you up microsoft has in fairness to be fair microsoft has done quite a bit to make query store less of a burden since its inception in 2016 um and so i don’t really find uh many people uh having problems with uh query store the way they used to there are some things that i if you if you do have a very heavy workload there are some things that i do recommend though um i think turning off query store weight stats is uh is pretty much a must if you have a if you have a heavy workload uh collecting those is not fun and i find quite often they don’t add a lot to the query tuning picture um sometimes they’re useful because you’ll see stuff like buffer io in there you’ll see stuff like um like lock weights in there but like if you know like most of the time it’s just like some cpu and parallelism you’re like oh wow well i knew that i can see that from the duration and cpu i knew that it’s obvious right um like even buffer io you’re like yes i see the physical reads that’s that’s great even locking if if if duration is much longer than cpu you’re like well something happened in there probably locking so like there’s like i just find them unnecessary in query so if they’re there then you’re not having a problem cool but i i usually turn that one off um and then of course 2019 i think 2019 or so maybe yeah 2019 offered like different collection profiles where you can like uh like like like adjust the thresholds at which things go to query store which is which is nice too but um when does it become more dangerous you know the thing the thing i get the thing of it is um if you have that type of workload uh you’re gonna care quite a bit about performance for that workload um you you might be able to uh assemble uh other ways of collecting that sort of performance data perhaps a free open source sql server monitoring tool would be of some value to you um like mine just saying uh but uh because you know like that’s not like you know like it does use query store if it’s there but it’s also collecting stuff from the server that’s not in that way so um like you would have to figure out a different way to um like really catch good performance issues there because just looking at the plan cache once in a while um you know you have a big heavy workload your plan cache is going to be blown out seven ways to sunday it’s like you’re not going to be able to find like historically treacherous things in it like just like most most plan caches that i look at do not have a very good lifespan all right last question here why do top or roll goal queries sometimes run slower than full scans uh well lots of reasons um let’s see uh often like you like like let’s say you get a query that does like a full parallel dop 8 scan of a big table and you’re like oh cool i found everything pretty quickly um top and roguel query sql server sort of makes a little bit with itself and says hey i think i can find this data in three rows but then it can’t find that data in three rows so i think the what the the thing that you want to look for in those queries might be like um like rarely occurring or non-existent data patterns that would be one of them um sometimes you’ll see sql server do something really silly and put like a top above a scan and it’s like single thread and it’s a big table and you’re like oh wait no uh don’t do that right top above the scan one of like if you see that in the query plan you you have you have a fix immediately because that should be if a top above a seek is like much much much easier to cope with than a top above a scan especially on a big table because most likely that top above a scan in a rogo plan is going to be single threaded and it’s like some 80 million row table and you’re just sit like single threaded scanning it like every time trying to find a table and a row in sql server is like i can do it in three rows and then like they’re like 3 000 rows you’re like you’re not doing it you’re just scanning this giant table you’ve scanned this 80 million row table 3 000 times now single threaded we are we are quitting done here um so usually it’s that um uh usually that’s my experience with it um you i’m gonna i’m gonna say this but i’m gonna preface it with saying like there’s a big asterisk here a lot of times uh uh SQL Server users um because of these very low number of steps to split um top middle third row and let’s say they go to the top and rogo queries um because of the very low row estimates where sql server is like no i can do this in three rows uh they will not get a parallel plan because sql server is like i can do this in three rows why do i need a parallel plan i don’t need multiple threads for this and three rows we’re done so a lot of times they get costed incorrectly and so you or like i mean they get costed correctly for what they are but for the they get costed correctly for the estimate but not for the reality they will be costed very low and not be eligible for a parallel execution plan. That’s another thing that ties into it. So I think that’s probably the most common stuff that you’ll see out in the world. I don’t know. Perhaps there’s something in there that I am not thinking of immediately, but I think that’s probably good enough. So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where I’m going to talk about something with change data capture that’s not fun to deal with and a good way of dealing with that. So with that out of the way, thank you. I love you. I will see you tomorrow. Good night. Sleep tight. If you have bedbugs, man, get out of the house. Don’t not like don’t like just leave. Burn it down. All right. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Erik Darling here with Darling Data and in this video I’m going to talk about where the free SQL Server monitoring tool is headed in the near future and go over kind of what I’ve been working on, why I haven’t done a release in a couple weeks now. So when I first started working on this, my original goal was to make something very easy for people to set up and run depending on their preference or depending on certain requirements. There was what I call the full dashboard that created a database on your SQL Server, logged a bunch of things there, and you could query it, you could back it up, you could send it to someone, you could do whatever, just about whatever you wanted with it.
But there were some problems with that setup. One was that a lot of people thought that that would be the one database that everything got logged to that they were monitoring. They didn’t realize it was one database per server and that database had a tendency to get fairly big.
So I had to take some additional correct… When I first made the whole thing, you know, I made sure all the indexes were compressed and took various steps to try to keep things neat and tidy. But, you know, certain…
Collection elements like query text and query plans and blocked process and deadlock report XML tended to bloat things out. So some additional corrective steps I took were to use the compress and decompress functions on those things. And that cut down on the size quite a bit.
But the… And I think I haven’t gotten really any complaints about that stuff in a while. So hopefully that solved the problem.
But… The additional sort of friction for me was having to maintain two different code paths. If there was a bug in one, there might be a bug in two.
And getting my various clod army to remember that there are two and fixes need to go to both and not defer or downscope things randomly was a challenge. So that wasn’t fun. So my first idea was I could make a lot of the stuff…
Between the two monitoring tools into sort of shared libraries. And that way fixes would only need to go to one place. That didn’t quite go as I planned.
So… And I still had this sort of additional friction point of, you know, light being very, very popular. But, of course, light is a user application.
And if you close your laptop or shut down a VM that you have it running on… Or, you know, you close the dashboard, it stopped collecting. That was what full tried to sort of get rid of.
But full had its own problems that I’ve already talked about. Full was just agent jobs running, constantly collecting stuff. As long as your SQL Server was up, data was flowing. So that was…
So, like, neither one really solved the full picture. So what I’m doing now is I’m essentially deprecating the full dashboard. I am keeping the sort of…
Portable light version of that. It’s kind of like when you download Crystal Disk Mark. You have the full installer or just the zip that you can crack open and run. And choose your favorite anime girl theme. That’s the best.
So it’s sort of like that. Minus anime girls. Unless… If anyone wants to donate or, like, add some themes in to include anime girls, I’m totally fine with that. If you’re into that sort of thing, skin it up.
But, you know, it’s not my thing. No pillows with faces on them for Erik Darling. But…
So the direction that I went is… One, I wanted something that would still be free. Or at least as free as possible. And so I chose Postgres with the Timescale database plug-in.
Or… What do they call them? Extension in there. The reason for the Timescale thing is it offers incredible compression.
Like… Postgres on its own does compression pretty well. And they have this toast thing that kicks in for string columns of a certain size. And in Postgres 18 you get this crazy LZ4, I think, compression on stuff.
So, like, Query Text and Query Plan XML and the Deadlock and Block Process Report XML was already doing a pretty good job of staying nice and small and tidy. But we have the additional sort of Timescale thing kicking in and keeping things even smaller. Which is wonderful.
So Postgres is the chosen backend. That means you don’t have to pay for another SQL or worry about having to pay for another SQL Server. I know a lot of y’all out there really like putting your monitoring tools on Developer Edition. I’m not the licensing police.
But I’m just saying… Might be a little dodgy. Like, in terms-wise. But… You know… That’s…
Between… That’s between you and Microsoft. I got nothing to say about that. I am just a lowly consultant out in the world trying to make a difference. So, yeah. So it… The Postgres part is free.
You will pay the… Potentially pay the cost. You can bring your own Postgres, too, if you want. So if you already have a Postgres server running somewhere that you’re okay with having a monitoring tool database in, you can bring your own. Otherwise, you would just need a new VM or something that has the Postgres instance on it.
And what this thing… What this uses is a headless Windows service. Headless meaning it is not tied to a user.
It is part… It is running on Windows regardless of… Well, I mean, unless you shut the VM down completely. Right? If it goes to sleep or you log out or something, it’s fine.
But… If you shut the VM down, you… I can’t… I can’t help you with that. So it’s running constantly the way that the agent jobs in Fullwood to collect data and put it into Postgres. And it’s sort of…
And it completely detached the collection service. The collection service from the viewer. So if you have multiple users… This is another friction point was getting this so that multiple users could all ping in and see the same set of stuff all at once.
You can do that. This also allowed for some neat security stuff where there is like an owner account that can do anything. There is an admin account that is allowed to change schedules and collection stuff and alerts and whatnot.
And then there’s just a reader only. So if you have folks who shouldn’t be… Who you don’t trust to tinker with those things, they can… They have a read only path to see the monitoring data without being able to mess with anything.
So all good there. And now we have, behold, a viewer with some improvements, I think. And if you’re asking how this solves the double bug thing for me, it doesn’t completely but it does make it easier.
Because much more of the viewer code… Viewer code path stuff is shared between light and this thing than with the full dashboard. So that’s good there.
Anyway. What was I saying? Yes. Good. So now we have this. And I’ve made some visual improvements and some performance improvements to things along the way. So you start off with what you would normally see when you open up light pretty much.
Again, some improvements. We’ve got some new stuff up here that show you the number of servers. How many are healthy and all that stuff. And then you have the normal sort of NOC style dashboard in here.
I believe HammerDB is… No, I think maybe… No, HammerDB probably finished on 2025. That’s why things are all green and happy over here. But going into the monitoring data itself, we can see…
Actually, let me set this to the last like four hours so that things are a little bit more zoomed in to when we had HammerDB running and doing stuff. So this is the sort of normal stuff that you see. We have our overview here.
We have our weight stats here that includes top weights and, you know, like the top weights that you have in here along with some of my favorite weights to show people. There are some neat additions when you like click on stuff and look at things in here. But you can just as always, if you want to, you know, see queries causing a problem, you right click, you get to those queries.
And under the queries tab, we have all the… Again, just same user experience as light. Just with a different back end that is a constantly running collector to get stuff from.
So all the stuff that you would want to see in here. The UI is a lot snappier too. I think clicking around through here, sometimes there were some long pauses on things I was always mad about.
But now everything seems pretty snappy. There are some new collection metrics in this one as well that, you know, give you a little bit more information. You know, there was some stuff in full that I liked.
That I wanted to get further into. So I’ve got all this stuff in here. And there’s no memory pressure events on SQL Server 2025. But over on 2022, if we look, we can see some memory pressure events if we go back to the last 24 hours.
You can see where, you know, SQL Server 2022. So SQL Server 2022 is my old prod VM that has been downgraded to like 2 gigs of memory and 4 cores or something. This thing gets beat up a little bit easier.
But 2022. 2025 is where I do like all my new development work and stuff. Now it seems pretty safe and worked out. So we’ve got all this stuff in here.
All the same stuff that you would expect. One big thing that I corrected on the advice of the lovely and talented Kendra Little was TempDB has been unpropercased. And it is now in its proper state.
Right? All lowercase. Good. Right? That’s nice there. But everything that you would want to see around blocking and everything. Right?
That stuff. Current weights. Look at all those wild spikes. Look at our blocking stats. Look at our block process reports. And our deadlocks. And our perfmon. Right?
And perfmon. Remember perfmon has all the different collector packs in here. So depending. So like you don’t have to remember all the stuff that you want to see. All the counters that go into certain perfmon things. And other stuff that was brought over from full into this that wasn’t in light.
We have stuff like session stats so you can see what things were running. Like sleeping background total. All that good stuff.
We have our usual agent job things going on. Our usual configuration and configuration changes stuff. One thing that I did change.
So daily summary used to be just a one line about today. Like this isn’t all lit up obviously because I haven’t. It’s kind of new. Right?
So it’s like new data. But one thing I changed in here is I made this a calendar view where you know like you can see the good days and the bad days. And then you can like sort of click and see what was going on with the good days and bad days down here. And you can you know it’s like oh you had a lot of deadlocks and blocking and crappy queries.
So you can go and look and stuff in there. So this is an improvement I think. System events.
There’s not a lot in here because this is all from the system health extended event. And quite frankly once again. The oh crap section. If you have a lot of stuff in here. You might want to hire a young handsome consultant with reasonable rates like yours truly to come help you with your server.
We’ve got some latch and spin lock stuff in here that got promoted in from the full dashboard. Because some people despite best efforts still care about this stuff. And of course the collection health stuff where you know I get to tell you how well my performance monitor collector is doing.
So this is. This will be in the next release. Available I want to say in the next few days or so.
Most of the work is done. There is just a little bit of polish that needs to be completed. And once that is all done.
Sorry. So mid week or so. Well actually you’ll be seeing this on Thursday. So it might have even been released by the time you see this. We’ll see how that.
We’ll see how that plays out. Anyway. This is where things are. This is where things are headed. Much closer to sort of an enterprise monitoring tool than the previous versions. This.
If I had to put a high number on it could probably support about 500 servers. If it needed to. If you have 500 servers. God bless.
And again this is totally free. There is no cost to you. There is no per server or anything. If you want to donate money to this project you can. If you want to. If you need a support contract that stuff is available.
But otherwise it’s just totally free monitoring. You can get it at code.erikdarling.com. That will bring you to my GitHub repo. And it’s just under the performance monitor section.
So anyway. That’s where things are at. Thank you for watching. I hope you enjoyed yourselves. I hope you’ll use the monitoring tool in its full enterprise glory. And if you have any questions, comments, concerns, problems.
That’s what GitHub is for. Otherwise. Thank you for watching. 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.
Erik Darling here with Darling Data, continuing my fast ascendancy to monitoring tool mogulhood. What you see in the background is the latest iteration in advance in my free open source SQL Server monitoring tool. This is going to be the enterprise edition of it, but we’ll talk more about that later.
What I want to talk about in this video is a new Cloud Marketplace skill that you can see chugging away here in the background. And what it aims to do is take the analysis engine from my performance studio application, which includes plan parsing and analysis rules and all sorts of other stuff, and hand them over to an agent.
So you can use Cloud, of course, but there are poor unfortunate souls in the world who are forced to labor under the drudgery of GitHub Copilot. And I feel bad for them because Copilot is just plum embarrassing. I would smack the words out of its mouth if it were a human.
It is… There aren’t words for it. It’s dumb. But a friend of the repo, Hannah Vernon, was nice enough to open a pull request to give our imbecilic Copilot LLM access to this as well.
So hopefully you will gain some respite from its moronics. But back to the tool at hand. We have a Cloud agent here looking at a query plan that I gave it.
You can see up at the top it said, Hello, Cloud, can you tell me why this query plan is so gosh darn slow? And the Cloud pulled up the skill that I gave it.
Up here, let me look. Successfully loaded this skill. And now Cloud is chugging away. Analyzing the query plan with my analysis engine.
And you can see it using all that. And the reason why I did this is because I got so tired of re-explaining execution plan minutiae either to a new Cloud agent locally or abroad. You have to say like, no, I don’t want to hear about logical reads.
No, I don’t care about operator costs. No, Cloud, row mode operator times are cumulative. Batch mode operator times are cumulative. Batch mode operator times are insular.
No, that’s not what that wait step means. No, buddy, what are you doing? So I got very tired of having to do that over and over again and basically have to retrain every Cloud that I talked to on SQL Server execution plans. And so I have another agent working on the monitoring tool stuff in the background.
You’ll see that pop back up in a moment. So this Cloud has finished its analysis. And what it’s saying here is, well, let’s scroll back up a little bit.
And at any moment, the other dashboard is probably going to pop up and get in my way. But in the meantime, so we have an answer here. And Cloud is smart enough to see that we have an eager index pool.
It is 98% of the query runtime. It’s 69.726 milliseconds. And it explains what the query is doing.
All right. And it tells you that SQL Server built an index for this at runtime. And, wow, it’s roughly 32 gigs of pages.
Whoo-wee. That is a long time. All right. And it read 17.1 million rows to hand back 81,000. That’s a lot of work.
All right. And it also notes here that, well, I mean, I don’t know if I particularly agree with this. So, like, the tool can only do so much.
All right. It can still say, like, menacingly dumb things sometimes. All right. But it does understand that this is a .4 plan doing serial work and paying full freight for the privilege. Well, that’s a hell of a sentence there.
That sentence is doing some work. All right. And we see here, all right, Cloud knows that exec sync in a parallel plan indicates that an eager index pool is being built. All right.
All right. It could pop up for other reasons, but, you know, for our case, it is correct. All right. And now if we look down here, we even have Cloud smart enough to know if you create this index, my friend, the eager index pool will go away. All right.
The mechanism concretely, they both vanish from the plan if we have this index in place. All right. So it’s even cool. All right.
It’s even cool enough to know. We don’t get a missing index. We don’t get a missing index request from this plan because we don’t we don’t because SQL Server does not emit a missing index request when there is an eager index pool. All right.
So that’s good, too. All right. And it even tells you don’t waste your time with all this stuff. Just create that index. All right. So if you would like to try this new Cloud or Idiot GitHub copilot plug-in, there are a number of ways to do that. All right.
So if you would like to try this new Cloud or Idiot GitHub copilot plug-in, there are instructions to do that down in the video description along with a link to the GitHub repo where all the code lives in case you want to take a look at it. Maybe you even want to have your own robot, give it a once over to see if there’s anything that you might care about changing or contributing in there. I’m happy to take contributions on these things.
But you can do that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you enjoyed my new Cloud and or Idiot GitHub copilot plug-in that makes execution plan analysis go a lot better than it normally would if you were to just leave the robots to their own devices because their own devices are often not very good.
So if I could say just one last thing in closing, it’s that, you know, like I feel comfortable using the robots for certain query tuning tasks. tasks because i i know enough to to tell the robots when when they’re full of it but i i worry about the the folks out there who don’t know and who get the robot saying criminally insane things to them and criminally wrong things to them and then this being like oh wow that sounds so sure of itself i ought to do that it like it’s it’s rough right like like it like like they’re they’re very good at logically like you know looking at things and logically sort of like chaining things together but um man uh the the the advice and analysis portion is uh often difficult to overcome but uh this this will hopefully make it better anyway give it a shot it’s kind of kind of fun to have uh claude not be or kind of have kind of fun to have an llm not be be completely lost in a query plan anyway thank you for watching
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.
Take three, Erik Darling here with Darling Data, and today is Tuesday, and the weather is mostly back to normal. We went from startlingly hot to constant rain. Summertime, baby! Anyway, it is office hours. This is where I answer five user-submitted questions that hopefully, well, hopefully users, I don’t know, is AI, are people sending AI questions in? I don’t know. I can’t tell anymore. I’ve given up on the world. Down in the video description, you will find all sorts of helpful links. Probably the most helpful video description on the internet.
If you want to hire me for consulting, buy my training. Become a supporting member of the channel for as little as $4 a month. Ask me office hours questions like you see here.
Or even like, subscribe, and tell a friend. All of this is down below if you just put your eyes a little bit… It’s like, I’m not going to tell you my eyes are up here because the links are down there. So, whatever. If you would like, if you would enjoy free SQL Server performance monitoring in your life, I happen to have that totally free, open source, no email, no phoning home, just pure T-SQL collection running, getting important stuff about your SQL Servers, wait stats, blocking, queries, CPU, memory, disk, tempdb, you name it, it’s in there. And I give you a way to have your robot friends talk to just the collected performance data. So you don’t have to worry about them going and running crazy queries on your prod servers. They’re just in there talking to them. That’s it. Thanks for watching. I’ll see you next time. Bye-bye.
performance data and that gives them a slightly better chance of saying something that is not completely out of this world outlandish um you know i mean maybe maybe spicy moment here but um you know i’ve uh i’ve been trying to give the robots some more chances to do stuff with like query and index tuning and man they they feed me some real dumb lines about stuff uh it’s it’s it’s annoying right and i’m like like do you do you remember who you’re talking to and they’re like i’m sorry i started apologizing uh and you know um you know yeah anyway let’s answer some questions oh boy wait time per core per hour second and sp blitz first first high impact weight type and sp perf check eric to help answer if top weights are high low in sp blitz first since startup equals one per core per hour divided by server time since restart uh i’m gonna paraphrase this a little bit because this is a lot of words um so uh in sp blitz first and i forget where this come from this came from this might have been a jeremiah thing uh but at least you know back when this stuff was getting written uh the like per core per hour per whatever thing um was like like dmv company policy uh i never quite understood it it never really sunk in with me why that was better um you know uh i think in in your case specifically where you have the default cost threshold for parallelism and max stop set to two uh it yeah like the parallelism weights are gonna look weirder than they do uh if if you just look purely at like the the totals and stuff um you know like i i i guess it can i don’t i don’t even know um i i i don’t i don’t have a good answer for why it’s done that way um or uh why why that that’s the ongoing thing um it it never again it never really resonated with me um i’ve had much better luck doing things the way that i do them uh which is you know why i do them the way i do um you know i i can’t really think of a situation where i would maybe want to factor in purely the number of cores on a server without contextual information like you have with like well what is max stop set to what is cost threshold set to like things like that um because just purely looking at at cpu time or cpu cores um i feel like that that loses some stuff so um i’ve never had uh terribly good luck following that that analysis pattern but i don’t know maybe i’m the dumb one let’s see what we got here is blocking a concern when querying the dmvs if not how does microsoft keep that data updated without queries blocking them um so i i do see uh blocking sometimes when i’m trying to query dmvs um you might notice that some of my uh scripts even have a locked timeout on them um as active has a couple lock timeouts uh when looking at when when trying to fetch query text and query plans there’s some cursors in there that do that but that are that have locked timeouts in them um i think sp blitz index has a locked timeout and it’s for for a lot of them it’s because you know you you can get blocked while stuff is um you know is going on in there and i there are there are a few remaining bugs um if i remember correctly the one in sp blitz index is around uh identity columns uh there’s like weird blocking that can go on uh trying if like you know the identity columns are um being inserted into uh in in some way uh i think that blocks stuff up there’s just been a number of weird stuff in there i mean the safest thing you can do is just you know um use no lock or uh read uncommitted when when doing that uh i obviously don’t know what microsoft’s secret sauce is for a lot of the the things they do behind the scenes in order to log that data um so i i couldn’t comment on that uh i don’t know how they do that but i do know that you know just because of the amount of foolishness i’ve dealt with querying dmvs over the years um the old recommitted is the best thing you can do there all right uh it’s not your classes but now my girlfriend has a crush on you any recommendations uh you let her down gently uh you know i’m i’m a pleasantly married man uh i can’t do anything for you there um i i am i am willing if if you uh if it would help you out um i i would send i would make an eric darling mask for you to wear uh around the house for that whatever you and your girlfriend do, I don’t know, maybe if that would be good for you, but yeah, sorry, there’s only one of me, only one of me, but let’s see, grow a beard, get neck tattoos, what, I mean, maybe that’s just what you need to do, right, maybe there’s a pattern forming, I don’t know, is it possible to tune, blah, more blocking, tune blocking problems without switching isolation levels, hell yeah, it is, come on, so the thing that you need to sort of make yourself comfortable with at the outset is that you will not be able to solve every iota of blocking or deadlocking in the database, even with a better isolation level than SQL Server ships with, which is the default locking garbage read committed, if you were to use read committed snapshot isolation or even snapshot isolation, you would still need to do that, so I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, with either writers blocking each other or under snapshot isolation, potentially writers dealing with conflicts, so you can’t fix everything, but you can do a number of things to ensure that blocking is minimized, you know, don’t update a billion rows at a time, that’s usually a pretty good start, batching modifications that have to do large amounts of work, they might not get any faster from start to finish, but each individual chunk will do that, so I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, be kinder to your server and allow other queries to sort of orchestrate themselves in, making sure that your modification queries have adequate indexes in order to get to the data that they will be modifying, that’s always a big one, if you’re doing like a delete or an update and there’s a scan in your plan, you can bet you’re doing way more locking work than you need to, and of course, write your queries in a way that minimizes the work that you’re doing in modification queries, you know, things like sargability and good estimates are often very helpful to modification queries, as are occasionally, if you have a modification query that’s doing a whole lot of work in order to figure out what result, what rows and what the resulting value should be, many times staging that stuff to a temp table can be faster than relying on a Halloween protection spool to populate and do the update, so sometimes separating the nasty part of the complex heavy-handed part of a query from the actual update that needs to occur can be very useful as well, let’s see here, how do you explain to developers, ah, I live a hammer, that works on my machine means absolutely, I think that’s a little unfair, it doesn’t mean absolutely nothing, it at least tests some compilation, it gives you some sense of validity, but, you know, I guess, depends a little bit on what their machine is, doesn’t it, depends a little, because if their machine is a big honking, you know, prod server size hardware with an actual production database, obviously with PII scrubbed out, so we don’t have any leakages or data theft or exfiltration or anything like that, it could be faithful, but most of the time it’s not going to be.
You know, I think that developers developing locally is, is totally fine to get, you know, like a prototype of something out the door, and for a simple enough query, that might be as far as it needs to go, but for anything where, you know, performance, concurrency, things like that start to become a concern, then it’s, they need to be, I mean, developers need to know that things just need to be promoted up to, you know, they need to be promoted up to, you know, they need to go through environments in order for, sort of, their, their work to be proven out, so I, I think that it, well, it doesn’t mean absolutely nothing, there is a bit more that goes into releasing queries that, that drive applications than, you know, just some, some C-sharp code that, you know, draws a Windows form or something, right, we have a, we have different sets of concerns, right, including, you know, making sure that, you know, the, the, the plan stays, the, the query’s execution plan stays pretty faithful, and the same indexes get used, and performance stays good, and all that stuff going up from one to the other. I mean, I guess it’s okay if the query plan changes, but you want to make sure that performance stays consistent on its way up, and of course, if your queries are parameterized, you, you can, you can, you can, you can, you can, you can, you can, you can, you can, you need to test multiple, sort of, parameter combinations of high and low density data to make sure that your query is not parameter sensitive as much as possible. This is, that’s actually something that the robots are good for, doing that sort of data discovery and testing type stuff, you know, I’ve built a number of, I mean, usually, the robots generally prefer Python for this stuff, you know, I’ve built a number of, sort of, test beds and harnesses for things, you can leverage SQL CMD for this, and, you know, you know, have the, have the robots collect execution, actual execution plans for you, you know, you, you can even have them use my performance studio app, if you go to code.erikdarling.com, there should be a link for that down in the video description, the performance studio query plan analyzer has a CLI component in there that you can, you can have your, you can have your robot friends use to spit out either JSON or human-friendly query tuning advice with, and that can be very helpful for them to skip over a lot of the dumb crap that they think about query plans, you know, like the, and it even sucks with, like, you know, having the test harnesses that I have built, where there’s, like, very specific guidelines and instructions, or it’s, like, I don’t care about logical reads, I will, I will, I will smack you into the dead end, and I’m going to have to, like, I’m going to have to, like, I’m going to have to if you tell me about logical reads, I care about CPU, I care about duration, if you start talking to me about, like, query, like, operator costs or anything, again, to the death realms, but, like, you know, they, they still slip up, and they’re like, oh, we did 10% fewer reads, I’m like, who cares, why does it matter, does the query get faster or not, right, yeah, I don’t care how many reads we did, faster, yes, no, right, CPU, higher, lower, yes, no, what are we doing here, right, give me something meaningful, users aren’t going to be like, hey, good job, 10% fewer reads, my life has changed, I care about, I care about their time, speaking of time, I believe it is time for me to end this one, I think I answered that question, I feel like I answered it, anyway, thank you for watching, I hope you enjoyed yourselves, I hope you learned something, and I will see you over yonder in tomorrow’s video, where we will talk, probably some T-SQL-y stuff, I think.
That sounds about right to me, all right, anyway, thank you for watching.
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.
Erik Darling here. Suffering. Suffering in this massive heat wave. Don’t like it. Don’t enjoy it. I mean, the kind of weather… I don’t know. Where’s it always cold? I’m gonna move there. What’s Iceland like this time of year? I don’t know. Maybe Brent had the right idea in 2020. Move to Iceland. It’s never hot.
I mean, you know. I think every beer costs like $30 because they have to helicopter it in from somewhere, but it’s worth it. You never have to deal with heat. Occasionally eat shark that smells like stale urine, I guess. I don’t know. Anyway, let’s learn some more T-SQL. This is the wrong title. Screw that. It’s too hot to change it. In today’s video, we’re gonna talk about performance tuning queries that have to compare the date differences between two columns. There’s one very obvious thing that we can do in the form of the computed column, but then there are other things that we can do depending on our indexing.
We can write into the query. Again, coming back to the idea that sometimes we can align our indexes in our computed columns to our queries. And other times, we can better align our queries to our indexes. If you would like to purchase the full course, where I talk about absolute accuracy, and I talk about the data-driven, absolutely everything in great detail, and you have access to the scripts and the query plans and all the other stuff that we do in here. You can purchase it for $100 off down in the video description. It’s a great time. You can have the whole family sit and watch it, right? Put it on at dinner. Better than leave it to beaver, I think. You can also hire me for consulting. You can become a supporting member of the channel if you would like to part with $4 a month to say thank you for all the hard work that I do here. Ask me office hours questions. That is, of course, free.
So, you know, it makes me feel good. And, of course, please do like, subscribe, tell your friends. Get your children involved early. It’s never too early to start learning about databases. Ruin your childhood. If you are in the market for free, absolutely, totally, no strings attached free SQL Server performance monitoring, you can download the performance monitoring tool that I make. It’s on GitHub. You can read through everything. Down in the video description, you will find the appropriate linkage to get that and grab it. It’s a great time, and it’s only getting better. I can’t make it more free than it is because it is entirely free, but I can make it better. I can add more value to free somehow.
But now, let’s continue suffering. In this dismal weather, let’s talk about SQL Server. So, in the last video where I talked a little bit about precision and date and stuff, this one, we’re going to talk about performance a little bit more. So, we’ve got these two indexes already, right? We’ve got one on the post table in the Stack Overflow 2013 database, one on last activity date creation date, one on creation date last activity date. And a problem that we identified was that whenever we want to date these two columns, SQL Server has no choice but to…
to scan stuff, right? And that… this poses an issue for us as performance tuners because sometimes a scan really isn’t… really isn’t all that good. So, we have to scan our non-clustered index, right? It chose the index on creation date last activity date. Why? It doesn’t matter. It would have had to scan either one, right? And there’s no… there’s no difference. There’s no hidden seek plan here that would have helped SQL Server along. Now, flipping that around a little bit, right? Let’s say…
let’s say that we wanted to try to write this in a more sargable way. I don’t know. How do you describe Rob Farley? Is he a friend? Is he a foe? All I know is that he’s a magician, and you can’t trust magicians, but he is a pretty smart guy. And a long time ago, he wrote a post about sargability and called it, like, Look Both Ways, or something. And Rob had some very good points in that post.
And so, I’m going to try to expand a little bit on that in a slightly different way, because I’m not… addition. I’ll never hassle you with card tricks, but maybe some people are into that. So like if we tried to write this query in this way, so like say we wanted to remove the date diff function from the two column setup, and we said, let’s date add 10 years to one of these columns, maybe SQL Server could take advantage of an index on the other column, because we’re like saying, hey, SQL Server, you can see, you can do all the math on this one column, and then maybe you can seek to the data in this other column, because there’s no function wrapping around this column now. But SQL Server does not do that, right? SQL Server still completely scans this index on creation date, last activity date. And if we try to add in a force seek hint to say, hey, SQL Server, perhaps you would like to try seeking here instead, we get an error that the query processor could not produce a plan of that variety.
And so we are stuck. We were stuck scanning this. Now, one way that you can get around this is you can choose to write your query in a slightly different way and give SQL Server some boundaries, right? So like the whole point here is that we’re looking for places where there are a 10-year difference between creation date and last activity date.
So the first thing that we might do is try to find a maximum value to start with, right? That’s what we’re doing in here, right? We’re saying, give me the max. And this works in either direction, going max or minimum. So we’re going to say where creation date is less than this whole date add construct in here, where we find the max last activity date. And then we will do our final calculation in here like this, right? And this basically mimics the last query, except it gives us a place to seek into, right? And we can see that we do sort of find some stuff in here, right?
We find the max last activity date in this portion of the plan. And then we seek into one of our indexes here for the rows that we care about, right? So we get down to that one row max, and then we return stuff out. And this is a market performance improvement. It’s a little hard to tell because the last query just said 563 milliseconds. And it was very easy to see.
For this query, we have to go and find the max last activity date in this portion of the plan. And then we go into the query time stats to see this 69 finished in 69 milliseconds. So this was a pretty good strategy for this one. And you can even flip that if you need to find like mins too, right? So if we wanted to flip the order of this, and like rather than saying where creation date is less than or equal to subtracting nine years from the max, and then the last activity date is greater than or equal to adding 10 years to creation date, we could flip that to do a min on creation date and last activity date and kind of do that in here.
In this way. But my memory serves, this is basically the same plan. But yeah, so but well, actually, I guess this one is 54 milliseconds, but well, that may be 55. Let’s see. Let’s see what query time stats tells us. Ah, 56 milliseconds. So not so I would consider that 13 milliseconds, maybe some noise. Maybe even if that’s consistent, is 13 milliseconds worth all that flipping around? I don’t know. If you’re having the robots do it, probably it’s nothing happens. Anyway, your company’s paying for it. So again, coming back to the sort of ivory tower way of looking at things, store data, the way you query it, query data, the way you store it, otherwise, you’re gonna have to write some pretty weird queries to find performance satisfaction with things, right? At one point, Microsoft did make an attempt at this sort of thing. They added that date.
Date correlation optimization setting around 2005. But it never really took off. And the basic idea was that if you had two date time columns, and one of them happens to be unique and has a unique constraint or index on it, which I know you people, right? And you obey all the ANSI settings rules applicable for filtered indexes, computed columns, index views, and you create a foreign key between your unique date time column and your non-unique date time.
Then the optimizer would be able to figure some additional stuff out when you’re joining two tables together. So if you wrote a query like this that has all that stuff applied to it, SQL Server would turn it into a query that looks like this, right? It would add this date correlation stuff to it.
And it would say, oh, well, not only do I look this way, but I’ll also look this way. But the problem is that it did that by creating an indexed view for you in the background. And so it’s not terribly surprising why this didn’t go very far and get much traction with the general public. I think Fabiano Emerim is the only person who ever saw, like, really blog about it. But I guess the obvious thing, and this is something that we’ve talked about in many other videos, would be just to simply store data the way that you’re querying it, right? And just assuming that we don’t care so much about all the precision stuff that I talked about in other places, we would just create a computed column. And notice that computed column is just a column that does not have to be persisted in order for us to index it. And because all it produces is an integer, maybe, probably an integer, date diff result, it is deterministic out of the box. We don’t have to do any weird entangling of things in order to make this deterministic. And then all of a sudden, our queries magically pick that up, right? And we just say, hey, I know who you are, right? Unfortunately, I don’t think either of the fancier queries that we wrote pick up on that computed column, but maybe. Let’s just go and have a look-see. I don’t think it happens, but yeah, there we go. Yep, they just use the regular indexes there, which makes sense because the expressions that are in use here are certainly not anywhere near the expression that was used in our computed column, right? It would have to be far more precise than that. But our query picks up on it down here, uses our computed column. We don’t have to worry about the data we care about, and all is generally much better. All right. I hope you enjoyed yourselves. I hope you learned something. This is the Thursday video, so I will see you next Tuesday for office hours. And again, if you want to purchase the full course material that all of this stuff stems from, the link is down in the video below with a coupon for 100 bucks off. All right. Thank you for watching.
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.
Erik Darling here, with Darling Data, continuing somehow to record these videos despite the terrible heatwave that is upon us. This is not for the fragile. This is the second European mass die-off happening currently. Well, I mean, I’m in New York with air conditioning. Still not fun.
We’ve been state mandated to not have our air conditioners go above 78 degrees, but I don’t know. I’m out here breaking all sorts of rules. Anyway, in this video we’re going to talk about two problems that you will face when you need…
This is an introduction to the problem. We’re going to flesh more of the problem out in tomorrow’s video. But we’re going to talk about two of the things that you will run into issues with when you start trying to figure out the date distance between two columns.
And there are some matters of precision that need discussing. So, down in the video description, you may notice that this is part of a larger course called Learn T-SQL with Eric. You are seeing little sniffs and dribs and drops and drabs of the full course.
But you can purchase the full course for $100 off down below. You can do that. You can find the will and the power to purchase all the material.
And then you too can know as much about T-SQL and performance tuning as I do. You’re fully up to snuff on everything if you ever watch it. Purchasing is half the battle.
Watching is the other 100% of the battle. There are also other links down there. If you want to hire me as a consultant, you can do that. If you want to become a supporting member of the channel because you’re like, wow, Eric sure does give us a lot of free stuff.
I wouldn’t mind giving him four bucks a month. You can do that. You can also ask me office hours questions for free. And as always, please do like, subscribe, tell all your friends. Join monitoring mobile party.
Anyway, speaking of monitoring, important stuff out here. My free open source monitoring tool available up on GitHub. This link and other important links where you can acquire.
This free thing are down below. Totally free, totally open source. You don’t have to give me anything, pay me anything. I don’t look at your data because you’re not paying me.
If you were, it might be different. But it captures all the stuff that I look at and care about as a performance tuning expert in the world. So if you would like to get your hands on that, you can do that.
There are all sorts of great things that go along with that monitoring tool. But let’s talk about it. Let’s talk about our data problems here.
So we’ve talked about local variables. We’ve talked about date functions a little bit generally. In part two, we’re going to talk about things you might want to think about when you’re doing cross column date math. So we talked about like if you have a variable or a parameter, the thing that a lot of people mess up is they do the date math on the column and compare it to the parameter or variable.
We know we don’t do that. Flip that math because that’s where it’s better. I’ve created a couple indexes here just to sort of illustrate the problem a little bit.
One on last activity date, creation date. One on creation date, last activity date. And the point that I want to make here is that no matter what we do when we run this query, SQL Server has no choice but to fully scan that index because SQL Server has no way of calculating this currently, right? It has to evaluate every single row that goes in.
And SQL Server doesn’t keep any information about like, oh, this date is five days from this date or anything like that. Nothing like that is stored. These are like because SQL Server by default doesn’t like know what you’re going to care about with these two date columns.
So it’s just like, oh, see what they do. But so it’s up to you to figure out what you care about with these date columns. And it’s up to you to create computed columns that match the things that you care about.
But just on its own, the thing that’s important is that SQL Server has no idea what you care about. Okay. you will care about in the future right sql server does not create indexes for you aside from meager index pools and sql server does not pre-compute any things like this it is up to you to design your database in a way that gives you sufficient access to your data the very ivory polished way of saying this is that one should query data the way that it is stored and one should store data the way that it is queried so if we need to if this is a persistent calculation we might want to store this calculation some we’ll talk more about that tomorrow today’s video uh one thing that i think a lot of people sort of misunderstand is precision when it comes to this uh we talked i think we talked about this a little bit before but it’s worth repeating because most people do not spend much time thinking about this i’m going to run these two queries together they were made to be run together of course and uh we’re going to look at the difference here so this one is uh simply calculating the date diff in years where the difference is greater than or equal to 10. all right and this one is using a much higher precision this one is taking 10 years worth of seconds all right that’s a crazy number and saying give only give me these and what we’re going to see is that there is a big difference and uh just what what is a 10-year gap to date diff and what is this you know a big number of seconds gap right because that is much more precise right we’re going 10 years down to the second not just 10 years down to the year so when we look at the results in here this first result returns 643 rows and this second result returns 23 rows so there are only 23 out of the 643 rows that actually are 10 years down to the second that’s a very very big difference when you’re calculating these things and what i think you might notice here is uh if we look at the first result we will see that there is in fact a 10-year difference between all of these but the number of months even does not add up to 10 years worth of months precisely right we have 109 month difference until we scroll way down and then we finally get to a 120 and 120 month difference and the the day difference is also consistently just continuing to sort of rack up up as we as we look down through the results for the second result again only getting 23 rows this is down to the second 10-year difference notice that we start at the 120 month difference here and we start at the three thousand thirty six hundred well the even number would be uh three thousand six hundred and fifty right ten times 365 days or so right uh screw a leap year even though there’s maybe two of them in there i don’t know maybe one depending on if you get lucky but uh no there’s definitely two in there just where those two follow will be up to the gods uh but this starts right at where we would expect it to so this is down to the second and this is just down to 10 years right the two like the the date part has 10 years between one and the other but the number of months and days and all the other stuff may not be precisely 10 years so be very careful with how you frame your date stuff because you might have some surprising results in there and i think the the truly shocking thing to me is that a lot of the time when people um start seeing strange results in the date math they rather than up the precision of what of what the date diff is calculating they’ll start doing all they’re still adding and converting uh they’re they’ll start doing all sorts of strange date math on the columns to make it conform rather than just using a more precise date diff calculation it’s a very very strange where developer brains go in these things me i go that’s what i care about even in the heat where my brain is melting right so if we do care about 10-year precision then we would want to make sure that we are using the most precise measurement right all right thank you for watching i hope you enjoyed yourselves i hope you learned something i will see you in tomorrow’s video where we will talk further about the performance ramifications of these calculations and how you can avoid ramifications all right thank you for watching
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.
Erik Darling here with Darling Data and it’s hot and I don’t know it’s too hot to do this so you’re gonna have to forgive me if my answers today are a little sweaty because man we are we are dying out here in this heat but it is time for office hours where I answer five of your questions before going to the beach. I don’t know is there other beaches in New York? Maybe I’m just gonna go outside and drink who knows anyway it’s time for office hours despite the heat if you want to ask me questions for office hours you can do that there’s a there’s a link down in the video description that will allow you to interact with me in that way you can you can submit your question and I will answer it faithfully I don’t care what the question is whatever you put on the teleprompter I will read there are other helpful links down there too like say maybe I don’t know you want to hire me for consulting I will show up like this if you want you can buy my training where unfortunately that is pre-recorded and I was wearing my my formal adidas attire for all of those but it’s still a good time and there’s there’s discount coupon codes in the thing down there too so you’re really out of excuses unless you are absolutely destitute you can you can also like subscribe tell a friend become a supporting member of the channel you should know all these things by now but I feel like a lot of you just aren’t paying attention to the importance I feel like a lot of you skip this part I also have free SQL Server performance monitoring tool it’s up on github that that link down the video description amazing times are ahead with that I’m rebuilding full is a better thing and the the light version is really alive and kicking a lot a lot of good improvements just across the board and that thing totally free totally open source you don’t have to pay me anything you don’t have to give me your email address and I certainly don’t collect any of your data because I’m not looking at that for free that’s that’s stupid it’s just a bunch of t-SQL collectors grabbing all this stuff that a a seasoned intelligent experienced SQL Server performance tuner like me would be would look at it and I put it in the palm of your hand assuming that you have a hand I don’t I don’t know maybe going into the 4th of July weekend that’s always a little perilous to assume that everyone has has hands or maybe maybe after the weekend when you watch this anyway yeah let’s let’s go answer some questions because the the heat insanity prediction is coming true my brain is melting brain is just entirely melting here all right let’s see what we got here do you think a DBA should know version control eg git and how does it fit into day-to-day database work and on that note do you happen to have any kind of version control training or module in your courses so it is becoming well I guess it depends on what you’re doing as a DBA a lot of places now if you want to you know get a like fix in somewhere for a query or a stored procedure or anything like that it has to go through version control and testing and you know so it is probably a good idea for you to have some idea what you’re doing there but this is also a place where the robot friends can be very very helpful because the robot friends know all the git commands and and and they can they can really help you out help you sort out some messy situations that would otherwise be really really annoying for you you know so I should a DBA no git it really it depends on what part of the DBA thing you’re in like no one’s checking a backup into source control at least I hope not right that’d be a bad time like yeah what are you doing I’m trying to commit this full backup last week so depending on what kind of DBA work you do maybe but like I think maybe a good way for you to get started with this is to get started with this that start a private github repo and like if you have scripts or you have a store procedures that you use maybe put them in there and just like for yourself start working on like you know what what what what like pulling them looks like and what you know maintain it like like creating a forking or not forking but creating a branch and doing some work on that branch and then committing it and creating a pull request and pushing that and all that stuff just do some work on some personal things that you use maybe like you know you’ve got I don’t know some script that looks at index fragmentation because you’re that kind of DBA and you’re like ah I want to delete this because it’s useless no I’m kidding but like there are probably things that you use day to day that would be good to that you like you know would would probably get some some personal benefit from just you know practicing with you know github is free right you can get github desktop I use github desktop for for my stuff because it makes things nice easy buttons to push I don’t find there’s a lot of pain or friction in there using github desktop but I would really I would say that you should you should just start a private repo with github and just mess with that a little bit I don’t I don’t do any training on that because it is not my forte I am not by any stretch of the imagination an expert when it comes to that sort of thing but if you if you want to learn that sort of thing you can do that all right next question really enjoying bit obscene when’s the next episode out Bob Ward is a guest me Bob Ward is not coming on my goddamn show I’m lucky to get Joe Obish on there when’s the next episode I don’t know whenever Joe gets his air conditioning sorted out and can can be presented in front of people again that’s that’s that’s that’s my best that’s my best estimate Joe is having some air conditioning issues right now that prevent him from appearing anywhere all right what are the earliest warning signs of memory pressure before everything melts down ah so the first sign of memory pressure is usually not from queries though it can be but usually not usually the first sign that you’ll see is other memory consumers sort of fighting with each other so you have you have a database right and you’re you’re looking at it and you’re like wow I have what’s this to make put some nice round numbers out there let’s say you have 128 gigs of memory and you have like 512 gigs of data that’s not like that like the data to memory ratio is gonna throw things off and so the primary consumer of of your memory right now would probably be sort of like the bucker pool and when you see a lot of weights on stuff like page IO latch right that means you’re queries are constantly going to disk for stuff so that means there is an immediate memory pressure because you are not carrying enough memory to deal with the data that you are currently working with so the first thing that you want to look for is churn in the buffer pool that would be that would be that would be the first thing to look at because that is only gonna get worse as your data grows now you know for that specific thing of course there are various ways to deal with that you know archival strategies getting losing data right getting rid of data uh you might be missing some indexes that prevent like large object scans we don’t care about small objects uh you might look at uh compressing page compression page compressing your rowstore indexes that would be a good start uh and then you you might also um i don’t know uh consider getting some more uh but usually that stuff all starts ticking up way before you’ll see like query memory pressure problems where you’ll have like like the bad resource semaphore weights um but if you if you if you also see those then well it’s it’s it’s you’re probably already having that meltdown you’re worried about so that’s your fault there all right how do you decide coin tosses obviously uh when to rewrite a query instead of just adding indexes well there there are two things there um well one of them sort of depends on what the index situation currently looks like right uh if you’re looking at a table that already has a kajillion indexes on it you might consider something like maybe not just adding another because uh the the chances are at least okay that there is already already an index right there’s already already an index that might help you out along the way so maybe just don’t just just don’t just don’t just don’t just don’t just don’t just don’t just don’t just don’t just don’t jump to that. But if you have a table that’s rather bare and sparse and barren of indexes, then you might consider, I don’t know, adding one that would help your query. So I’ve done a bunch of videos on this sort of recently under the Learn T-SQL series of things where I talk about aligning queries and indexes. Sometimes you have no good index in place and there are query strategies like adding an index that would be good. And then sometimes you already have an index and your query is just not written in a way to take advantage of that. So if you’ve already got an index in place that would help your query, then it would be time to rewrite. But if you’ve got no indexes, then you must create at least one to help your query out. But the decision usually comes down to looking at the indexes and saying, oh, what do I got versus what are the goals of my query? Start with the where clause. That’s usually a pretty good place to start.
I’ve had to solve a dramatic number of query problems recently. With for sequence, that’s an interesting one, right? Because like, why is SQL Server choosing the scan? There’s an index it can seek to. And it’s like, I don’t know, because there’s like no rhyme or reason. Like I get costing, but you know, like, why are you doing that? And then SQL Server is like, well, I thought this scan would be cheap. And I’m like, cool, but you’re scanning 43 million rows. And you could just seek to 100,000 of them. And it’s like, oh, yeah, you’ve got a good point there, Eric. Thank you for forcing me to seek.
So that’s sort of that there. You know, if you’ve already got an index, write your query in a way that takes advantage of it. If you’ve got no indexes, well, perhaps then you create one, right? Seems reasonable to me. I don’t know. Why? Oh, this is a hum. Questions like this and answers like this are why Bob Ward is never going to be on Bit Obscene. That, I think, I don’t know. Perhaps Bob’s religious obligations would prevent him from being on a podcast. Sorry, on a radio program called Bit Obscene. But why does parameter-sensitive plan optimization sometimes make things worse instead of better? And the answer is because Microsoft completely beefed this feature. Man, they, like, it’s like clown walked up, like, pie to the face, spray bottle. It’s like Moe came up and just, like, Larry and Curly bonked their heads on something. But, man, they really screwed that one. So, like, right now you get three plans, right? I mean, just on the face of things. Like, there are all sorts of, like, ways you can get more plans. But right now, let’s just say for an equality predicate, you get three plan variants.
And the way things get bucketed, is you have the least common plan, you have the most common plan, and then everything in between. And I’ve shown examples of this in my other videos, where you’ll have, like, something at the very, so, like, I think it’s the votes table that I’ve shown with the vote type ID column, where the least common value is, like, 700 rows. The most common value is 37 million rows. So, they each get a plan variant. But the problem is that every, like, every time you get a plan variant, you get a three-row count between, like, 737 million shares the second variant, the plan two. And there are some very, very big swings in that data. You have some that are, like, 5,000 rows, and some that are, like, 3.7 million rows, and a lot of stuff in between. And the sharing in there is not good, right? So, a lot of that does depend on, like, which of the parameters gets fed into the variant two plan first. But it’s a bad sharing time within that chunk.
It just really messes stuff up. So, I was so excited about that feature, and then I saw it, and I was like, ah, yeah, complete screw job. But, hey, we got Fabric. That’s real groundbreaking stuff, right? If you ever want to know what Databricks looked like, like, seven years ago, try out Fabric. If it’s up, right? Who knows? It’s been down a lot lately. And, uh, not, not, really a lot of explanation of why. And, um, yeah. So, what a waste. Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I’m gonna go cool off now.
And, uh, I will see you in tomorrow’s video, where, uh, I don’t know. I don’t know. This might be too much clothes. We’ll see what happens. All right. Thank you for watching.
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.