Erik Darling here with Darling Data, and it is horrible once again here, heat-wise. Yesterday it was so bad that my thing actually disconnected from my phone when I was trying to record. So if the end of that video yesterday was a little wonky, now you at least have a reasonable explanation why. I’m managing, but just barely. I’m gonna take a cold white wine bath, probably Sancerre, Chablis, something like that, and then maybe just drown in it. Like a fruit fly, just drown. Like so many of the fruit flies I have envied drowning in a glass of wine in my life, that’s how I’m gonna go out. Anyway, we are going to finish, or we’re not finished, we still have a long way to go on this. But, the next thing we have up is, uh, answering, draining more of the office hours queue here. So, we’ll start off with, sometimes blocking chains disappear if we raise max stop. That feels backwards. Explain, please. Uh, well, it, most likely it’s just that some of your queries are finishing faster. I mean, uh, like I don’t, you didn’t really describe what, what sort of blocking problems you’re having. If you’re under the default read committed garbage, isolation level, uh, where read queries and write queries still fight with each other, then you, you, it very well could be cases like that where things are, uh, are improving and, uh, you know, you see your, your, your queries finish faster, so blocking goes away. For modification queries, of course, the, the, the, the right cursor portion of the query plan is always single threaded, except for in some limited insert cases. Uh, but, I suppose it’s also possible that the read cursor portion of those finish quicker, uh, with a more wide parallel distribution of rows, and that would explain it, part of it as well. So, that’s, that’s what I would go with there, at least, um, if, if I’m thinking about your question correctly, which hopefully I am. All right. We forced a plan in query store. Ah, this computer’s getting dim. Uh, and performance got worse.
Why would that happen if it was the fastest plan historically? Well, two things could be happening to you. Uh, one, uh, your, your, your plan could, uh, fail to force. That’s, that’s one. And two, uh, it might have only been the fastest plan historically under a certain set of circumstances that, uh, that are not shared by all the other, uh, query patterns, uh, that, that are now forced to use that plan. Uh, that’s something that has happened to me in the past that has bitten me, and it wasn’t fun. Uh, so, uh, so, uh, that’s most likely, uh, a parameter sensitivity issue and not just, uh, this query got a bad plan issue, right? So, uh, that’s what I would, that’s what I would hedge that one on.
All right. Uh, what plan operators usually signal something very wrong is happening here? Well, in a select query, uh, an eager index pool would be the first one that comes to mind. Uh, perhaps, uh, a lazy table spool, uh, would be another one that I would look at. Um, any sort of pattern where you have the constant scan, uh, merge interval thing happen that goes into a nested loops join, especially a serial nested loops join that usually indicates that you have a join with an or clause or perhaps some mismatched data types and, uh, correcting those, which I have many, many videos on would alleviate those plans. Uh, parallel merge joins, uh, order preserving operators, uh, order preserving exchanges and parallel plans.
Uh, that, that would include, well, I guess, sorts, uh, and stream aggregates. Never like to see those. Never like to see those. Uh, that’s, that’s, uh, top above a scan would be another one that I would, I would say rounds out that bunch. Those are, those are my least favorite things to see in query plans right off the bat there. Uh, let’s see. Uh, let’s see. Next up. Why do sorts suddenly explode in memory usage when data grows a little bit? Uh, could just be estimates. Um, could just be some, it could be the, uh, stats updated, uh, along with stuff. Um, cause you know, like data growing a little bit could still be influent, but could still require a lot of modifications to your tables where, uh, you know, you would see, uh, you know, stats get updated automatically. Uh, perhaps a stats update job. Um, yeah, that’s, that’s usually how it goes.
Uh, but you know, the thing that, that, that usually drives, uh, sort, uh, memory usage, you know, aside from the number of rows that you’re, uh, that you’re dropping, that you’re putting through the sort operator, estimating to put through the sort operator is of course the width of those rows. And I’m not saying that anyone has gone and changed the width of your string columns, but sometimes adding, uh, just a few, like, uh, like a more rows to the cardinality estimate will also serve to inflate, uh, the size of the data. The SQL Server estimates, uh, will be, uh, passing through the sort and could make memory usage, um, will explode to some degree.
All right. Last question here before I go melt into oblivion in my bathtub. Uh, is it possible? I hate the doors. I don’t want to die like Jim Morrison. Gah. All right. Forget that. It’s not going to happen. I’m going to figure out a different way.
Uh, is it possible to tune around terrible cardinality estimates or do you usually rewrite queries instead? What does tuning around terrible cardinality estimates infer if not rewriting the queries? Uh, you, you, you have asked a tricky question. Um, so like, I mean, I guess tuning around, I don’t know.
Are you updating statistics? Are you, are you, uh, are you, uh, creating indexes or creating statistics of some variety? Uh, are you forcing different cardinality estimation models? If not, if you’re not doing any of those things, I don’t know what tuning around this means if you’re not rewriting the query.
Uh, but you know, for me, terrible cardinality estimates, um, if you can, if you can root cause the terrible cardinality estimate, um, perhaps it is in outdated statistics issue. Perhaps it is, uh, the cardinality estimation model you’re using an issue there. Uh, perhaps you are doing something non-sargable or, uh, using some query form like a local variable or table variable or even table value parameters, which, uh, mess cardinality estimation up in slightly different ways. Uh, that was an all B possibilities as well.
Uh, and you know, uh, maybe you’re using a common table. A CTE, a popular blog, uh, table, uh, topic this week, uh, CTE not being so great. Uh, and perhaps, uh, you have some very complicated query in there and perhaps, you know, dumping stuff to a temp table is many SQL Server luminaries have suggested over this past week.
Uh, could be a viable exit from your terrible cardinality estimation problems. But, uh, your question, uh, as it stands, it’s quite an odd bird, right? Because I don’t, I still don’t quite get, how do you tune around terrible cardinality estimates?
Or do you usually rewrite the queries instead? That is going to twist my melon for the remainder of my days, which as soon as I figure out an alternative to drowning in a bathtub of wine will, uh, be shortly numbered.
Anyway, uh, the heat is getting to me. There’s a, there’s a moped gang going by and, uh, I, I, I have to go do things. So, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, which will sadly be my last one from Paris. And, uh, we’ll be, uh, uh, I’ll be picking up next week in, in sunny Portugal, which we’ll see, we’ll see how hot that is and how much my, my will to live is diminished by that heat.
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 is back to being a sweltering madhouse in Paris. And, uh, yeah, I’m having a real tough one today. It is brutally hot out there. And, um, as good as white wine is, it is not very hydrating. So, I’m just feeling a little weird. Anyway, uh, we’re going to continue to clear out, uh, the office hours, this cache of questions that have built up, uh, while I’m, while I’m traveling on vacation. Uh, we got a few more days left here in Paris and then off to Portugal and, well, uh, destinations beyond, as they say. Anyway, the first question of the day is, is TempDb contention still a real thing in 2022 plus or mostly solved? Uh, it can still be a real thing, but I, well, let’s do two things here. One, let’s hand it to Microsoft because they did, uh, really, cause they have done a lot of TempDb work over the years. Uh, probably most notably the 2019 feature, uh, the in-memory TempDb metadata. That’s good. I, I like that feature. Um, when it works well, it works well. Uh, I, I have run into some, I mean, I think earlier on I did run into some very strange, uh, like memory leak. Everyone says they think they found a memory leak in SQL Server.
I’ve, I’ve, I’ve run into a couple of real bad memory issues in SQL Server. Um, I had one client, uh, who turned it, had to have an availability group and I’m not saying the availability group is part of it, but, uh, I hate availability groups. So, uh, let’s let them share the blame a little bit. Um, then, uh, the primary node, uh, they had the in-memory TempDb feature enabled on the secondary. They did not. And every three, seven, some odd number of days, uh, uh, the, the primary node would, would, would perform it just completely eat, eat, eat, eat, cake, uh, and, uh, uh, performance with tank. And they’d fail over to the secondary. Everything would be fine for a while. Uh, and then they’d be like, ah, everything must be cool. Let’s fail back over to the primary. And then, uh, some modulo odd number of days, uh, would pass. And then, well, things would get weird there again. Uh, so I, I, like I said, I have run into some, some, some not great stuff with it, but you know, I, I deal with, uh, the strangest parts of the SQL Server universe. I do not deal typically with very normal things that most normal people would deal with. So I’d say for you, uh, yes, it probably is solved, especially with the in-memory metadata feature. Um, other people, they need some extra help. All right. Next up. Uh, how bad is it to rely on forced plans for months or years?
And it’s not bad at all. As long as they still work well, what are you worried about? Are you going to enforce them and deal with that hell again? I don’t know. It seems like a bad call to me. It’s not, it’s not how I would want to do things, but you know, maybe, maybe you live a little bit, maybe you sail a little bit closer to the wind than I do. I don’t know. Uh, it could be, could be a real wild and crazy kid over there. I don’t know.
Uh, let’s see here. Uh, well. Why does SQL Server sometimes choose a serial plan on a 32 core machine for huge queries? Well, he said, he said the word sometimes. So I’m going to assume that there are times when it chooses a parallel plan, which most likely means that there is nothing directly inhibiting a parallel execution plan like a scalar UDF or inserting into a table variable, uh, or rather a non-inlineable scalar UDF or, uh, inserting into a table variable or something.
So, uh, sometimes it, again, these questions, every time someone asks, it all comes down to costing. It all comes down to the optimizer costing queries and figuring out if parallelism would be a useful, uh, addition to the query. Uh, remember, remember that, uh, in order for a parallel plan to occur, a number of things have to happen.
Aside from, uh, just there not being any direct inhibitors to a parallel plan being used, uh, the, the cost of your query plan still has to break your cost threshold for parallelism. And then SQL Server has to land on a parallel plan that is cheaper than the cost of the serial plan that it began with. Remember, all plans start as serial execution plans. Parallelism is a choice derived later in query optimization.
So, uh, it, it most likely the cost just didn’t work out. Uh, the cost math just didn’t work out for your query to get a parallel plan sometimes. All right. Uh, next up over here.
Uh, when is batch mode actually a bad idea? Um, so I think, I mean, it’s bad idea. I mean, there are certain queries where batch mode is less useful.
I think one of the things that, that always gets me is, um, uh, batch mode bitmaps in row mode plans or in mixed, uh, like batch mode on rowstore plans. Because, uh, you see like a filter operator after a big clustered index scan and you’re like, well, that’s, that’s nice, but it didn’t quite work out. Um, so I, I would say like, you know, um, mostly with smaller queries where it’s just not necessary, uh, is where things get a little weird.
Um, plans that are prone to spilling, uh, on a sort maybe, uh, cause, uh, batch mode sort spills are, uh, just absolutely delinquent part of the engine. And I’m not sure why that hasn’t been fixed yet because it’s been a known problem for years. They, they are, they degrade like 500 times worse than row mode spills.
Um, so I don’t know. There’s, there’s certainly some times when batch mode is not the best choice. It was like a bad idea.
I don’t know. Query by query basis. Like, like, like a lot of the Mets lineups listed as day to day. All right. Uh, how do you, shut up motorcycle.
I think you’re so cool on your Parisian motorcycle. Uh, how do you identify memory pressure before the server completely melts down? Well, it might start with small clues.
I think probably the, the, the best sign of memory, memory pressure starting to, to line up, uh, would be in your wait stats. Uh, if you have lots of page IO latch, the IO is key in there, underscore SH or EX waits. Uh, that means that, you know, your server is already, uh, potentially under memoried.
Uh, and so when, when you see, uh, queries just routinely, uh, needing to go to disk for stuff, that is most likely a good indicator that some more memory would help. Of course, other things like indexing, compressing indexes, archiving data, stuff like that can, I’ll contribute, but, you know, um, in general, uh, you know, that, that would be a pretty decent place to start.
Apart from that, uh, you know, you might see either the resource semaphore, you might see the resource semaphore waits start to crop up a bit. Uh, those are always, uh, a potential thing to show in there.
Um, so, you know, that, that wait stats is usually where I would start there. Um, you know, especially if you, uh, have queries, like even if you don’t have the resource semaphore, if you have queries that are asking for very big memory grants, that memory has to come from somewhere.
That memory generally gets stolen from the buffer pool. So queries get more reliant on disk. So there’s lots of reasons why you might, um, uh, lots of reasons why you might start looking in wait stats initially to figure that out.
All right. I think we have one more here. Uh, can temp tables cause blocking in user databases or only in tempDB?
Well, technically only in tempDB, right? But, you know, most queries don’t run in tempDB. So if you have contention that is causing blocking in tempDB, uh, like on, on user, on, like temporary objects or whatever, uh, then queries in user databases will might be the ones getting blocked.
So, uh, it’s an interesting question though. Look, what is the locality of the blocking, right? Because the resource contention is in tempDB with the queries running in the user database.
There’s all sorts of interesting things to consider there. But, um, I think unless you’re using a global, global temp table, then, uh, that multiple queries might try to modify, insert, update, delete from, uh, then that would be a different thing. But, uh, if you’re using global temp tables, God, God help you.
You have, you have done something strange in your life. I don’t know that there is help for you. All right.
Anyway, uh, it’s, it’s too hot to keep this going. I need to go into the shade and I need to, to start paying attention to my glass of wine. So 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 continue our Parisian adventure, answering office hours questions. 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. It’s another wonderful day here in Paris, France, and I am coming to you from a slightly different window in the apartment. There are some windows where it would not be feasible to do any of this from, but we get a few good ones with rather nice views of stuff out there that, I don’t know, well, I get to look at and enjoy, so I’m sharing a little bit with you, but I’m probably not going to invite you over. Yeah, anyway, we got some questions to answer here from the fine folks out there in the SQL Server community. So the first one is, we see massive blocking chains during peak loads, but no deadlocks. Is that normal? Is something broken? And the answer is, I would say that’s fairly normal. Well, sometimes blocking does beget deadlocks. Quite often, blocking just happens rather naturally on its own without two queries getting into a fight over the same resources. Note that I said resources there because deadlocks can happen for far more reasons than the usual sort of embrace of death scenario like, you had to update table A, then table B, and this one tried to update table B, then table A. There are all sorts of things that are happening in the same way.
There are all sorts of weird things that can happen in there. Like even on the same table, it’s like you could see two queries deadlock if one tries to update index A and then index B, and the other one tries to update index B and then index A. And queries can even deadlock on themselves in a parallel query plan. Sometimes things get a bit mucked up in there. Most frequently around exchange op, well, I mean parallel deadlocks by definition happen around exchange operators. Usually when they are order preserving. So if you have a parallel query plan with merge joins or sorts or stream aggregates in there, they are more prone to parallel deadlocks. All right, next question. Sometimes CPU is low, but response times are awful. What gives? How does that happen?
Well, I mean, it’s something that I’ve talked about, I think, at least a couple times in recent memory here. You know, CPU being high is actually, I think, better because it means that everything is kind of running. I would take high CPU over low CPU and things being awful. Low CPU and things being awful could be any number of things. You could be looking at, you know, the usual blocking scenarios. You could be looking at threadpool. You know, you could be looking at your, I feel like I just answered this. Just ask me this question. You could be looking at things like your app servers being overwhelmed and just encumbered in ways that they were not meant to be.
So all things worth looking at. All right. Is plan forcing safe long term or just something to buy time until we fix real problems? So I assume by plan forcing, you mean query store plan, plan forcing, which in my experience is quite fragile and I would not rely on it long term. If that query ever gets a new query ID, you fail over, yada, yada, yada, yada. You are looking at plan forcing no longer working.
So from my perspective, it is a temporary bandaid. The one place where it is potentially not a temporary bandaid. And this would go for query store hints as well is if you are using an ORM rather than store procedures or even something like Dapper that allows you to write ad hoc SQL queries into a string. There are all sorts of places where I think link to SQL allows that as well to some extent. I’m not a developer, so I do not traffic well in these things.
But I would say it is a temporary fix unless you are in a position where from your position, you cannot change the code immediately. Query store plan forcing and query store hints are both rather fragile though, so be prepared to monitor that situation closely. All right.
Our app suddenly started timing out after a statistics update. Could a stats update actually make performance worse? Sure. There’s always that possibility. You know, I am in the camp that sticks at least pretty closely to up-to-date stats being the best way to allow the optimizer to make reasonably good choices.
Mostly because of the, like when you have data that gets added to a table and SQL Server does not have a representation of that data in the histogram, but users are querying that, the optimizer doesn’t have a really strong way of figuring out cardinality estimates for those off-road histogram steps. Other database engines like CockroachDB will do things a bit smarter. They do something a little bit more sophisticated where they’ll like look at like how many rows exist for like other values and be like, oh, well, I can infer, you know, cardinality will probably look like this for those newer values.
They project some stuff out. So SQL Server just, you know, on the legacy cardinality estimator, you get a one row guess and the new cardinality estimator, you get sort of like a density vector-ish estimate on the number of rows. It’s like 30% of the table or something like that.
But, you know, so like I do think that stats updates are important for those things. But I also do recognize that stats updates can cause issues in other places. For example, they might cause a plan flip where you get a worse execution plan afterwards.
That’s always a possibility. And, of course, if you run a stats update and all of a sudden a whole bunch of things need to compile plans and, you know, you don’t have a rather – no, I’m sorry. I was going on the wrong path of that.
And if you have to compile plans for stuff and you have a really highly concurrent environment, you can see a bunch of compile locks and all that stuff. So, you know, stats updates, they, you know, well, I do believe they are necessary. They can mess with stuff in new and interesting ways.
So, again, I do think you should update stats, but you should also monitor the situation closely. All right. Last question here.
How do you debug performance issues that only happen under real production load and not under the dev server load? Well, this is why I built a monitoring tool because all of the good stuff happens in prod, right? All the interesting stuff happens in production.
And, you know, not having active monitoring there, it leaves you just really blind. You know, the way that I try to tell people is if you can’t see a problem, you can’t solve a problem, right? These things need observability, you know, especially as DBAs are, I think, you know, shifting closer and closer to the SRE family of employees where, you know, you have DBREs and all this other stuff.
I think observability is far more important. You know, you’re not just checking on like, oh, did the backups finish? Did the maintenance finish?
Oh, do I have to patch this thing? Like you’re not doing just like dull ass production work anymore. You’re actually having to do like real stuff that, you know, keep servers up and alive and running. And, you know, having a problem and not being able to see deeper into that problem or have any record of that problem, you know, you might get notified of a problem far after it happened.
And by then, SQL Server may have been restarted and you may have absolutely no evidence of what was happening on the server at the time. So, you know, anything that you can do to increase observability is important for you because like setting up an extended event or something or a trace or something after the fact buys you nothing, right? Like you might catch the next thing if there is a next thing, but you still don’t know what caused the current thing.
So having as much observability into the current state of the database and current database problems, it just gives you such an advantage because you can not only have, you can respond, you can respond reactively more intelligently when you have that, that observability. But also it gives you the ability to, you know, just like forecast these problems, see what’s getting hot, see where, you know, see what’s really lighting up the server, you know, start fixing stuff before it turns into that. It’s just so important to have your SQL Server monitored.
And again, that’s why I built free SQL Server monitoring tools because, you know, I got sick of showing up to client sites and them not having a monitoring tool or them having a really crappy monitoring tool. I won’t name any names. You know who you are, every single last one of you. And like, just like, like, okay, like, well, you have a monitoring tool. Do you use it? No. Why? I can’t figure out what’s wrong.
Okay. Well, you don’t have a monitoring tool then, right? Let’s just over and over again. It’s that. So, you know, from my perspective, like I got real sick of that and that’s why I built my thing. It’s free. And there’s absolutely no reason not to use it. It’s for you to do your job better, right?
I ask nothing of you in exchange for using it except do your job better. All right. Anyway, that’s five questions. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. It wouldn’t be office hours without a siren.
And I will see you next Tuesday with another office hours video. All right. Thank you. 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 we’re coming to you from a slightly different Parisian window today where thankfully the temperatures have gotten back to a place where I no longer feel like I’m dying without an air conditioner. So enjoy the view a little bit. There we are. Look at all these wonderful French people doing wonderful French things. Anyway, we’re continuing to clear the air conditioner. clear out the office hours questions queue. And so that’s our job. And we’re gonna do it. Just because I’m on vacation doesn’t mean I’m not working. Do what you love and you’ll never take a day off in your life. Alright. First up, what causes SQL Server to suddenly change join strategies for the same query text? Well, could be any number of things. Alright. Usually all the same things that it always has been. SQL Server came up with a new query plan, perhaps because of a recompilation or what it would be not necessarily a recompile, but a fresh compile because like the cache got cleared or something. Perhaps some statistics updated. Perhaps we have a parameter sensitivity issue. But it is always one of those things, right? Something just changed and we had to we had to make a different decision for some reason. And here we are brand new query plan, brand new join strategy, sometimes better, sometimes worse, but always valid. That SQL Server always valid. You know, there are a number of things you can do to stabilize these plans. Like we talked about in yesterday’s video. I’m a huge fan of query hints. I think they are underutilized by people. I think people are unnecessarily afraid of them. But you know, you can always do other stuff too. You know, if you want to do, I wish, I wish the guarantees of query store plan forcing or hints were better. They’re not. Not that great. Yeah, so that’s not, that’s not really my favorite recommendation. You know, there’s plan guides, I guess, but really what it usually comes down to for me is looking at the two different query plans and figuring out if there is something you can do in order to get SQL Server to choose a more stable query plan naturally. Sometimes that is index changes. Sometimes that is query rewrites. But in general, that’s that’s the first place I go. And if that offers me no, that offers me no real help. Then I will help then I will go to query hints or something else. All right. When should you walk away from tuning and recommend redesign instead? Well, man, that’s a big one. So the thing with redesign is that that means I mean, I guess it depends on like what at what level we are redesigning. Are we redesigning a wide table to normalize it? Are we redesigning some something else about the way that we’re storing data to make it easier to access the data paths that we need?
You know, for me, you know, for me, it is usually a question of will that redesign take longer and benefit the general performance characteristics more than me just pulling the data that I need out and into the format that I need along the way? Temp tables are a great way of doing that. You know, so I don’t think that I would ever walk away from tuning. I think that the two tasks really have to be done in parallel. Because when you have when you have when you have like performance problems across the board, you need to do something to alleviate them immediately.
But you also want to take the proactive and take good long term steps so that, you know, you like you don’t have to keep doing all the other work to move data around just so a query can run your query should. Your query should, you know, again, the polishing ivory that I usually do here is, you know, store data the way you query it and query data the way you store it. If you have to keep changing data in order to get it out, perhaps you should start storing your data in a format that is more aligned with the results that you are returning.
But this is specifically geared towards like when I would recommend to redesign, not just like, you know, go ahead and start changing tables and doing whatever you want. When I get it willy nilly because that’s that’s painful and often requires application changes. If there’s one thing I know about DBAs, they don’t make a lot of application changes.
So that’s that’s that’s what we got there. Let’s see. Why does SQL Server sometimes just stop like everything queues up for five to 10 seconds and then magically recovers? Well, there’s a there’s a lot of reasons for this.
I mean, the obvious one is locking and block. It’s this usually comes down to resource contention. It’s in it’s someplace along the pipe. So the most common one, especially if you’re using the God forsaken garbage isolation level read committed, locking and blocking between read queries and write queries, I’ll absolutely do it. More severe ones would be things like threadpool waits running out of worker threads, resource semaphore running out of query memory grants, resource, query memory grants space, resource semaphore query compile, or even just beyond that compilation locks.
Like if you’re waiting a long time on like synchronous stats updates and like the same procedure needs to compile a plan across like 100 sessions, that first session sitting there waiting on the stats update and then the 99 other ones are waiting for this one procedure to cache a plan so that they can all use it. That’s another gnarly one.
There’s all sorts of stuff that could do it. Even if you don’t hit threadpool, you can get pretty backed up on just like runnable queues. Like, you know, queries have four different, let’s say four different states in SQL Server.
They’re running, they’re on a CPU, they’re runnable, which means they could run if they could get CPU attention. They’re suspended, which means they’re waiting on something that’s not CPU or they’re sleeping dormant, whatever. You know, if you can stack up your runnable queue pretty high even without hitting threadpool waits.
You can see that a lot when people mess with the max worker thread setting, which is kind of a goofy thing to do for most people. I would say maybe don’t run and do that first. Like that’s maybe not my favorite setting for people to change.
Alright. Query store says average duration is low, but P95 is horrible. Which one should I care about more?
Hmm. Uh, well, that’s an interesting question. Uh, I don’t know if 95% of your queries seem to be unhappy for some reason. Uh, then that’s probably something worth looking into.
Um, the thing with, well, I mean, actually I don’t even know. Cause, cause like it’s just like a query store should have a pretty good detailed query history. And so you should be able to ascertain mostly that like average duration is not good for something.
Um, I would, what I think, so if you’re getting, I would, I would wonder where you are getting your P95 from. There are lots of monitoring tools out there that are not quite as granular as, uh, query store is. Uh, Datadog being one of them.
Datadog is highly sampled and highly aggregated data. And they do not exercise, uh, their data collection across many of the sources in SQL Server that you would expect, uh, query sampling to go from, to, to come from. Not like DMV wise, just the columns and the DMVs.
Um, they do a lot of sort of like averaging over totals. Like I think they only collect total. I don’t think they collect like mins, maxes and stuff. So I would really wonder where you’re getting your P95 numbers from if query store is saying that average duration is a-okay.
Uh, so I would, I would wonder, I would wonder a little bit about that. Uh, let’s see here. We got, that was one, two, three, four.
We got one more. Oh boy. All right. Last one. And then I will, I will give you another view of Paris and then I will say goodbye. Uh, uh, I see memory grant feedback kicking in over several executions, but performance still sucks. Is that feature actually reliable?
Well, it has gotten more reliable. Uh, but if memory grant feedback kicks in and does stuff, uh, perhaps it is not memory grants that are your problem. Your problem could lie elsewhere.
Uh, you know, it, it is, it is amusing how people think that these features, uh, individually will, will just solve all their performance woes and worries. But, uh, it’s not, not often the case. Um, you gotta, you still gotta do some work once in a while.
So what I, what I would look at, I mean, you could look at like what memory grants are affecting about the queries execution. You know what I mean? Like if you’re, if you get to a stable memory grant and things are not spilling, then that’s cool. But you’re, you know, there’s still a million other things that can go wrong on a query plan and you should spend some time looking at what those million other things might be.
Because memory grants are just one very, very, well I wouldn’t say one, one very small, but they are certainly one, uh, piece of the puzzle that make the entire query performance picture whole. Alright. So here you go.
Here, here’s, here’s Paris. This is, well this is sort of what I get to wake up and see every morning. Uh, it’s pretty nice. Uh, I like it. Anyway, uh, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something and I’ll see you in tomorrow’s video where we will clear out more office hours questions. Uh, they, they, they really piled up. Uh, and the, the once a week thing was not clearing out the cubes. Uh, you know, anyway.
Alright, 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 today’s video is coming to you from Paris, France. Be here for two weeks with the family. Sounds just like New York out there. And then we will be popping around Portugal for a couple weeks. So I’m just going to use this as an excuse to clear out the office hours question list a bit and hopefully, well, I don’t know. hopefully maintain my high-quality SQL Server output for everyone. It is very, very hot here in Paris today. It is like 96 degrees and there is still no such thing as air conditioning. They have still not invented that yet. I don’t know who is in charge of Paris in this round of civilization, but I wish that they would hurry up and invent air conditioning because the people suffer, your majesty. So the first question that we have is, is there ever a case where hints are the best long-term solution? And the answer is yes, absolutely. Despite SQL Server being a highly advanced LLM, it takes query text in and spits out query plans. It still can’t figure everything out in one go. Sometimes it just needs a little bit of extra help along the way. So I would highly suggest that you, if you’re running into a problem and it feels like only hints can fix it, use the hints. That’s what they’re there for. They’re not just there as a cautionary tale in the docs for people to only certified database administrators can use hints. It’s silly. Anyway, I use hints quite a bit for everything from, you know, fixing production problems all the way down to just sort of like experimenting with different queries to see what different query plans look like and how things get costed.
At the very least, at the very least, they are an educational tool, but I find them quite handy for fixing all manner and variety of issues that would otherwise just not work properly. So there’s that. All right. The next question is, why do some servers feel slow even when CPU, memory, and IO all look fine? The two biggest things I find, it’s either going to be network related, like the results take a long time to get from the server point A to point B, or it is going to be something on the application or web servers that are receiving your results and maybe taking a long time to process or render those.
That is the two most common things that I see aside from anything, you know, let’s just say SQL Server side with something easy like blocking, right? Queries taking a long time to compile, you know, just like, you know, stuff that is not going to be like, hopefully not going to be the normal state of affairs. I guess locking, you know, SQL Server, sure. Yeah.
But, you know, the compile thing, that’s a little outlandish. So don’t, don’t, don’t run off and go tell everyone I said that. All right.
What parts of execution plans do people obsess over way too much? Oh man, it’s always costs and percentages. Those are the things that people will not let go of. I don’t know who told them, who taught them that costs were real things, that they were some sort of durable performance metric, but alas, they’re not, they, they just very misleading at the best of times and as complete liars at the worst of times.
So I think that that’s probably the thing that I would stick with there. Aside from that, you know, I would say individual operators, people hyper-focus on scans. That’s another thing.
I don’t know. Probably about it there. Mostly it’s costs. That’s the, getting people to stop paying attention to costs is far and away the most, the most uphill battle that I fight day to day.
And it’s not just with people. It is also with the robots. That’s why in Performance Studio and the Cloud Query Plan plugin that I built, like it is littered throughout the instructions to not look at costs, right?
If someone gives you an estimated plan, ask for an actual plan. If all you have is an estimated plan, there are like, you know, a variety of query plan patterns that you can look at, but always make sure that it’s grounded and you need to get an actual plan to figure out what’s actually slow.
So, all right, let’s see. Question number four here. What mistakes do people most often make when trying to fix parameter sniffing?
Optimize for unknown hints or local variables. That is it right there. I see people do that all the time, and I guess maybe it gets rid of parameter sniffing, but often the plan that you get is not the most high quality of execution plans.
That’s an easy one. That was like a ringer. Ah, man, I feel a little guilty about that. Why do some deletes take forever even when only removing a few thousand rows?
Oh, God, because you probably have way too many indexes that those deletes have to bounce around in. I mean, perhaps blocking, right? That’s always a thing that could happen.
But usually when a delete takes a very long time, it is because whether the plan is a narrow or wide index plan, it is most likely that you have a buttload of indexes, and all of those indexes have to be deleted from. You’re looking at ordering data stuff that has to go with sorting the data by the index key so you can delete from it, all that stuff.
That is far and away the most common reason why. So, yeah. Well, that or maybe you don’t have an index that allows your delete to find the data that it needs to delete in an efficient, timely manner, and that’s what’s messing you up.
All right. That’s five questions. It’s hot, and I need to go take my third shower of the day. I will not be live streaming that. This is not the time.
This is not the channel for it. But thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video, which will also be office hours, but hopefully a less hot office hours. 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, we’re going to talk about mostly AT TIME ZONE performance, but also some other interesting stuff about AT TIME ZONE. I’m going to be real honest with you, I hate timezone stuff. I hate all of it. I’ve never taken naturally to it. It’s all too weird, and managing it is just such a nightmare. in UTC. Store someone’s timezone, I guess, and deal with it from there. As soon as you have to start mixing things, it gets awful. The absolute pits. Down in the video description, you will find all sorts of helpful links. You can hire me for consulting. You can buy my training. You can even buy the full version of the training that you’re seeing in this video with the coupon code down below. You can become a supporting member of the channel if you feel that the content that you’re getting here is worth $4 a month. And of course, you can continue to ask me office hours questions, and please do always like, subscribe, tell a friend, maybe even several friends if you’re poly-friendulous or something. If you’re in the market for free SQL Server monitoring, you can get mine. Totally free, open source, no email, no phone home, no weirdness. Just a bunch of awesome data collectors getting all the important stuff from your SQL Server, and giving you all the information you need to fix your, well, find and fix your performance problems. Recently launched, recently got some AG monitoring in there, and also recently went Enterprise Edition, and we’ve now got a headless Windows service. You no longer have to worry about not monitoring stuff if the app I still have those. I still have that version, but it’s a headless Windows service backed by a Postgres database. You can monitor hundreds and hundreds of servers for free with it. Gosh, is it pretty darn good. All right, let’s, let’s T-SQL ourselves here. So, at time zone has a lot of weird performance problems. Not weird, very obvious ones once you start looking at it. Basically, it’s bad if you do something like this, and it’s good if you do something like this.
Right? Do, don’t do your date math on your at time zone on a column, because you will beat the crap out of your server. Right? So, I’ve got query plans turned on, and I’m going to run both of these. And I suppose the fun part is that until they finish, we won’t know. We won’t know which one is our problem here. I guess we will, because the results, I lied. So, in this query right here, we are having our where clause where creation date at time zone, Eastern Standard Time is greater than D. And I’ve got even got a recompile hint on here. So, SQL Server, there’s no statistical mystery there. And in this one, I’ve changed it so that I’m just using a time zone that sort of reverses the math, right? So, the minus five UTC to plus five, or actually, depending on what time of year it is, I guess it could be fewer or more hours. But the first query that we see scans the entire clustered index takes 10 seconds doing that, right? And part of that 10 second scan is at time zone calling out to, like, Windows to figure out time zone stuff. Everything, every time is a Windows API call. Right? So, like, it’s tremendously slow there.
And this one is better, but it’s not the plan shape that we want. And it’s not the plan shape that we want, because we were slack with data types, right? Because when you add time zone something, what happens? You don’t, like, so, the creation date column in the comments table is a date time. But when you do this, you add a time zone to something, guess what happens? That’s not a date time anymore. So, we get this query plan shape that I’ve talked about before that happens when you are slack with your data types, right?
So, you would want to write your query like this, and you would want to convert the result to a date time, and then you get the nice simple seek plan like this, right? So, if we just converted to date time without the switch offset here, we’d lose the offset information and the time range, right? And we don’t want to do that, because then we’d get wrong results back.
So, the wrong result would look like this, right? Because if we just say convert date time at time zone this, we lose this, right? That’s the five hours that we want from there.
But if we do the switch offset, use the switch offset function, then we keep that, right? Switch offset adjusts the time zone offset while maintaining the point in time that your data is in. The underlying UTC stuff all stays the same, and it’s really useful for normalizing data to UTC or displaying your user’s local time zone, I guess.
You can even combine it with two date time offset when you need to attach an offset to a date time value, right? So, for our purposes and illustrating that a little bit, this is what we started with, right? If we send that, if we say, if we just add time zone that, then we get the time zone added, which takes five hours, right?
And we switch the offset, we get the five hours in here, and then when we convert that to a date time, right? We lose the time zone portion from these two, then we get the correct time from that. So, just a couple notes about that stuff.
I’ve got a few more videos that will go over time zone things and hopefully help you, because I have just struggled mightily with time zone stuff over the years, getting it to work correctly and getting it to perform well.
So, hopefully, the upcoming videos will also help you if you are also struggling with those things. 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, enjoying a lovely LaCroix Pamplemousse seltzer. No booze in that whatsoever. Can you believe how lucky we are to enjoy a non-alcoholic beverage? Anyway, we’re going to do some more Learn T-SQL with Erik. In this video, we’re going to talk about cross-table date math, and I am going to catch myself and a funny little bit of irony in this one. Down in the video description, if you would like to purchase the full training content, there is a link with a coupon code attached to it down in the video description. Just below me here, just below this lovely fold here. There are also links for you to do other things, like hire me for consulting, become a supporting member of the channel, ask me Office Hours questions, and you can also, while you’re down there, like, subscribe, and tell all of your friends. Your beautiful, wonderful friends who would just benefit so greatly from watching these videos. If you are in the market for free SQL Server performance and availability group recently monitoring, you can get mine. There’s also links for this stuff. Totally free, totally open source, no phoning home, no weird stuff.
A brand new sort of version of the monitoring tool is available. Let’s go to Postgres and Timescale backend, and it has a headless Windows service, and it can monitor up to 500 servers, again, totally for free. It doesn’t cost you. It doesn’t cost you more if you want to monitor more than 500 servers. I would just maybe start a second install to monitor the other 500, because concurrency for that many servers, even in the wonderful world of Postgres, is maybe not so much fun. But, but let’s talk T-SQL here. That is, that is the turkey that we care about.
So, so we’re going to use this query, which, if I remember its provenance correctly, came from the Stack Data Explorer site. I can just never find it when I go look there again. But it’s, it’s, it’s, it’s the intent of the query is to find posts that had a lot of very early upvotes, and this query was always very slow, and to me, the interesting part of the query was that the where clause was looking for a date diff in columns on two tables. Now, under normal circumstances, if you had both of these columns in the same table, right, you could, you could, if you were denormalized a bit, but this would be a terrible denormalization.
You could create a computed column, but them being cross table, you don’t get that. SQL Server 2005 or so had this feature called the date correlation optimization, which could help in cases like this, but it required a lot of stuff, like, like a unique index on one of them. Which you can’t always get, and a foreign key between the two of them, which you can’t always get, because your data is filthy and cackadoodoo dirty because of your, all your nolak hints and whatever.
I don’t know, whatever. Let’s just go, go along with it. It’s funny.
So, it would be very hard to get that set up, but you, nothing is stopping you from creating an indexed view that can do the same or a similar thing. Now, this query runs for about, oh, five, six seconds or so. Oh, we got, we got a big five and a half second thing there.
And, you know, like one, like, this is not the thing I wanted to look at. It was being very silly. But, sort of, like, the important thing here is that, like, you know, you can, like, there’s only so much you can filter until the tables kind of get joined together, and you can finally apply, like, some of those filters, like the date diff one here, right?
The date diff in hours is less than or equal to 168, which is seven days, right? So, you have to, like, get all the rows from both tables and then do the join, and then you can start doing other things. And then you can start filtering your results to where they’re greater than or equal to 50.
And that’s fine, but, you know, if you want this type of query to be much faster, there are some things you have to do, right? So, you know, like, you might not find it very useful to seek to 40 million rows. I know I sure wouldn’t, but this is the way the votes table kind of breaks down.
So, you know, like, anything in here is going to not be fun. Like, nothing about this is going to be enjoyable. So, what I would recommend here is, especially if you are not in a situation where you can get the date correlation stuff working naturally out of the box, one thing I just want to point out about the original query, I’m going to make you sit through about five and a half more seconds of runtime here, is that, like, the majority, if not the total of these operators, aside from gather streams, are all happening in batch mode already, right?
Like, this is a nearly entirely batch mode query, and so, like, you’re already getting a lot of, like, the oomph that you would get out of this. You know, like, I guess if you want to add a non-clustered columnstore index there, you could, but we’re going to look at a slightly different way of handling it.
So, let’s come on down here, and what we’re going to do is we’re going to take a portion of the query that makes for a good indexed view. It’s not the full query, because I’ve talked about in other videos where we talk about indexed views. Making overly specified, overly specific indexed views will largely lead to those indexed views not being used by queries that could potentially benefit from them.
So, looking at all the, like, this is just, like, the base query that I would start with in here, and the idea behind it is just to get this part sort of squared away, right? So, we’ll create our unique clustered index as required in order to create other indexes.
And then there’s a neat trick with indexed views. Now, the indexed view documentation has some stuff about columnstore indexes. You can obviously not create a clustered columnstore index on an indexed view, because you can’t create a unique one, and the indexed view needs that.
But you can create a non-clustered columnstore index over an indexed view, and that can sometimes get you a very, very good mix of both the pre-aggregate and the batch mode that you would want. Did I actually run that?
It just happened very quickly. So, this all happened, it all happened in the blink of an eye. And now we can sort of, we can run this query, right, where we can get our sort of pre-aggregated list of stuff from the indexed view that has a count big of greater than or equal to 50, just like our original query.
And then we can join to our, just those rows to our query, and we can get ourselves a wonderful result that goes a lot faster. This all finishes in, well, because this is all happening in batch mode, we have to, and there’s no parallelism, we have to go look over here, and that the query time stats, and we’ll see this finished in 500 milliseconds of wall clock time with 460 milliseconds, 462 milliseconds of CPU time.
So, if you’re in an environment where these types of queries really have to be front and center fast, indexed views can go a long way to making them much, much faster. There are, of course, many caveats and drawbacks to indexed views.
Maintaining them on modification can be somewhat painful. And for this one specifically, which is something I’ve talked about many times in many other videos, when you have a cross-table index, when you have an index view with a join in it between one or more tables, not lock escalation, but isolation-level escalation can get pretty out of control.
You’ll also end up seeing a lot of serializable stuff that you didn’t ask for. SQL Server just does it automatically. So, you do have to be pretty careful and, you know, pretty conservative with how many indexed views you’re using and all that stuff.
But under the right circumstances, and especially if you start getting real crazy and creating non-clustered columnstore indexes on your indexed views, you can do a lot of really, really neat tuning stuff if the workload demands it.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll purchase the full course that this was just a tiny, itty-bitty little taste of and, yeah, all that stuff.
All right, cool. 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.
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.