Category: SQL Server

Parameter Sensitivity Training Part 1

---

Video Summary

In this video, I delve into the intricacies of parameter sniffing and parameter sensitivity in SQL Server. Starting off with some humorous self-deprecating remarks about my past Data Community Summit 2025 presentation, I aim to make a complex topic accessible and engaging for viewers. We begin by defining key terms like parameter sniffing versus parameter sensitivity, explaining how the optimizer uses parameter values for cardinality estimation in busy OLTP systems but highlighting where things can go wrong when queries get compiled with skewed data or variable runtime conditions. Throughout the video, I use practical examples to illustrate these concepts, such as comparing execution plans and discussing local variables vs. formal parameters, all while providing a clear explanation of why parameter sensitivity is a critical issue for database administrators to understand.

Full Transcript

Erik Darling here with Darling Data. And I’m going to spend the next few videos doing the material from my past Data Community Summit 2025 regular session on parameter sniffing. And hopefully you find it as enjoyable as the millions of people who showed up on Friday morning to hear me deliver this. So we’ve, we’ve, you, you, you’ve got to see it. We’ve got that going for us, don’t we? Anyway, uh, welcome to your HR mandated parameter sensitivity training. I’m Erik Darling and blah, blah, blah, all this stuff. If you’re watching this, you’re at least moderately aware of some of this material. Um, I will try to remember to put the link to, uh, the thing in here, uh, but I make no promises. And if you don’t like it, I don’t know, leave. Um, but we’re going to start by defining some important terms here. Uh, the first one being, uh, sort of differentiating between parameter sniffing, uh, and parameter sensitivity. Uh, so parameter sniffing is when the optimizer uses values assigned to one or more formal parameters for cardinality estimation, right? And this is a thing that it, you know, I, I appreciate that it does because I work with a lot of OLTP systems that are pretty busy.

And if SQL Server were coming up with execution plans, every time one of those little tiny queries came in, um, I think things would go a whole lot less well. It’d be like, I don’t know, some, some hit every time the, the, the, like a query opened a connection and ran something. Um, if you would prefer that behavior, you can go use Postgres, right? Because there’s not really a plan cache there, right? So this is the generally okay thing that SQL Server does. Where it gets a bad name from is, uh, where it gets a bad name from is, uh, when you have a problem where a query gets compiled with some parameter or set of parameters and the cardinality estimates that produce a cached query plan for re-execution, uh, do not really fit the mold for, uh, what the current runtime set of parameters is looking for. Uh, so this is, this is what the sensitivity part is where, uh, you have skewed data or perhaps your queries are, uh, either like you have like actual physically skewed data in an index, right?

Like one value accounts for like, like a lot of rows in your table and other values account for very small numbers of rows in the table. That’s data skew. You could also expand that to be like, okay, well, you know, uh, I have a date range and sometimes, uh, or rather most of the time when I’m searching that date range, it’s like the last like hour or 24 hours or I don’t know, week or something.

And then every once in a while I have to search for the last like six months or six years of data. And all of a sudden, uh, that, that breaks the mold, right? Cause like, you know, searching for way more data within that range, uh, the query plan for searching for like this much data as usual, like those two things conflict, right?

That’s not, that’s the thing that we have to deal with. That’s the problem that we need to focus on today. There are also all sorts of general performance issues, right?

And a lot of these things can sometimes feel like parameter sensitivity, you know, sometimes, uh, you know, you deal with more data in a temporary object than others. And maybe there’s a plan reuse thing in there. Uh, there’s all sorts of other stuff that you can do that just craps all over your queries that is not parameter sniffing, but often people, you know, the, the first thing they’ll blame is like the most convenient set of words that they’ve heard about performance problems.

And like, like, I want to sound smart and be like, Oh, might be parameter sniffing. Right. So like, you know, look, SQL Server has a lot of features, right?

And, um, you know, in a database, it is real easy to shoot yourself in the foot, uh, with performance problems. And a lot of the features that SQL Server has really just make it harder to miss. Right.

So like just very good aim with some of those performance, with some of these features. So, uh, the first thing we need to do, aside from wait for this green screen to come down, I don’t know what your problem is. Sometimes if I lean away from it, it goes away.

Uh, the first thing we need to do is separate queries that are sometimes slow for other reasons. Right. Like say sometimes a query gets blocked and people are like, why is this running for 10 seconds? And it’s like, well, cause it got blocked for 10 seconds.

I mean, as soon as it, as soon as it was unblocked, it was fast. Uh, other times you might run into something that’s slow because you don’t always read data from memory. Sometimes you have to read data from disk and sometimes that is uncomfortable.

So just for example, uh, if we run a checkpoint and I drop clean buffers here and, oh, I’d must have all, must’ve had a big buffer pool on that one. Uh, sometimes, uh, you might run into a situation, make sure a query plans are turned on here. That’s a good first step.

Uh, where, you know, you run a query and you’re like, wait a minute, is this parameter sniffing? Right. Right.

Cause I am using parameterized dynamic SQL here, but did my parameter get sniffed? Was something bad? And the answer is no. Sometimes you get stuck reading pages from disk. So these two queries obviously have time differences between them.

Right. Uh, almost like 10, is that 10 X or a thousand X? I’m not, I don’t know. Let’s just say a thousand X, right? Right.

600 milliseconds versus six and a half seconds. But the whole problem is that this query had to read data from disk. If we go look at the weight stats, I don’t know how I’m going to manage this one. If you go look at the weight stats for this, this is a lot easier in person because I could, I can move things around a lot.

But the weight stats for this indicate that we spent, uh, 41 seconds reading pages from disk into memory. Right. So in this parallel plan, all eight threads had to go out to disk and read stuff.

Right. And we did not have a good time with this. But this isn’t parameter sensitivity, right? This is more like environmental sensitivity. This is more like your SQL Server can’t cache the data that it needs.

And maybe it can’t keep the buffer pool full of the stuff that it needs because you don’t have enough memory or because you don’t generally have good indexes. Or maybe you write queries in a really crappy way where you can’t like, you know, seek the stuff and you have to scan all your indexes. There are all sorts of possibilities for this.

Um, page compression is a great way to start fixing this too. But there are all sorts of reasons that might, that might be the case. But this is still not parameter sensitivity. Right.

Because that, like that query, that second query ran and it reused the same plan that the first one had. But it was a lot faster because the data was already in memory. So, um, what kind of code is parameter sensitivity?

Is parameter sensitive, right? Yeah, words. Uh, so it usually touches table with skewed data in them, right? So, like, you know, uh, I think, uh, if you’re working with the Stack Overflow database, um, you know, John Skeet would be an indicator of skewed data, right?

Uh, user ID 22656. Uh, lots of rows in the post table. Lots of rows in the comments table.

Um, and so his user ID leads to a lot of interesting problems. You can also run into it when, uh, you know, like not with the, like with things that are like inequality predicates where sometimes they may find way more data than others. Right?

So, like, you know, it’s really easy to imagine that with a cache plan using something like this. But you can also run into that with like predicates. You can run into that with in and not in stuff. So there are lots of ways, lots of places you can run into skewed data.

Now, I think like is a particularly fun one. We’ll look at an example with that in a few. But other things about it is you will be using formal parameters and not local variables. Right?

Local variables do not have parameter sensitivity issues because their cardinality estimation. is treated much differently from that of formal parameters, literal values, and other such things. Um, we’re not too concerned with the concept of simple parameterization here.

Because it’s really only used for queries that qualify for what’s called a trivial plan. And trivial plans do not really have any cost-based choices. Having cost-based choices to make is where the parameter sensitivity stuff really kicks in.

Because SQL Server made choices based on cost from one example of cardinality estimation for a set of parameter values. And then reusing the plan for a different set of parameter values where the costing would maybe work out differently because of the number of rows involved. That’s where the sensitivity end comes in.

So if we look at execution plans for these two queries, right? We’re just going to get a count from the user’s table. One of them is for reputation two and one of them is for reputation one. I have literal values in my query, in my, both of these queries, right?

But if we run this and we look at the plans, then of course we have to move some stuff around. And actually, no, we can just use the arrows, I guess. Thanks, SSMS.

Thanks, SSMS. Making me do more work. The last time I upgrade you, you fiend. So if we look at these, you see that SQL Server did, like, replace both of my literal values with this at one parameter. And that means that SQL Server gave a, like, was like, hey, this plan seems pretty trivial to me.

And then SQL Server also said, and I think I can apply simple parameterization here. And so it did. And so we end up with this.

And we can also see some artifacts of that because SQL Server did cardinality estimation for reputation equals two. And I guess it was pretty good. It was a pretty good guess, right?

We were only off by, like, 190-something rows or, I don’t know. Doing math on the fly is not my specialty. So this was pretty close, right? But then down here, you see SQL Server reused that same cardinality estimate of 1770.

So SQL Server parameterized, cached, and reused my plan for these two queries with literal values in them. So using literal values does not always insulate you from these types of things. But often the queries that get this are so simple that we’re not really worried about, you know, big performance problems with them.

But anyway, the two most common vehicles for parameterized code in SQL Server are, of course, store procedures and queries that are executed from an application using SP Execute SQL. SP Execute SQL is a store procedure. And when you execute dynamic SQL with it that contains parameters, you are essentially executing parameterized code.

All right. But we’re going to talk a little bit about the main distinction between formal parameters and local variables here. Because sometimes the local variable thing matters and sometimes it doesn’t.

I’m going to start these two queries off running. And then we’re going to talk a little bit about that. Oops, that was supposed to. Oh, that was two queries. Sort of jumped around on me.

So the thing with local variables is, and, you know, I’ve talked a lot about this and written a lot about this. But the main thing with local variables is, like, it’s very easy to come up with a demo where there’s one thing in it that’s a local variable and that local variable screws everything up. In real life, often the times when I see code that uses local variables, the local variables aren’t the only thing in there.

Right. Right. So when you look at, so, like, if you’re using local variables, sometimes it’d be, like, other things in the where clause, like other, you know, search arguments in the where clause that might sort of tame the effect of bad cardinality estimation from local variables. I’m not saying that it’s okay to use them all the time.

But I am saying that a lot of the times, you know, you might have other stuff in the code that sort of hides or, like, sort of, like, smudges over a little bit about how bad those estimates can be. And other times you’re looking for, you know, such small amounts of data that it doesn’t matter. This is an example of looking for when it can matter and when it actually doesn’t matter.

So the first time that we run this, we’re looking for, we have a local variable where parent ID is zero. And the second time that we run this, we’re looking for things where parent ID equals 184618. So one query obviously takes far longer than the other.

This one takes 10 seconds. And this one down here takes one millisecond. The reason why all comes down to the cardinality estimates and the number of rows that we actually found. You’ll notice that both of the index seeks into our index on parent ID estimate three rows are going to come out of it.

That’s the local variable density vector guess. Of course, in the first query, we actually find six million rows. And so this was not a good situation.

If you find yourself running into situations like this and saying, SQL Server came through cardinality estimation, well, that’s your fault. But if you run into situations like this where you’re like, oh, SQL Server guessed three rows, but we got 518 rows and everything turned out okay. I don’t know.

Maybe you’re safe for now. But don’t say I didn’t warn you. Local variables can really screw up query performance stuff, especially as things get bigger and bigger. But if we look at the data distribution for stuff in the post table, we’ll see that just because the post table is not well normalized and we have questions and answers living together, we have six million rows for parent ID of zero, which are questions.

And then we have just the top nine or so posts and the number of answers they have. So non-zero parent IDs are answers. The one that has the most rows in it is 518.

So this is where like, you know, like these very low numbers is where SQL Server starts to get these like these estimates for local variables from. But let’s talk about some other stuff. If you want to read in detail about local variables, I have a post there.

It’s a long post, but, you know, it’s a good one. So it’s sort of interesting, like why that happens in SQL Server. And this is sort of a thing.

Well, I mean, it used to be table variables would get sort of a similar treatment, right? You would run code that involved a table variable. And unless you had a recompile hint on there, SQL Server would always guess one row was going to come out of the table variable, even if you had an index on it, because SQL Server doesn’t maintain any distribution statistics about data that is in table variables, right?

So Microsoft changed that sort of recently. In SQL Server 2019, we got this table variable deferred compilation thing, which is kind of a smudgy name, but it really means that when you have code that uses a table variable, SQL Server will defer compilation of queries that touch that table variable until after the table variable gets populated.

So we know how many rows. We have table level cardinality for what goes in there that, of course, doesn’t extend to any like, you know, column level statistical histograms of data in there. But we at least get table cardinality.

So Microsoft could technically do this with local variables as well. It could say, I’m going to defer compilation of any queries that touch these local variables until that local variable has a value assigned to it. Right now, we don’t do that.

Right now, SQL Server says, I’m screw it. I’m going to compile a plan. And in order to do that fast, I’m just going to use these unknown estimates for any local variables in my where clause. If we did this in the future, well, if we did, we can change that.

Right. And if we did the deferred compilation thing where we didn’t compile a plan for any queries that use those local variables and like the where clause. If we did it every time, it would just feel a lot like you’re just recompiling, like just constantly.

If we just do it once, let’s say for a store procedure, and then we just use that plan over and over again, it would just feel like parameter sniffing all over again. And sadly, a lot of people use the local variable thing to get around parameter sniffing. So the internals to do the second thing are actually in the product.

They’re just not available to us. From what I hear, you need window bug and you need to start altering memory addresses in order to expose it. It’s not even like some super secret trace flag, but you could get local variables to act the way that tape, to act with local variable deferred compilation.

I guess it would be if you’re feeling bold and brave and doing it. But like I said, since people historically have used local variables to avoid that, the powers that be have decided not to break this behavior for them. You know, this, of course, could be introduced with some sort of database scope configuration or, you know, query hint or something.

But, you know, we just don’t have access to that yet. So let’s move on because what we have to talk about, at least probably the last thing in this video that we’re going to talk about, is recompile. So I love recompile.

I think it’s a fantastic thing. I think it’s a fantastic technique to use. You know, there are, of course, some caveats to that around, you know, like planned compilation time and execution frequency. But, you know, for like reporting type queries, I don’t see a reason to ever not have a recompile.

You would have to convince me to not put a recompile hint on a reporting query more than you would have to convince me to put one on. But there’s one thing that people continuously screw up. And that thing is where to recompile.

Because there is a difference between recompiling at the procedure level and recompiling at the statement level. So let’s create this index. And then let’s look at this store procedure here.

So this store procedure does have the procedure level recompile up at the top. And I say this sucks because this is really not my preferred method of recompiling. This recompile hint here is, this doesn’t have the same magic powers that it does if you put a recompile hint down lower.

So this actually has the same query repeated twice in it. There’s this one here, which is just a normal query. And then there’s this one here, which has option recompile at the end or a statement level recompile.

If we run this store procedure, which is going to execute both queries, right? It’s not one or the other. This doesn’t go terribly.

But there are some obvious differences between the two execution plans. The first one up here goes parallel and does an index scan. Okay. Second one down here does not go parallel and does an index seek.

This is the one with the statement level recompile on it. If we scroll on down a little bit, you’ll see the option recompile in here. But so the main difference between the procedure level recompile and statement level recompile is that statement level recompile gets you what’s called the parameter embedding optimization.

And you can see that if you hover over the index scan and you look at the predicate that gets applied up here. SQL Server doesn’t seek with this. SQL Server scans the whole index and tries to figure out if various things are null or equal to columns or whatever.

Right. There’s just a lot of stuff going on in here. In the second one, SQL Server simplifies that whole thing and just seeks to where owner user ID equals 22656. Performance isn’t dismal in either of these cases.

But if you’re going to ask me if I’m going to pay the price to recompile something, which one I would prefer? It’s obviously the second one. All right.

Obviously, the statement level recompile gets us a little bit closer to what we like, you know, reality on this. So if we run this with using a different parameter this time, right? Because remember, the pattern in here is this whole wacky, nutty thing, right?

This, you know, the old kitchen sink store procedure type pattern. If we run this with creation date now, SQL Server is going to give us a different execution plan for both of them. Right.

Because we have both a procedure level recompile, which means SQL Server is going to generate a new execution plan for all of the queries in this procedure when it runs. And then we have a statement level recompile, which tells SQL Server, give me a new query plan for this one query every time it runs. So what happens is SQL Server, when we use a different parameter, SQL Server does not reuse the plan from before.

SQL Server gives us a new plan for both of the queries in there. But this one, we still have this whole big predicate. SQL Server tries to figure out all of the knownness or not knownness of all of these parameters.

And in the second one, SQL Server simplifies it to one predicate that just says greater than or equal to 2013, 12, 31, 0, 0, 0, 0, 0, 0, 0. So my strong, strong preference is to use the statement level recompile and to not maybe generate new execution plans for absolutely everything in the procedure and target particular queries within the procedure that might be parameter sensitive or might be using local variables, which can also benefit from the parameter embedding optimization. SQL Server 2025 also has a new feature to try and deal with the optional parameter thing called OPPO or the optional parameter plan optimization.

It’s built closely around the same architecture as the parameter sensitive plan optimization. I have not had great experiences with that yet. The OPPO one.

And we’re going to talk more about the PSPO one a little bit later. The first thing that I want to show you, though, before we, the last thing that I want to show you before we go and before I start talking about something else is the effect that using option recompile can have on queries in the plan cache and in query store. So if we look at this first query up here, and I think if we open both of these, we should be in good shape.

If we look at both of these, right, we’ll have, these are two of the queries that ran from the procedure that we just, sorry, these are the two queries that ran from the store procedure that had option recompile hints on them. If we look over here, or rather if we start, let’s start over here because maybe I should do both. There is no parameter list for this, even though this came from the store procedure.

And even we, even though we know that SQL Server like ran a parameterized query because we have the option recompile hint on here. We have to go find the literal value in here. Right.

Right. And that’s the same thing is true for both of these. If we get rid of these and we look at this without the recompile hint, and I’m going to, I know I’m going to regret doing this because I’m going to have four of these and I’m going to have to figure out which ones are which. So what I should do is go look at the query IDs.

So that’s two, six, seven, eight, two. All right. So let’s get rid of this. So avoid two, six, seven, eight, two. We want two, six, seven, eight, one. One, if we look at the query plan for just this first one and we go to the properties, SQL Server will, this is the one that used the procedure level recompile.

This one we can see the parameter list in, but we’ll just see which ones were null and which ones actually had a value to them. So this is the one that executed where we use creation date. So we have the parameter compiled value in here, but we see the parameter compiled value for the rest of these is all null.

So that was way longer. Well, not way longer. That was just a little bit longer than I expected it to be.

But we’re going to end here and we’re going to pick up next looking at a minor parameter sensitivity example. So we’ve got that to look forward to. So thank you for watching and I’ll see you over in part two of this.

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.

bit Obscene: The Cloud, and Stuff

---

Video Summary

In this video, I delve into the complexities and challenges of moving production databases to cloud environments, particularly focusing on Microsoft Azure. Alongside my co-host Joe, we explore the nuances of modern cloud infrastructure through a series of candid discussions and practical examples, using the Azure portal as our guide. We dissect the performance implications of various cloud services, questioning whether the allure of managed solutions truly delivers on promises of ease and efficiency. Our conversation touches on topics ranging from CPU performance and storage latency to the ongoing challenges of database management in the cloud, all while critically examining the trade-offs between abstraction and control. Through this exploration, we aim to provide a nuanced perspective for anyone considering or currently navigating the complexities of cloud database deployment.

Full Transcript

This computer. All right. Welcome to yet another episode of the Bit Obscene podcast. Today, I am joined by two special guests. We have a new special guest. We have Microsoft Certified Master, Sean Gilardi, in a completely unofficial capacity. We have our usual temporary co-host, Joe Obish. Wait, temporary? Do you have some kind of plans to get rid of me? Wait, am I being replaced by Microsoft Certified Master? Is that what’s happening?

You are being replaced like AI, like everyone else. Okay. All right. I can live with that. Yeah, I’m migrating my best friend to the cloud, and you’re gone. You’re out of here.

But also, also in an unofficial capacity, I am going to be your completely unbiased moderator, Erik Darling. I have absolutely no certifications or skills to speak of, so I’m just here to make sure everyone doesn’t curse and keeps their clothes on. Anyway, Joe, why don’t you start by stating your position on the current state of the cloud?

And it doesn’t have to be any specific cloud. It can just be the cloud in general. But if you want to name names, I’m not going to stop you. So do you know why they call it the cloud?

I do not know why they call it the cloud. It’s because shit rains down on you. I’m already failing as a moderator. Where is my beep button? I don’t have any certifications either, but I would state my opening position as I’ve been a cloud hater since about nine years.

So I’ve been hitting on the cloud for nine years, and I’m mostly focused on the Microsoft Azure cloud platform. Would you consider yourself an OG cloud hater, or are you more new to the cloud hating game than most others? I mean, I started in like 2016. I’m sure there are people who’ve been hating it for longer than me, but I feel like nine years is a pretty respectable number.

It’s a pretty respectable number. Almost your 10 year anniversary of hating the cloud. Almost, yeah. Are you a proud cloud hater, or are you just sort of a run of the mill day to day cloud hater?

I think I’m run of the mill, because if there are other things to talk about, I’d prefer to talk about those things, with the exception of this very important run of the mill. We’re not a table discussion. Okay. All right. Great. Great. All right. Well, good, good, good opening salvo there. Sean, what is your, what is your position on the cloud?

Any cloud will do. Yeah, I’m more of a moderate on, on that. It’s, I think there’s good use cases for various things. You know, not if we, in just the vein of databases, it’s a hit or miss. I still think there’s some good use cases. Overall though, there’s, I think there’s better use cases for people. But overall.

What use cases, what, what use cases sound good for the cloud? What, what would you, what would you put in the cloud and what would you take out of the cloud? I think there’s some really good use cases for people doing backups who otherwise wouldn’t have backups, right? Sure. So like if someone wrote a piece of software and they wanted to back it up so they didn’t lose it. And maybe like if anything terrible befell their laptop, they wouldn’t just like have be completely out of luck.

Or accidentally deleted the GitHub repo. Yeah. Like that. Exactly. Yeah. Correct. Someone like that should probably. Yeah. That would be good. Yeah. But to, I, I also wanted to go with, I was told in two, about 2009, I was working at a undisclosed location and we had a TAM from Microsoft come in and, and I was a DBA at the time.

And tell me that, you know, I only had two or three more years left as a DBA because everything was being, was going to start going to the cloud. Uh, won’t need DBAs anymore. Everything’s going to be auto everything. The cloud’s going to have everything. Nothing’s needed. You know, you’re everything. So, you know, fast forward, what is this almost 18 years and, uh, you know, here we are with the same issues. In fact, I would argue at least for databases, more issues being in the cloud.

Um, uh, so I, I would say I’m not necessarily an OG hater. I am, I’ve graduated into the, uh, been here long enough that all that is old is new again. And, uh, the same cycle. Sure. So, you know, I mean, I, I think the, the original death of the DBA was that, that, I mean, at least like the first extinction that I survived was virtualization. Right. Cause virtualization, I was supposed to wipe out like DBA is no longer necessary. Everything, uh, is in your own personal cloud.

Yeah. Yeah. Don’t have to worry about anything. Just do backup. Yeah. You know, all that great stuff. And then, uh, of course the cloud was the second, I think big extinction, extinction event for DBAs. And now we have the, the, uh, well, I guess I think AI is more of a general extinction event, not just DBAs, but they’re certainly part of the, part of the fauna that, that seems to be getting, you know, scorched off the earth. Uh, I mean, it got the dinosaurs, so it sure did. It sure did. Uh, you don’t see too many of those around anymore.

It’s a shame. They, uh, they say the same thing in Oracle, or at least at the, the one time I went to open world, which I would describe as a combination of amusement park slash, uh, prison camp. It, uh, wasn’t very fun.

Is that when you got that shirt? Yes. I, I, I, I, I, I’ve been wearing these shirts ever since I tried to avoid a repeat of what happened in open world, but the, the, uh, really rich guy in charge of Oracle was giving some, uh, keynote and I was going to watch the keynote in person, but they wanted to search my bags. Cause I guess everyone hates them.

Hmm. And I, uh, declined the search. So I, I, I, I didn’t get to watch in person. What was in your bag? He said that, uh, that’s under, uh, NBA. Uh, he said that.

Yeah. Pay the pay the pay was going away. That was in like 2017. And I figured, well, if he’s right, there’s no reason to pay attention to him. If he’s wrong, there’s also no reason to pay attention to him.

So I just like. Stop paying attention. There was no way for Larry to win. That was there. No, I mean, he started, he started with the bad opening statement. Yeah.

I mean, don’t you feel bad now? He, he did because of you, he couldn’t get another yacht. Hmm. He had to go buy an island boats or dried up. How’s he going to get to the island?

We don’t know. We don’t know if you got a boat, you gotta get a boat guy. Everybody knows that cat catamaran captains are very, very difficult to come by these days. I’ve watched below decks.

So, uh, I guess let’s, let’s, let’s go back to a Sean statement here because he said, Wait, don’t you want to state your opening position or did you write? I am.

I am a completely unbiased moderator. Oh, I don’t have an opening position. My job is to explore your positions. Okay. Oh yeah. Yeah.

Hey, so we’re going to, we’re going to do a lot of exploring of positions in today’s, today’s bit obscene. Podcast. Uh, so, uh, Sean, you said at some point that there are things that are not so good in the cloud. What, what, what, what are some of your least favorite things to see in the cloud?

What, what, what sort of workloads do not do well in the old cloudy space? Production databases. Production databases.

All right. No, it’s, I mean, that’s a little bit of a thing, but there are times when it’s, yeah, when it’s fine. But when you move your database into the cloud, especially production, right? It be, it’s not a, oh, let’s move this.

Let’s do this. You’re either. 100% entirely in and you move all your applications and all your web and all your content delivery and everything goes to the cloud. Or you’re just going to have a terrible time.

And then once you’re there, it’s kind of no different, you know, as you don’t, you don’t have control over everything. And that’s supposed to be the allure, right? Unless you do a VM, but then that’s not really the cloud.

That’s just a VM in a data center somewhere, which is the same thing as people did with VMware. So, yeah, or hype, virtualization in general. No one runs Hyper-V in production, Sean.

Come on. Don’t, don’t start. They might start now, but come on. Come on. Unserious. I mean, Brogcom did it. Did, did a Hyper-V a favor.

They sure did. It’s like, you know, oh, we have this thing and oh, well, our latency is really high. Oh yeah. That’s because you’re going from Los Angeles to some cloud provider through 17 levels of networking through 150 different ISPs and 17 satellites. Like, yeah, you’re going to be latent.

I don’t know what, well, it was faster when we were, well, yeah. And then the thing is, well, now you got to put all your stuff up there. So then it becomes not a, oh, we’ll do this and it’ll be great. We’ll get some HA, you know, we don’t have to run a bunch of this stuff.

We’ll get backups and some of the other things too. Well, now we have to change our entire architecture. Oh, they don’t have a service for this. Uh, yeah, it’s, you know, oh, we have to change how we do this now.

Oh, we got to re-architect this. It just becomes a whole thing. And maybe a re-architecture is needed or maybe a refactoring is great, but it. Yeah.

You know, a lot of people, a lot of people got sold on this, like a very, like a very easy lift and shift. And, you know, especially for legacy applications with a lot of legacy dependencies, a lift and shift is just not, not really, not really. Yeah, the lift and shift and the high performance.

Yeah. I do see there are some high performing, we’ll just be generic and call them skews out there from every cloud provider. How much performance you actually get from them, eh.

And that’s for the less managed things. And then you get to the more managed things and it becomes much less performance. That, that is, that is something that I have found as well.

Uh, so, you know, um, like, I, I think. Like almost this it’s, it’s weird because a lot of people did go from like physical to virtual. And there was obviously like the abstraction tax on that, but like, it was, it was very weird for me to see people go from virtual to the cloud.

And then there’d be some additional abstraction tax on top of that. So it’s like, you like, like, I remember like doing like, you know, oh, we’re going to like, you know, we’re going to do a physical to virtual conversion. We have to forecast hardware.

Like, you know, like in the past there’s been like, I don’t know, let’s just say like somewhere between 10 and 30% hit on performance and from various hardware resources. We have to account for that. Then like, they were like, okay, we’ll go to the cloud.

We’re already virtualized. So what’s the difference going to be? And then it was just like, wait a minute. Like we got screwed again. Like, no, we, like, we need so many more cores now. What is happening?

Yeah. And it’s again, when I say cloud, I’m not talking about any, any infrastructure. So in particular, it’d be, yeah, it’d be all managed stuff. So anything Pat would be.

Yeah. Like clearly known as Paz. Yeah. Platform as a service for those uninitiated going and watching. Joe is going to share his screen and show us some, some interesting things. Now.

This looks like, this looks like the Microsoft Azure portal. I am. I am so triggered right now. Are you? I am so triggered. So first of all, Sean, the standard series Gen 5 logical CPU is great for most relational work database servers. Yeah.

Cause you know, like when, when you do like in, you know, some like custom 2.3 gigahertz Intel from like seven years ago, I think. How, what a great CPU for my database. But like, I mean, it was a great, seven years ago.

Maybe it was, I mean, I’m sure, I’m sure it’s great for like, like power efficiency. Um, but like. I mean, I don’t know, like, like maybe we’re going to be like donating our extra CPU cycles in the cloud to be like carbon neutral or something. Well, like, I don’t care about the power efficiency of the data center.

Like, I, I got this whole long rant where like, um, you know, like, why do we use databases? We use databases or because they’re supposed to make things easier, right? It’s supposed to make development easier.

You don’t want your developers coming all day based on scratch. Right. And you know, like for a while, like. That doesn’t seem to stop them a lot of the time. Yeah. Like before a while, like things actually were getting easier, right? Like you remember back in the bad old days of 5,400 RPM hard drives and like, like page splits were super important.

And everyone said, oh, index documentation is super important. And, and, and then we like got better storage. We got better hardware.

Like things got easier for a while. Right. And now we’ve somehow made things less easy for our production workloads. And I, uh, I think that computers and software should serve us and we should not be the servants of software.

And for the cloud, it really feels like sometimes you are a servant of, you know, these CPUs that were great seven years ago and these storage latency is, which are unreasonable. They, they, they, they backtrack quite a bit. It’s solved by just paying more, right?

If you just pay more, it just, it gets better, right? The more, it’s like the more, you know, except the more you pay. Yeah.

Nothing wrong with that. Also those CPUs, it’s entirely possible that they have not been patched with various Spectre and Meltdown mitigations and they might still be pretty good. Those patches nerfed a lot of stuff, but you know, just throwing it out there.

And it, one thing I’m curious about is like, there isn’t anything inherent to Azure SQL database, which requires that them run on old crappy CPUs. Right. Cause that’s what they could get off eBay.

Well, I mean, like, I do have to wonder if the lack of diversity and like super high spec CPUs is kind of our fault. Like maybe there’s no demand. Like I, I see, I don’t know a lot about it, but I like, I see Amazon trying to compete on like performance.

Like, Oh, Hey, you, you want to run your database in the cloud? Like here’s a 4.5 gigahertz CPU from last year with 32 gigs of memory per core or, or something. Right.

Whereas like, I, I mean, I’ve never seen an Azure SQL database offering that’s like anywhere even close to that. Oh, like, we’ll like you, you will want more memory. Okay.

We’ll give you eight gigabytes per core. And that’s like our super size option that that’s like all we got. Like, I think there are some that are like 13 gigs per core, but like that, that knob doesn’t turn well for me, especially with a lot of the workloads I deal with, because like, you know, like a lot of this stuff, you know, like, and granted there, there is some like, like far, far down the road wisdom that as you remove IO bottlenecks by like adding memory, you could theoretically add CPU bottlenecks. Because now everything is just banging CPUs up 100% all the time.

But like, for the most part, like most of the workloads that I deal with could survive very easily running at between like 60, 70% CPU with far fewer cores. But they just need way more memory because the amount of data that they’re dealing with is like, just not come, this is not compact well into what you get available through the usual like web portal knobs for adding memory to these instances. Like there have been, you know, there have been some stabs at improving that with like the constrained CPUs and with the memory optimized stuff.

The problem that I run into is that like, aside from, you know, them not being available across all regions, a lot of the time is also that like, you know, the cloud providers, and I’m saying this generally, they know what you’re using them for. They’re like, they’re like, they’re like, they’re like, they’re like, that’s for a database. That’s for your expensive thing.

Of course, we’re going to charge you more for like, like, come on, like, like, you’re not be not putting like your app servers on that. Like, you don’t you don’t need, like, you know, 768 gigs of memory for your app servers. We know what you’re doing with that.

That’s where you’re putting the $7,000 a core thing. Yeah, it’s a there’s definitely a lack of flexibility there. Right. And it makes sense. If you think of it from a cloud providers perspective of if we let people choose, and even if you charge them for what they did, right, this is how much per gig of memory, this is how much per core, whatever, even if you let customers choose, that would be a nightmare. Because at the end of the day, you still have a set of computers with a set of specs. And if you don’t have those at exactly 100% on memory and CPU usage, you’re losing money. Right? So from the cloud providers perspective, whether it be Google, Amazon, Microsoft, whomever, you need to get that packed in, you need that kind of over subscription, almost model in order to just make it work.

Yeah. Right. You’re gonna say, Oh, you want your one fourth of a virtual core for free for 400 hours a month or something. Here you go. We’ll let you do that as a way to get in. But it’s also gonna run on that low end hardware that you’ve already over the five years have, you know, amortized out. You’re just trying to get every every last dollar that you can from it, which I don’t blame them. But at the same time, it’s Yeah, I wouldn’t put a actual production workload on something like that.

No, but like, but cloud providers, the offering is for Azure, like, like, that is the production CPU for Azure, like, for other providers, it might be different. Yeah. And that’s kind of the problem, though, right, Joe, is that we’re sitting here talking about CPUs, you know, brought, Broadwell Skylake, and the whole point of the cloud was it was supposed to abstract you, right? You’re not supposed to care about those things. You’re not supposed to know. You’re not supposed to even know about them, right? Maintenance. Yeah, that’s done for you. You shouldn’t even know, like, all these things that we’ve gone to. This is one of my issues is Oh, well, the cloud is gonna be easier. It’s gonna we’re abstracted from all this stuff. And then what do we get? Well, we’re on a when you were showing that the screenshot, Joe, we’re on a gen five. Well, what about is there a gen six? Did we get on a gen four? What about a gen 68,000 million? You know, why are we we need to be pinned to a to a gen 12? Because we’ve noticed a performance regression in gen five? None of that should be a thing. In my opinion, if we’re going to client like none of that should be a thing on a on a Paz environment.

Well, but you get different offerings like you know, as you said, you spend enough money, you’ll get better performance. I mean, maybe it’s hidden in some like super elite, like diamond platinum level customers only. And I don’t even I don’t even have access to the portal because it would cost more than Eric’s favorite booze. We call that the Walmart club. Yeah, like maybe things I don’t know about but like, like, I’ve I’ve never seen an Azure like, oh, you want 4.5 gigahertz CPUs, you can get them will just cost you a lot of money. Like as far as I know, this is not available. Like you can get a bit more memory, you can get faster discs. I think they have some premium CPU that they say is better, but it’s still not like the best on the market.

No, but a lot of this is the fault of cloud providers, right? Because what they did was they marketed it as like cheaper, faster, more secure, more reliable, all these things, right? There were all these promises and promises made by what the cloud was going to give people. And then people got there, and they weren’t really getting all of those promises met. And then like people wanted to know what was underneath the cloud because they were like, wait a minute, like, we’re not seeing the performance what the like what’s in there, like what’s under this. And so like, like, they had to start like putting these like various things out there. I do think that there would be like, I understand why, like CPU and memory are not separate knobs that anyone can turn at any time to allocate more or turn down like the allocations.

But what always bothered me is that like, there weren’t like, there weren’t like, just like more, especially in the database space, there weren’t more sane offerings, especially for like known product limitations. Like if you take SQL Server Standard Edition, right? Like there’s obviously limits on CPUs, they change from year to year. And there’s obviously limits on memory that have changed at least once. And perhaps there’s another change coming with SQL Server 2025. You don’t have to blink once or twice to confirm that. But like, like, there was almost no normal sized box for Standard Edition that was like, hey, here’s like eight to 16 cores with like 196 gigs of memory. It was always like, well, you can have eight or 16 cores, but then you have like 128 gigs of memory that you can’t touch. And you’re like, I don’t need it. Like, just give me something that, give me a glove that fits, man. Give me a glove that fits.

And then like, uh, manage instance with like, uh, like, like, like their, like their cap on memory is like 480 gigs, I think for a lot of them. And you’re like, like, like, like, I don’t need 128 cores and 480 gigs of memory. I need like, like maybe 32 cores and like two terabytes of memory or a terabyte and a half of memory. Like, like, like, like I like, this is a database, you know, what you’re dealing with. We need the database. We need the memory. You know, uh, and like, you know, trying to add like, you know, like incrementally faster storage offerings behind the scenes. It doesn’t cut it for them for those things.

Either of you guys know why those memory options aren’t really available. Like, like, is it just overall industry capacity? Because in terms of memory, right? Like you buy it once. It’s pretty low power uses, especially compared to your CPU. You know, there isn’t a licensing for anyone on memory at any point. Like, you know, like if any, not directly, but indirectly. There’s yeah, the biggest. So to answer your question, you know, kind of the biggest, uh, items there are going to be the density that you pack it to. And then what skews you want to sell it to, right? Like if you’re trying to really maximize out your stuff, there’s a per core kind of throughput that you can get with memory.

So if you can do, depending on what your bandwidth is for those kinds of DIMMs that are in there, uh, you know, you don’t want to put, if your per core bandwidth is say 80 gigabytes a second, you don’t necessarily want to put in so much memory that pretty much can’t go through it. So if you’re giving out, you’re packing that with whatever CPU you’re getting wholesale, uh, you, you don’t, you basically, you want it to be matched pretty well, right? You don’t want it to be like Eric was saying, you don’t want a server that has four cores and two terabytes of memory.

Cause then you’re going to run out of cores before you satisfy the memory. And you don’t want a server that has a thousand cores and 64 gigs of memory. Cause you’re not going to get a whole lot done.

So for databases, for other things, yes. For database. Yeah. It just seems like there was never a good alignment on cloud hardware, specifically for databases. Like that, like that’s the thing that’s always bothered me the most is like, like even like, uh, like various platform as a service offerings, like no, just no one got the numbers kind of right on that.

Like it always, it always baffled me a bit that like, like, like what, like, like why, why do you, why are these like these insane jumps in CPUs with these like minimalist jumps in memory? And you’re like, I, I, I just never understood like, uh, like even from like a, a slick, a server density perspective, like, like, like, like, like if you’re, if your host VM has like, let’s say 256 cores in it. Like, like, like why not limit the CPUs and bump up the memory and like what people sort of like, you know, be more dense in those spaces.

It’s, it’s, it’s, it was always, it was always odd to me. I have a model, which might not be correct, which is cloud provider buys like a hundred thousand servers or all the same. And then some, some set of servers, but we’re going to use these for the database workloads.

And there isn’t necessarily as much like care and careful attention that we might want because we’re database people in terms of, you know, this subset, these specs are going to be totally different. I don’t know if that’s true. That’s just, just like a, No, I think, I think that’s, that’s probably accurate, but then, then you have cloud providers that now have like bare metal offerings and you’re like, We’ve just come full circle.

Like why, why, why did we do this in the first place? It’s honestly, back to my original kind of statement of, it does make sense to some situations. There was a company that I was helping and, you know, the, the CIO basically said, you know, I’m tired of running data centers.

I’m tired of paying multiple ISPs and dealing with all this stuff. I’m happy to pay more and put it in the cloud and not have to deal with this. Yeah.

When there’s an outage, it’s not everyone that you like, not your employees hair on fire. Exactly. It’s, you know, there’s, there, there are some, there are definitely, there’s definitely some wisdom in this stuff that you can, like the, the, the worries you can offload to the cloud. But, you know, um, Then they get upset.

Well, what’s, what’s the last ETA? What’s the status update? What do you mean? It’s not DNS. What do you mean? It’s DNS. Yeah. You know, like you’d be real careful with those SLAs on the cloud provider stuff too. Real clear.

Cause you know, fine print. Yeah. Cause you know, like you’re, you’re, you’re not an important face to them. Like, like they have, they have big customers who are important faces. Like, like you with your, like, you know, like, I don’t know what, like maybe like a hundred VMs. Like they’re going to look at you and be like, yeah, okay, we’ll, we’ll get to you.

Don’t worry. Like, fine. You’re like, yes, we take a number. We’ll get there. You get, it’s like, you know, deli counter rules with, with some of this stuff.

I have a question for Joe. Oh boy. He was one of the. Oh, wait, wait, wait, wait. The MCM is asking me a question. That’s how this works, man.

This is quite a day. Oh, well, you know. All right. Go ahead. The viewers, what they want. So I’m ready. So are there any workloads that you believe are actually good for the cloud? Yeah.

Like anything that’s, that’s a non-database workload, because it’s not my problem. And since it’s good for the cloud. Um, well, like, like I wouldn’t go so far as to say that. All database workloads shouldn’t be in the cloud because, you know, like I’m naturally drawn towards like super intense workloads.

Like where are I going to push the limits of SQL Server hardware? Like that’s the kind of thing that I like to work on. And I like to think about like, there definitely are a lot of, you know, pretty bog standard workloads out there and they might do just fine.

Um, on, uh, cloud hardware, or at the very least they’re, they’re, uh, they’re today and they haven’t gone out of business yet. So they’re, they’re, they’re, they’re getting by somehow. Um, I think your earlier point was pretty good in that, you know, cause I remember hearing the same thing years ago where like, we were thinking about the cloud and we wanted information about the hardware.

But with respect to SQL servers and their response from Microsoft was, oh, well, why are you even asking that? Like we’re extracting it, like, you don’t have to worry about that. So, you know, like, if, if you go in with, with that, that position of.

Like, I want the best possible hardware. And it’s just some, like, you know, like within the big amorphous cloud, I want the, like, uh, the, uh, densest, like sweetest, most fluffy part of the cloud. All for me.

Right. Like that, that, that doesn’t really. Yeah. I want the part where the rainbows are. Yes. Yes, exactly. No, but like, so like, like for, for cloud workloads. So like, I think we can all generally agree that, um, super high end, super, like, you know, like, like workloads with very high performance requirements.

Are maybe not a great fit for platform as a service. You can, you, when you have enough, when you have enough granular control with like the, like a VM host in the, or like a VM in the cloud, you can probably do some better work there. But for like, you know, someone starting out an application, if you think about like the initial spend that they might have to make on infrastructure, people to manage that infrastructure, people to deal with all that stuff.

Like, like, like that could, that could be a, like a really big hurdle for them to jump. But if someone like is just starting out an application, they can get, you know, lower end hardware, uh, performance is probably not like, like if they over spec some, like a, like a startup startup application, like they can get away with like, you know, maybe the extra, like five, 500 to a thousand bucks a month on some slightly bigger hardware to compensate for stuff. Not have to worry about like any initial purchasing, not have to worry about investing in people to manage it, not have to worry about all the stuff that goes into that.

They can focus on having an app, developing it and making it accessible, right. Getting some money in the door. Then from there, you know, you, you, you get into stuff, but like, you know, I think the, the, the general problems that we’ve dealt with and the problems that we see probably like more day to day is with like, you know, legacy stuff that got pushed into the cloud without really any forethought or investigation.

Uh, like higher end workloads that, you know, like maybe they started there and they like outgrew whatever they were on, or maybe someone just, again, tried to lift and shift it there. And no one was prepared for all of the things that come along with moving an application to a wholesale new environment. Um, you know, I, like I’ve seen some people, uh, you know, like get like, like, Oh, we started moving all our application servers to the cloud and the latency between the application servers and the database server killed us.

So we had to hustle to get the database in there and like the latency is gone, but like performance is completely fell apart. Cause we didn’t do a good job with the migration. So like, like a lot of, there’s a lot of stuff that like, you know, that comes along with the move to the cloud that just doesn’t go well for people.

And like, like, I think those are the problems that we see. And I think that’s where a lot of like, you know, the sort of like the cloud hating comes from. I think you hit the nail on the head though.

Uh, in that’s the quintessential. Perfect use case in my, in my opinion. And, and what you said of, let’s say you’re, you’re an app developer, right? You write in whatever language you write in and say, Hey, I need, yeah, I need a database.

PowerShell. That’s, that’s absolutely what Eric writes. I was like, I need a database, right? I need somewhere to, to put this stuff and I’m gonna, I need a web server and I need this.

I mean, it only makes sense, right? You click through a couple of clicks. Yep. You got a web server, you got a connection string to a database. Like you’re ready to go.

Yep. Right. You, like you said, you didn’t have to deal with, you might not know, you don’t want to spend your time setting up infrastructure and setting up and doing all this stuff. Active directory. Yeah, exactly.

And be working. You could be working on your code. Like you said, getting your product out the door so much faster, so much easier. And then if you need to scale up, great. If you need to spend a little bit more money, great. And then if you need to iterate on that, I’ll scrape.

I think that’s the perfect use case. Yeah. Honestly. You need to hire a consultant to help you save money in the cloud. Great. Exactly. But I, I also agree that, yeah, the legacy was a problem. I even think a lot of the new stuff is a problem, even with the quintessential best use case.

And that’s just because it’s, it becomes, and I’m not, I know this isn’t a talk about AI, but it becomes the same issue I see with AI where, well, it’s someone else’s problem. I don’t actually need to understand what I’m doing or know how anything works.

I just know click it goes right. Bigger number, better, more clicks, better. Everything’s fine.

So then when there is a problem or when things don’t scale, right, or when stuff happens, it’s, oh my God, well, I don’t know. Well, this and this and this. And I think that also leads to a lot of quote, you know, bad cloud experiences as well. And it’s, it’s because at some point when you make things so easy, which is good, that you remove a lot of barrier to entry, right?

So friction is gone. That’s a good thing. But then people get lazy. People don’t care, especially in today’s environment where every, no one wants to be an expert. No one wants to understand how things work or how it goes.

They just want to click on the next thing and go and click on the next thing and go. And that’s fine until something breaks. Yep.

And so I think that’s, that’s the downfall of that scenario. Yeah. Like, you know, you get people who are fine continuing to rack up the technical debt without ever paying off any of the interest on it.

That’s, that’s, that’s what I see quite a bit. The other thing would be like people just picking the wrong target for their cloud migration. That’s another big thing where they, they just weren’t prepared for the limitations and restrictions that are the things that they’re not allowed to touch in these various places.

Probably one of the, like the most common ones I see are people who are like, we moved to Azure SQL database. Now, like everything slowed down.

Like what happened? I’m like, like, well, like, let’s, let’s look like if they still have the old server, like we take a look at that. And I’m like, oh, well, this is how you have it set up over here. And this is how it’s set up over there. And they’re like, well, can we change that?

And I’m like, no, like biggest one, probably like cost threshold for parallelism. It’s like, it was set to like, they had to set to like two, like one of my clients had it set to like 200 or 250 when they were like, you know, on their on-prem, because they’re like, that was just, I know that was, but that was what worked for them.

They also had maxed offset to two, which I thought was nuts. But then like, you know, they, they tried setting it to eight once and it was just like massive thread pool weights everywhere, but different story for a different day.

But like, just like not being able to like change cost threshold for parallelism up to where they had it before they were, they had to like, like, it was just like a lot of tuning work to get everything to a point where it was no longer just breeding the server all day.

I think your earlier point was, was good. I’m always doing it as like, I have an existing legacy application. It’s not in the cloud, we’re moving to the cloud.

And, you know, like if you’re starting out in the cloud, if you’re used to performance being what it is, I think it’s definitely easier to adjust. Joe, are you saying if you’re cloud native, that is that what you’re going for?

I mean, like, I have like, I have like two prepared rants here. I’m going to give them both. You can take a breath between them if you need to.

Let me share my screen here. Yeah, go for it. One straight conscious stream of thought. I’m just going to mute myself. Like on this subject, like, I don’t know like where this marketing stuff comes from, where, you know, like, we’re bringing the seats.

You know what I was saying before, like, I don’t think that developers should be spending their time tuning queries if they can. Like, you want developers to make your application better. Yep. But like, even on Microsoft’s website, they always talk about, oh, you, you’ve developed workloads, well, you can put them on the worst hardware possible and even like, you can save money.

So first of all, this is basic tier, five DTUs, IOPS, one to four IOPS per DTU. So that’s between five to 20 IOPS. All right.

I’m going to, I’m going to get my, my virtual stopwatched out and I’m going to count to five. And I’m going to see if I can do it faster than one second. One, two, three, four, five.

0.76 seconds. If I can count faster than the storage is giving me IOPS. 0.76 seconds. I think we have a problem. 0.76 seconds.

And like, I like, I don’t know like why this stuff gets pushed. Like, you know, like developers do make a lot of money. It’s hard to hire good ones. Like let’s make them super inefficient and make them hate their jobs. Cause that’ll be better.

Like, I don’t know like why we’re pretending that something like software industry is like, so cutthroat and competitive. Cause you know, like if, if you look at the profit margins for software compared to like real companies, I mean. 0.76 seconds.

Real companies. 0.76 seconds. It’s not like an order of magnitude almost in, in, in some cases. 0.76 seconds. Like, like, like, like it’s like, I don’t know why I had like software companies like, oh, you know, like, like who we’re giving our developers. 0.76 seconds.

These like, uh, these, these, these, uh, two V core machines that we’re saving so much money. 0.76 seconds. And like, there’s another company and they managed to get development machines down to like 0.5 V cores. 0.76 seconds.

I know we’re saving so much money. 0.76 seconds. And then another company finds a Pentium three server in a dumpster and they throw the workloads on that for developers. And finally, thank God the business is safe. Like, like, like, it wasn’t real cost.

0.76 seconds. So I don’t know, like, I don’t know where this stuff comes from, or like, why anyone. 0.56 Like I it is. 0.Нет serviços here.

0.80 seconds. 000. 0.75 seconds. 0. Cohen would be.

0. enam hour. 0. chartsred żebyarnos to değilif C mitras. 0.ense Zoom um, slow I have Señor thank you, this could hit that button by hitting down tiny 诉, uh, we’ve got that to compte. cloud services they say it is great for developer workloads but that’s a before you go on to to rant number two joe i i echo that though in the i’ve worked at places where you know they’re paying people hundreds of thousands of dollars a year not counting any benefits or anything like that right and then it they make it sound like it’s the end of the world if they have to spend a thousand dollars on a lap on a new laptop for you that’s not 12 years old that has 64 gigs of ram and it’s like why like you said why would you want to hamstring your your highest you know pay like your your main thing with essentially what is what 200 a year you’re okay with spending 200 grand a year but you’re not okay with spending 200 a year to make that work but i i don’t understand i’m with you well there’s there’s two parts of it right like um if if you’re so like let’s say your developers are developing on azure sql database for like an application that works in azure sql database if they’re developing locally on developer edition with like the hardware right all right there they might have a very different idea of works on my machine and like like when it gets then when it gets up there they’re like wait a minute like all this stuff that happened here isn’t happening there and there’s a lot of other weird stuff happening there and i don’t understand it so like i guess like i think there is some like there is some correctness in making and having people develop where they at like on the same platform that the application lives like it would almost be like if you have a like a like oh like we have a sql server application but you’re going to do all your development work in postgres and we’ll figure it out later right it’s just like it’s almost that level of difference between like what you’re like where your development stuff happens and where your workload actually runs so like i think there there is some there is some wisdom in like having people develop there as well but um you know at the same time like if you are what’s a good way to put it like if if you if if you don’t have people who are smart enough to deal with um the way that things work in a in a certain place then they like you you need you need to sort of rethink that strategy a little bit well and to be fair i mean like i agree with you but i shouldn’t be able to kind of fashion your iowa operations well yeah absolutely right like and like like that is the model that microsoft for whatever reason says is great for developer vertical it’s like if you i don’t know if you some like p8 or whatever is is is that hopefully that’s a real thing and you put your dev environment on a pa like i think that’s totally fair um i’d even go as far as to say if it’s too slow for developers it’s probably too slow for your application and you might want to rethink like what you’re doing there um i like that’s also true yeah nowhere clause just uh uh filter in the app it’s better yeah that’s right i’m ready for a rant number two bring it on um and i mean like this is about cloud migrations again and how sometimes you can kind of end up in a state that doesn’t really make a lot of sense and like you know i mean like to be fair like it is our choice like um i’m uh turning into cloud again yeah joe is passing through a cloud um you know because like you you’ve not on the azure sql database or even on the azure platform right like you have you have managed instances you have like vms in azure if you want to do everything yourself and you have azure sql database right and all those have abbreviations that i can never remember don’t forget hyperscale hyperscale is part of azure sql in the cosmos come on come on i’m pretty sure that’s true and you know like oh but like like and i’ve seen guidance which is like you know if you’re doing a lift and shift azure sql database may not be right for you maybe you should use match instances maybe you should use just basic vms you’re everything yourself i have seen that guidance but you know there are some companies out there who are going to lift and shift to azure sql database and i feel like the most natural path doesn’t make sense um so but like you know we we have talked about dtus versus the micro model right like no one knows what what what what a dtu is you uh can’t fold over your your licenses you already have in the vtus so i understand why people would want to pick vcore especially if you’re doing a lift and shift like that seems to make way more sense than utu most of the time and then the second session is do i want an elastic pool or not as far as i can tell it’s it’s the same cost it’s the same cost per core like i i i won the pricing thing and i changed elastic versus not elastic it was the same cost and you know like like you’re probably not gonna have your databases all running at the same time and like it’s you know instead of trying to scope out like i have 20 databases let me figure out the v course for all of them i have 20 databases just you know i’ll throw them in a bucket and i i think i saw sean expressing dismay at elastic pools i’ll all let him chime in after my my rant’s done but like like like if you think about the most natural path you know v cores elastic pools you’ve gone from like an on-prem instance of sql server to instances of sql server and you can connect to it and you can see all the databases but they but they like can’t talk to each other and you have like all of this azure sql database limitations no asian jobs no cross database prairies blah blah blah you have different performance like kind of like like it just feels like like like the original dream of azure sql database where you know you’re you’re one database on a server and you have many friendly databases and you all live and coexist together like it it just feels like like kind of like a poor implementation of what you’re used to on-prem well yeah but that’s why managed instance came along right and it’s like two different two different target audiences yeah no i mean like like it is but it’s it’s hard to find people saying good things about managed instances right but sean you you wanted to say something about elastic pools share share that’s your wisdom no it’s elastic pools is the same issue that that i saw with as you said people on-prem where they put 600 databases on an instance right a lot of people out there don’t necessarily i won’t say no any better but don’t necessarily have the forethought because everything you know everything is a now now now now just now and then you know nothing’s gonna happen just now and just it’s it’s a problem for later and so you get to your two databases in elastic pool and that’s fine then you get to 10 oh look we’re so happy we’re saving money we got 10 databases on our elastic pool then you get to 100 and stuff’s not running right at certain times and you’re like boy i wonder what’s going on but of course nobody cares enough to look into it and then you know for the company and then you get to the whatever the max is and you try to add that extra database you get an error so then you open up a ticket what we’re getting an error when we try to to open this okay what’s the error well the error says uh you’ve hit the maximum you know 500 databases in it but we but we need we need this but but we need 501 yeah we what we don’t understand why we’re getting the error and it’s like first of all wow reading is apparently hard these days and two well yeah you’re gonna have performance problems it’s just and then and then you get the well we shouldn’t have to to scale up or scale down or you get the opposite well we’re having a problem with our last book and what’s going on you know oh well we did a scaling operation this hour and then this hour we did another one then this hour we did another one then this hour did another one and we don’t know why we’re getting different performance different times and it’s just there’s there’s good things to it i’m obviously highlighting the bad things right now there’s a lot of good things to it it’s just the elastic pool i don’t like because as i’ve as eric well knows and as you well know people don’t know what their queries do they don’t know how much performance they actually use on things and they just keep throwing stuff at it until it breaks and then everyone’s super surprised that it breaks like this comes back to like what i was saying earlier it’s like everyone has fallen in love with the abstractions and that you don’t have to think about anymore developers fell in love with orms cool i don’t have to think about SQL anymore i throw it out there like oh i’m in the cloud every cloud provider says hey guess what you don’t have to think about it anymore like you know you go ahead like you know have fun all right like you know like we’ll take care of it all for you and then you know it’s coming along and all of a sudden they have to start thinking about stuff and they’re like wait a minute i’m allergic to thinking no i can’t hey i don’t want to do this it’s like i like oh i was told i wouldn’t need to think about it i was told i wouldn’t need any experts like that eric do you have any do you have any advice for someone who maybe doesn’t want to think about sql server and wants to offload their thinking their thinking to someone else yeah i mean i am i’m a very affordable object for a SQL Server i was gonna say yeah i would recommend eric my rates are reasonable as as everyone well knows so don’t be shy speaking of reasonable i want to tell you something unreasonable all right that would be my uh final pre-prepared rant all right the great clr scandal of 2016. so as everyone knows as your sql database used to support clr functions and well there’s things aren’t functions i’m missing college functions that’s fine they realize that there are some i think it was like a like like like a security vulnerability where in theory if you’re a super sophisticated attacker you could use clr code to like read shared memory and like read other customers data um so then clr got turned off and you know if you were currently using clr in production and as your sql database well that’s too bad it’s turned off and that they have said oh we think we can turn it back on in uh six months um as of 2025 it is not back on in general now i i have heard rumors that you know if you’re a super elite diamond platinum card uh customer you can get your clr functions approved maybe it’s even sean who occasionally approves yeah rubber stamps for you um but you know like a lot of us aren’t super diamond platinum elite customers of microsoft and like the wall like things and like that was really the instant for me where when someone occasionally asks me you know like you know hey joe what’s your opinion i would say well you know what about like i i don’t want my cloud vendor to break my application without any warning like you know i’m not saying microsoft like should have left clr on i’m i’m sure the security problems were very difficult to solve and they probably did their best but in terms of the business reality if you if you have relied on cr and you know you weren’t again the super platinum diamond whatever customer you’re you felt a lot of pain you had to work around and you know and like all of us know how difficult it can be to you know some clr as well if you’re just splitting strings or whatever sure do it in t-sql but there’s all kinds of fun things you can do which are like spooky hashes to do in uh native sql yes like a spooky house and i have i have heard the argument hold on john i have heard the argument that you shouldn’t be doing like i’ve heard the argument that you know if you’re making new application all the restrictions on azure’s database are actually good because you shouldn’t be doing those things anyway which i think is fair like you know like it’s important to make the mess and taste good so you know the the uh the children like to take it like you can say that um and you know like i’ve had the displeasure of using the service broker and transactional uh replication replication so you know like if if we can spare the next generation of developers of dbas from those technologies like okay but it isn’t true that like every single restriction like you know like i had a problem with the hash bytes and at the time i was like kind of a pseudo diamond platinum member so i asked microsoft for help and i thought that that that they would they would resolve the scalability issue instead they said oh why don’t you just use clr and when we did so you know like like if like if the vendor itself is saying hey you know there are some limitations on our platform clr is still good then use i i think the argument that clr functions are an ancient relic of the past to be looked at by historians only isn’t going to hold water especially because i looked at sql server 2025 stuff today and they talked about the freedom to use different programming languages within sql server and you know what functionality used to give us the freedom to use different programming languages in sql server take it away joe clr yeah um go ahead eric no i would you know defend it sean defend it so no my my thing this is again my opinion here just opinion of one guy yes is is that one very handsome gentleman oh thank you just for you uh if you’re using clr you’re doing it wrong that’s just my opinion because you like for your example for hash bytes i i understand the the motive behind that i don’t think putting in the clr is correct i think for the amount of money that you’re paying for sql licensing but if you’re gonna do a very cpu heavy amount of hashing do it on the client side do it on another on another application server why do you want to do it on your database server you know if you look at at some of the stuff that i i don’t know if you remember because this is getting pretty old because i’m pretty old is there used to be uh soap endpoints for sql server remember those right yeah again why that’s what a web server’s for like i don’t know that this is not a there are things to make developers lives easier there are things that are you know there’s a lot of things that can make your life easier for example you can take four grams of ibuprofen a day and feel amazing and nothing hurts it doesn’t mean whatever hurts still doesn’t isn’t broken and wrong it just means you don’t necessarily feel it today well it’s just that’s that’s all mind over matter sean well it is just pretend it doesn’t hurt that’s right but but the clr thing i mean you also have to look at the model in which it runs there’s a bunch of stuff could i see clr for a couple things yeah i don’t agree that the geometry and spatial and stuff data types where it ended up being clr i think those should have been a native item uh i think the format function too yeah i mean there’s there’s a lot of stuff again there’s a lot of stuff i don’t think should be at a database level i was kind of upset when xml got thrown in there do you really want to do xml parsing and how else do we store query plans sean we need our query plans stored somehow xml was the choice so yeah if you if you watch that which i i would i would encourage you to watch that uh intense but you know i think again all that’s old is new again right we’re basically getting soap endpoints again uh you know uh the clr what do you mean with the vector stuff right i mean kind of yeah right like you’re getting all this stuff all over again i don’t i wish there was a different process i again i i don’t have any say into that i don’t know anything about that i’m just saying as a as a dbn developer who would use it there are times when it’s really nice but there’s also the thing of well it lets me do a i’ve been tasked with doing a i’ll just do it and as joe said service broker might fit some of those does anyone here want to deal with service broker not particularly no but like where where i where i sympathize with the need for these things in a database is that so much of that stuff needs to end up in the database anyway like short like like like what you were saying yeah you could use like you could offload the generation of the hashes to uh to an application server something that is far cheaper to have you know three billion cpus in and get it done quickly but all those hashes need to end up in the database anyway so then you’re looking at like getting those all those hashes from the application server into the database and if you’re at the point where generating all those hashes is so computationally expensive that you need them offloaded then like getting them into the databases like that’s going to be kind of ugly too and if you need them as part of some etl process it’s like like like when you’re swapping all this stuff around so like like i understand why all this stuff ends up is an like something you can do in the database like i understand why like you know like why like why people need to do this like where the data lives but um i mean like you’re all you’re you’re also right in that like you know the limitations that are imposed on azure sql database tend to keep developers more honest about what they put in the database right because like obviously a lot of the stuff that like you can’t do there is just like oh they’re kind of like you know what thank god you can’t do that here like like i’m actually kind of relieved you’re not you’re not allowed to do that anymore uh but really let’s be honest the the most bright shining beacon the most the best part of azure sql database is that it uses recommitted snapshot isolation by default can we can i get an amen can i get an amen and read committed snapshot isolation by default when is that when is that coming to on-prem sean tell us all about it no clue you can enable it you can enable it should be but should be by default though yeah there’s there’s there’s something there about um the limitations actually keeping you in check i don’t necessarily agree with limitations but i understand the the need for them in certain locations i again i don’t think any of the offerings from any of the cloud providers are inherently bad i just feel that people look at them and treat they want to see them as new and shiny but also treat them as the old thing that they’ve been using forever yep and there’s a there’s a mindset there’s a dichotomy shift that needs to happen when you do that and so many people are stuck in the old mindset and then they bring that to the new and it causes problems i don’t see a problem if you’re in the new mindset and you’re doing it with minus the no one wants to be an expert everyone wants to be lazy you know oh ai write me a query okay cool copy paste next or like you said orm generate me a query because i’m too lazy to to write a where clause yep you know and instead i get a 17 long union all yeah it’s yeah yeah it’s it’s the the general uh you know like um there there are other words for it for the ai era but you know it’s it really is the the general indemnification of above people like like in everything that they do uh like you know like just abstract all the hard thinking away to these other things and you are free to just do whatever like you know like indulge in whatever dalliances you know give give you a sense of whimsy at the moment and like there’s no one it’s very hard to find people who are very deeply invested in things uh to the point where like you know they could write a book or even like a blog post about something that shows any level of like real technical understanding yeah you don’t have to write a book right it’s just understand like you said about rcsi like when’s that why isn’t that standard how many people could even you know give a reason why that would be a pro or a con right how many people would even know about isolation level yeah well yeah as i found kendra little something about balloons yeah something about yeah kendra little world-renowned balloon expert are they can tell if they are red or not it’s yeah yeah i would know i would like that depending on your isolation level no wait no it was marvels it’s always right it was like black marvels and white yes yeah see but the original the original post on that was jim gray actually r.i.p uh jim gray was the original person to write a post about that and the funniest thing is that post was about snapshot isolation not about read committed snapshot isolation but it has it has slowly morphed into the oh read committed snapshot isolation has all these problems but no the original post was about snapshot isolation but that i mean that my point is like i don’t expect someone i would expect isolation levels to be a basic thing the developer understands like if you’re writing uh something for linux uh or windows right you’re going to use an api that’s publicly available i don’t expect you to understand how every little thing in the kernel works right to use that api but i would expect if you’re having to give a handle to something or you’re having to give a you know whatever to a thing that you understand what that is that you’re supposedly working with right like the base just the basic yeah i was once told by a microsoft support person that snapshot isolation did not apply to comstore because comstore doesn’t store data in pages snapshot isolation only applies to roster data that is a very interesting take so if we have a moment of silence for that um but i actually do want to talk about sean’s uh well why don’t you do the hashing in the application because you know we are people who want to be experts and i like to think that many of our viewers want to be experts too because you know why else would you watch sepa server content on youtube right in fact i even know some experts who won’t even go that far that’s true um can’t consume it undigested uh for our workload and i’m not saying that it’s true you should always do hashing in sql server and i’m sure there’s some cases where it would be better to not do it in sql server um for our workload um for our workload it’s a very big etl process random middle of the night um these hashes can only be calculated like in the middle of of etl so like just and if you want in etl like data size i don’t know like 10 billion rows and 10 terabytes of data so like if i wanted to do in the application like even if it was like free to compute in the application like like zero cpu zero everything like i don’t know how i would get the data back into my 10 terabytes of staging tables efficiently if you do an updated single threaded and like you know like like the updates are going to be way more expensive than the hashes or if i restart all the data again to get parallelism again that’s more expensive like like for i mean like it’s not so much that like the hashes didn’t cost very much with respect to the rest of the workload um well in theory they didn’t cost very much the hash by tip limitation made them cost much more than they should have um i think the other thing that makes it easier for us was you know like we have like a 96 core server 112 core server whatever this is running in the middle of the night like i want that server running as hot as possible i want to be able to like uh like a steak on that server you know like like they’re like you know like talking about like like like like like like alerts for using too much memory like you have cpu at like 90 95 it’s fine it’s it’s 4am you know like who cares like you’ve been a nice bath in the morning yeah like like even if there was some way to get my hashing outside of sql store like i don’t think that would mean fully impact the size of the server so for my workload you know for my special workload which no one else could possibly talk about or know anything about and now the rules apply because it’s my special important workload or it was i don’t think hashing would work on the on an application server and i mean like i don’t know like you know like we can joke about using other programming languages and sql server this is the direction microsoft’s going you can see a lot of them run for a while i mean it’s so whatever whatever feels unreasonable to me too on rare occasion if you have something that that that tsql is very bad at or it’s impossible doing tsql and like maybe it is a maybe it is a shortcut and you’re better off like not doing it in an ideal world but sometimes shortcuts are the fastest way to get your destination yeah so like what right so programming languages outside of tsql that we can use in sql server now it’s like the first one was sort of python and r i believe java came along what what did javascript get added is that one that that we can do now or is that oh not man that would be wild i don’t recall no okay yeah i don’t i don’t have the full list because it’s not something i do very yeah i actually i think i ran python once a sql server yeah it wasn’t very good i don’t know why i was doing it maybe i wanted to write a snarky blog post or something well you’d have to be the that’s with the ml service yeah yeah yeah that’s right that that whole thing yeah that’s a whole data cluster well r.i.p yeah bdc yeah but uh so i guess you know it is somewhat heartening that microsoft is slowly chipping away at the dumb stuff that people would need clr for like 2025 is getting regex and a whole bunch of regex functions we got string ag we got string split so like like like there is some movement in the right direction for people to not have to implement their own dumb stuff um you know like like there are some things getting added that it’s just like oh cool like maybe like light at the end of the tunnel stuff where it’s like oh great like i don’t have to do all this nonsense myself i can do it you know like there’s there’s a there’s something built in for me to do it with so like like that is heartening but um you know i i i it would be nice if uh sort of like when you’re writing t SQL and you run into something that is like generally not a relational strength um you know like like a lot of a lot of the string stuff would fall into that um some of like some like the broader mathematical calculations would also fall into that but like it would be nice if there were you know things you could do like in a like a way that were was relationally friendly but just in a different language and that and that translated a little bit better um you know like like even if you you think about just like you know like something as simple as like a b tree like like people people have this weird uh expectation of indexes that once you get into an index you can go any which way and jump around and do all this other stuff but like like what does a b tree really do what’s its strength it gives you a good starting point and you can go this way or you can go this way from that starting point you can’t go both ways and come back up and jump around and like like you have to do like another piece of work if you want to go in a different direction or if you want a different starting point so like like people do have unreasonable expectations of like databases and certain database structures but it would be nice if there were ways to do relationally friendly things and maybe like a language that wasn’t directly sql i guess is my point like there like there should be a way to to work with data within a database that is maybe not just sql like like sql because obviously capable of a lot of stuff but there are many things like when like like you were talking about xml earlier like some of the xml parsing queries that i’ve had to write i’ve been absolutely insane and like the things that i’ve had to learn about xml just to get data out of sql server i’m like why am i doing this like why is there not just a function that produces this view for me like i shouldn’t have to do this but just enable all the automation yeah okay fine but like still like if i’m trying to read from an extended event file like you know like there should be something that just like a like some sort of built-in function where it’s like okay i want to read data from this file i want to read xml and then parse it i want you to just give me the the contents of the file in a tabular format you like something should be able to like look at that file look at some metadata maybe even look at like a format file like the like bcp and bulk bulk stuff does and be like oh cool this is what’s in the file these are the data types these are the columns like and just like give me a table format for that great like that should be easy but no we’re still having to do all this terrible work you can do it with uh powershell you can do it with powershell online but uh someone but i think but that’s the api right like that that is the api that’s given to you via yeah would it have been implemented different i think it could have been implemented different do i know implement it better no but could someone theoretically smart put a put like an abstraction between me and that to make it easier yes it yeah it would be nice in in your example especially for the xml as as someone who’s also done stupid stuff with xml and then said i’m done with this and walked away is is you know would have been nice food of come back relational right if it would have come back as maybe some type of temporary table that you could have just got stuff on or whatever i don’t know what the implementation would look like like just one of those streaming window functions or streaming um table table functions that like a lot of the other dmvs work from yeah yeah and that would be great and the i think a lot of the thing is you’re trying it’s always round peg square hole right it’s like let’s take something that is is document object model that’s not made to be anywhere near you know it’s made for a specific implementation and hey let’s let’s go ahead and just shave those corners down and shove it through that round hole well okay but you know microsoft are the ones who chose to store many important things in xml system health extended event query plants yeah but nothing switched over to that except for like a couple goofy new views like uh but like this is there’s just so much in sql server that like in order to get information from it like it’s stored in xml so it’s on you to do that there’s like it always struck me as odd that you know you would find these blog posts with like you know like really detailed examples of like how to shred the xml to get this and that from it and there wasn’t just like some abstraction to be like oh i want to select this xml stuff i can just do select star from this thing it shows me all the columns and then i can choose which columns i want like to me that just that seems like hey let’s make life easier for the people who have to deal with this all day not make them also have to be xml experts so like i don’t know it was always weird that that wasn’t a thing but we have straight we have straight very we have stayed very far from the cloud sorry now do unicode no draw the line somewhere yeah no the getting back to the cloud though i like i said at the very beginning i think there’s a lot of good use cases for it i think it’s a tool like anything else you know just like joe it’s a tool and you can use it for good or you can use it for bad you can use joe’s joe’s etl strengths to get 10 terabytes of database loaded in once or you can use joe’s uh evil uh you know whatever i don’t know what his evil name is cloudy joe instead of regular joe yeah not sunny joe and rowdy joe all right sean all right so here’s okay well give me here’s the issue hang on hang on hey joe hang on one second uh i just want to do a time check here sean do you still have a hard stop in about 10 minutes i i can go another a couple more yeah all right cool so joe i’m gonna i’m gonna give you the floor to make your statement but then as is your unbiased moderator in this discussion i do need to get closing statements for the thing and then we will we will end this this episode a bit obscene i don’t know how long this has been at this point but uh a bit of thing yeah it is obscenely long at this point but i’m happy with that it’s not going to be a two-parter anyway joe you have the floor bang the gap uh i’ll start by saying this has nothing to do with the cloud ah but like i feel like there’s a little bit of like uh you’re in trouble now showing like disingenuous a little bit maybe like not sean of course i’m not sure he’s he’s above reproach but like i feel like if you ask some people like hey sql server 2022 uh i want to do a bunch of like regex stuff in sql server some people might say oh well you’re a dumbass like why you want to use all your cpu power doing regex and your very expensive licensing like you should do in the application but now in 2025 where we have like built-in project stuff you know microsoft just uh gives it to you i wonder if those same people would be like oh yeah i mean sure you know like it’s important the according to data where it lives and you can do the filtering as low as possible and you can send less data to the client and you don’t have to do all this comic you don’t have to split your code up and you know network latency blah blah blah so like i do feel and i understand like you know like if something isn’t supported in sql server well it could be natural to say well it’s like you know do it somewhere else but you know it’s kind of like a you know it’s it’s it’s a temporary convenience right because if you go far enough in the future i’m sure every possible useful thing will be supportive 10 minutes in the future yes all right there’s a one is a wonderful thing that you just said i i heard most of the words in it it was good they sounded fantastic all together like that so we’re we’re going to close out this episode of bit obscene we’re going to get closing cloud statements from each of you uh i’m gonna i’m gonna flip a coin to see who goes first well i would but i don’t have any coins uh so uh since since i know all my coins have been confiscated they’re being melted down into pitchforks uh so since we started with joe we’re going to start with sean for the for the closing closing statements here so sean uh the floor is now yours uh until you stop talking yeah the saying the i think the nlb always it’s it’s a tool i think it’s a good it’s a good tool that fits a lot of places but it’s not really the multi-tool right it’s if you’re a multi-tool you do a lot of things you might do some of them okay many of them okay but you’re not doing anything very great right people go to the multi-tool because they’re like well i can go do this i can go do this i can get it done it might it might remove the it might might get the screw up but i’ll strip it or it might uh cut this but it’s not going to cut it really well it’ll but but it’s enough to get the job done and that’s how i see it it’s when you start having to split your offerings so that there’s all these individual specialized things it makes you makes me wonder like is this the right thing or is are people going to know enough coupled with people just not either care i don’t know if it’s not caring or not wanting to learn or what have you you know i’d really be interested to learn more about that from individuals but just the the lack of wanting to be experts just getting onto the thing and going you know i i think the cloud is a good thing um i’d arguably say i’m happy that it’s here for some of the things that it’s given us but as a you know cio quarterly oh the cloud is the hotness put all your stuff in the cloud let’s do it i mean you’re pretty short-sighted for that you got to have you got to step back and say what makes sense how do we put the whole application life cycle in it how do we refactor things or if we’re net new what what do we need to do to get the correct performance get the correct items you know is it a level that we’re okay spending uh because as we’ve seen you know there’s been a lot of companies with blog posts about pulling all their stuff out of the cloud going back on prem saving tens of millions of dollars a year uh even with their costs and and it’s um so so it’s a it’s a tool use it well i have nothing against it uh on the you know just in general it’s just how it’s it’s like anything else how people misuse it and then don’t read the fine print and complain or say oh this sucks it doesn’t well yeah you use the tire as a you know to cut your tomato of course it didn’t work well and the tomato squished i’m you know take 10 seconds and think about it but i don’t know if we can have that today so overall cloud okay if used in the right context like everything else otherwise you know i’ll take a part from apple and say you’re holding it wrong and uh that’s it all right well as long as those 10 seconds are billable i don’t mind thinking about it all right cloudy joe hit us with your closing statements here i would say that if you can’t cook a steak on a server you don’t truly own it and you should fully understand what that means before you make decisions if you want to move things in the cloud um like i i mean i i’m sympathetic to you know i don’t know you have some startup and like the thought of well you know we’re going to get hardware and dsn number one and then we’ll get hardware and dsn number two which has to be far enough away and we’ll set up an availability group and backups and we’ll define our rto’s and uh rpos and we’ll you know do failovers and test our hdr and we’ll we’ll like we’ll like do drills and like you know like uh cut the links and turn servers off and you know make sure that we’re always up when we need to be and we’re not losing too much data i mean like that definitely i mean that’s a pretty intimidating thing right like it’s not like how it used to be in the good old days where you just had a server and uh like under someone’s desk and you know the server and a prayer that yeah that has served as a production um and like i can understand why you wouldn’t want to take all that on why you wouldn’t want to hire someone to do that i mean what you know if you hire the wrong person or you know like like what if you hire a shady data center because you know like plenty of those i mean you don’t even know like what the data center is doing like like they might even be in the cloud for all you know so in your effort to avoid the cloud you might just be taking even even like another step away from what you control um and you know like we already said if you’re starting with the application it’s just easier from the beginning to say okay you know we’re not going to use clr because we don’t believe in freedom and we’re not going to use service broker we’re not going to use asian jobs and we’re not going to do cross database queries and we’re going to be used you know we’re going to live with our 10 milliseconds of vital latency which might spike up to like 30 seconds on occasion it’ll be fine um it’ll work right for us um like i think there’s something to to be said there i do think it’s also probably not easy to make the right decision if you are shifting to the cloud um it does seem like microsoft’s documentation does get better over time but like as someone who’s personally tried to look into this stuff for recently there are a lot of dead links still there are a lot of dead links and some of the dead links sound really useful too but then you click on them and it redirects and redirects and then you get a 404 like like it is surprised i found it surprisingly difficult to find like good cloud content in terms of like you know like hey like we did a migration we learned xyz or like here’s a really clean breakdown of what to choose when like if you want to choose azure yams or managed instances or azure sql tv or even some non-azure you know you want to go on uh amazon or google or i think oracle has a cloud uh maybe we shouldn’t uh shouldn’t shouldn’t talk about that yeah no no one goes to google let’s yeah all right yeah well like i don’t know if it’s all like like uh uh ai slap now or people hold their cards too close to their chest or all the knowledge is locked behind paywalls and you know consultants and fancy training but like i i found it pretty hard to get answers even to what i would think would be some basic questions like which platforms support clr you know like as someone who develops that like i i i want that like loud like red blinking letters like hey if we’re going to use a ccdcql database no clr for you like that is something which you know like it’s a decision microsoft made nine years ago they should fully own it so you know then like that’ll increase our chances of perhaps stumbling into making a good decision so to wrap all that up a lot of people use it i’m sure it’s good for some people i think if you’re starting new it’s definitely easier and if you’re doing a lift and shift hopefully you’re better at googling than i am or you hire the right consultant and you can maybe get to a good enough answer all right wonderful wonderful you said joe what are your closing thoughts eric my closing thoughts my closing thoughts are thank you both for joining us on this joining my temporary co-host and my potential temporary replacement co-host for for joining us uh uh at here at darling data headquarters on this episode of bit obscene uh brought to you by darling data and the good folks at beer gut magazine uh and as always thank you for watching i hope you enjoyed yourselves i hope you learned something and uh maybe one or more of my temporary co-hosts will see you in the next episode of the bit obscene podcast all right thank you very much

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.

SQL Server Performance Office Hours Episode 43

---

Questions:

•  Any general thoughts on using indexed views in Enterprise Edition for performance tuning use cases outside of OLAP? Good? Bad? Evil?
• Is there a performance difference when using UNION vs UNION ALL inside an EXISTS with 2 or more queries?
• Any tips for making DML faster if all I can do is change the query text? I know the OPTIMIZE FOR tricks for changing the shape of the plans, but they never seem to make much difference.
• In your opinion, what is the hardest thing for people to grasp about databases?
• Okay seriously why do you hate logical read for query tuning?

To ask your questions, head over here.

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.

Optimizer Rules I Wish SQL Server Had

---

Video Summary

In this video, I dive into some optimizer rules for SQL Server that I wish we had, focusing on how they could improve query performance and make our lives easier as database administrators. I start by showcasing a new rule in SQL Server 2025 that optimizes queries written using `LEFT OUTER JOIN` with a `WHERE` clause to remove nulls, demonstrating why it’s more efficient than the traditional approach. Then, I explore other rules that could benefit us, such as decoding case expressions that produce literal values and transforming row goal costing into more intuitive query plans. Throughout the video, I highlight how these proposed changes would simplify query optimization and reduce unnecessary overhead, making our work with SQL Server even smoother.

Full Transcript

Erik Darling here with Darling Data. Look at this wonderful logo, this steadfast forever logo of mine. I’ve tried various redesigns of this, but none of them quite have the same feeling, you know? Logo gives me a feeling. Today we’re going to be talking about optimizer rules that I wish we had. And when I say we, of course, I mean SQL Server, but we being SQL Server users, we would have them if SQL Server had them, and that would be wonderful.

I’m going to show you one thing I’ve talked about before, a new rule in SQL Server 2025 that I think is just grand. And then some other rules that it would be cool if the optimizer had available to it. So we’ll do that. Down in the video description, since my job is making SQL Server faster in exchange for money, you can do things to support those efforts, like hire me for consulting. I can make your SQL Server faster in exchange for money.

You can buy my training, and you can learn how to make your SQL Server faster, again, in exchange for money. That’s buying the training. You can support this channel. That’s the sort of kindness of your heart thing. I suppose I am teaching you how to make SQL Server faster. You’re getting most of the money for that one, though, since this is free.

And of course, you can also ask me office hours questions. You can ask as many of those as you want. And like the trained monkey I am, I will answer them. And of course, please do like, subscribe, and tell a friend. I understand if some of you out there want to keep this channel a secret. Keep it under wraps so you can maintain your competitive alpha SQL DBA advantage at work by knowing all this stuff that no one else knows.

But sharing is caring, my friend. So, you know, do spread the good word and all that. By the time you watch this, it will still not be 2026. But these events are obviously happening in 2026 because March of 2025 is long gone. So in 2026, I will be at Datatune in Nashville, March 6th and 7th. And I will be at Data Saturday Chicago, March 13th and 14th.

We’ll be a little bit closer to baseball season. I’ll be about ready to leave the house again. I will have a reason to live, as they say. Okay. With that out of the way, though, let’s talk about these optimizer rules over here. We’re going to go over this way and talk about some stuff. Now, this is my SQL Server 2025 instance. And I got a couple of queries here. And one of them, well, they both have hash join hints.

I’m going to show you why they’re there in a minute because it’s quite frankly embarrassing. But one of them is operating in compatibility level 160. That is, of course, the highest compatibility level if you are on SQL Server 2022. And this rule is not available there. The database default for my database over here is, of course, 170.

But just to be explicit about it, I am telling I’m showing you that this is compatibility level 170. Now, if I run these two queries and we look at the resulting query execution plans, we will see what the rule that I am talking about in action. Now, the first, we’ll go back to the queries for a moment. This is a query pattern that I have had to argue with many a person about over the years.

And I say, you know, this maybe isn’t the best way to do things. You should maybe try writing this as not exists instead. Might be a little bit more efficient. You know, might be something to look at and they’ll say, well, I would, but I don’t know how to write a not exist query. And I say, well, my rates are reasonable. I can help you with that. It’s pretty easy. Right? Not exists. And then a thing.

And then you’re done. Right? So don’t forget your where clause. So this query is essentially looking for rows in the user’s table that don’t exist in the post table. Wonderful. This is, but again, this is not the best way to do that. This is probably how I would say, no, don’t do that here.

But we look at the query plans. We’ll see that the first one has done a hash match left outer join as I have told it to do. And then we have a filter operator. And of course, I’ve talked about all this stuff before in the past. So yes, this filter operator is responsible for removing rows after the join where the ID column in the post table is null.

Remember that it with outer joins that null rows are preserved or non matching rows are preserved. And we can, we can filter them out. We can filter the non matches out. And this, this works well for, well, I mean, it works with the, using the clustered primary key on post because it is not nullable and it does not have any null values in it.

So if nulls are produced by this join, it means that the rows didn’t match. Okay. But the, the, the way that you would normally write this query to get the query plan that I’m about to show you would be using not exist. But SQL Server has a brand new optimizer rule, which says, hold on.

Hang on. I know, I know what you wrote, but I think I know what you meant. And I’m going to do things a little differently. You’ll notice that there is no filter operator here. We just have a hash match left anti semi join.

Have to be very careful saying that one, lest you get sued. So, but this, this query is essentially removing the non matching elements at the join rather than fully joining everything together and filtering it out afterwards. I think this is wonderful. High five, Microsoft. You did it.

The reason I have the hash join hints in here though, is cause golly gosh, without, without those hash join hints, uh, things, things didn’t look good for either one of these queries. Let’s just to show you, right. Let’s, let’s run this. Uh, you know, this is not going as fast as it did that first time.

And, uh, that’s not good. These query plans are embarrassing. Yeah. I mean, the only thing that could make them more offensive would be if they were parallel merge join plans, but, uh, they, they basically follow the same pattern where now we have a serial plan, uh, which is a shame for the number of rows that we have.

Uh, and now we do a merge join left outer join, and then we have the filter and, and then down here, um, of course we have, uh, the, the, almost the same thing where it’s, it’s serial and we have, but this is a merge left anti semi join. Not a good time. Not a good time. Uh, and of course for completeness, another way to think about writing this query aside from not exist would be using the fabulous except operator, right? Just write the query like this and, and count this, right? That, that works pretty well. Uh, remember both of these queries got back, uh, 1 0 3 0 9 8 7.

And if we run this one again with the hash join hint, we will get back 1 0 3 0 9 8 7. And we have our left anti semi join plan. So all that’s great. Right now that is wonderful. All that is fantastic. Let’s talk about some other optimizer rules that might be nice for us to have.

One of them is this. So a lot, a lot, a lot, I’ll see people write, uh, create a view or create or alter a view. If, if you’re, if you’re hit with that. And what they do in the view is have some sort of case expression. And the case expression usually allows, uh, a user to like, you know, uh, supply like maybe a more user friendly search term for something.

Like question or answer or wiki or tag wiki ex, ex, ex ex, ex, ex, ex, ex, ex, ex, exerpt. Uh, well, whatever. Forgive these things.

Uh, but the, the problem becomes that when you, you, you, you, you try to write a query that filters on a case expression like this, um, for the, like the outcome of the case expression, then things get a little dicey, right? So let’s create or alter this view. I don’t know which one we’re doing.

What do you think we did here? Did we create or did we alter? I don’t know. Now, if you write your queries like this, which are not user friendly, because now you’re expecting users to have like memorized whatever data dictionary you have and know that post type IDs of one are questions. Like if we write the query like this, you know what we get an index seek into the, the, the index that we created on post type ID and, and all is well there.

But if, if we write the query like this, where we say, Hey, I want to find where the, the post type. Now this is using the post type case expression column. Well, we don’t use that index so well anymore.

Now, now, now we have to scan that whole index and things slow down a bit. So one rule that it would be really nice to have is, you know, just, and we could start simple here. We could start real easy and just, you know, for, for, uh, case expressions that produce like literal values like this, the optimizer could do a little something to decode that.

I realized that there are much more complicated case expressions in the world that could be written. I’m not asking to take care of all those because that would be nuts. Right. They’re like, you know, even if this were like case, what, like, you know, like a column or something, or maybe a column from a different tape, like different tables, that stuff gets real dicey.

But like, just for like when the case expression has some literals in it, like, why doesn’t the optimizer just sort of like reverse engineer that a little bit? Just like decode the stuff. So we get the nice easy seek plan.

Like why, why, why do you need to beat us up with this stuff? So that’s, that’s not cool. It would be nice if the optimizer could do that. Another one that I, I, I have a particular problem with is the, the costing around, um, uh, the, the row goal costing that leads to query plans that look like this.

So let’s run this thing. And what we’re doing is saying, we’re giving me the top one post, uh, ID from the post table, uh, that doesn’t have a vote, right? Ordered by the ID column descending.

This query takes around five seconds. And you can see most of the time in this plan. I mean, all of the time in this plan really is spent in here, right? Top above scan, almost never productive, right?

This is almost never a good time. There are very few cases where this works. And the, the reason why this, why this query plan pattern kicks in is because of some row goal stuff, right? We say select top one.

That’s a row goal, um, exists and not exists. Make it may introduce row goals in our queries. I have that whole video series on row goals or my presentation. So you can go watch that if you’re interested, even talk about this here, but this, this, this query pattern is almost never useful, right? We do get a missing index request, but SQL Server should really just not bother with that at all.

Like if we say, Hey, just hash join, right? Uh, this does not take five seconds to run, right? This takes, well, I mean, I guess, no, let’s, let’s, let’s give, let’s give one of those fancy optimizer things.

Uh, like, like memory grant feedback a chance here. There we go. Now that thing doesn’t spill anymore. Look at that two seconds instead of five seconds, because we get a hash join plan. Granted, we don’t have a missing index request now, but it would be cool.

The SQL Server would give us the missing index request and the hash join plan and just move on, right? Like why the top above scan? Why the nested loop?

Why, why are you beating us up? If you can’t have a top above a seek, don’t do it, right? Lay that thing out, get away. Right? Not a good time. Another one that I really dislike is the eager index pool.

So if we run this query, right, this query is going to take a little while to run. Oh wait, maybe it’s not. Cause I have an index on there. Ah, crap. Ah, yeah.

Ruined the whole thing. All right. Let’s get rid of indexes. I should have cleaned that up before I started writing this. All right. Let’s get rid of our indexes and let’s just say, do this. Right?

So now we’re going to get the slow plan that I meant to get. All right. This is what I get for like having multiple versions of SSMS open and working on multiple things at once. Not everything goes well on the first try. But this query is slow.

This query is not fun to watch. Right? And you can see this query took about 15 seconds and all of the time in here was spent eager index spooling. Right?

So we have 15.6 seconds here. The whole, well, like 15.7. It’s not that far off, but anyway, all the time is spent doing a nested loops join and building this eager index spool to make the nested loops join more palatable. Okay.

Now, since this query is using cross apply, SQL Server is not really hip to the hash join. Right? Query processor cannot blah, blah, blah. But there are other ways of achieving this that the optimizer could say, hey, I was thinking about building an eager index spool, but instead, why don’t, like, I understand this query. Right?

Seven seconds. And we don’t have the big nasty spool in there. Granted, like, this is still not like a well tuned query, but it’s so much better than the alternative. Eager index spools off of large tables, anything over like a million or so rows, they just completely lose their marbles.

And then, like, eager index spools, they get built single threaded. I can go back a little bit to talk about that. Even in a parallel query, eager index spools get built on a single thread.

Otherwise, they risk sort of deadlocks and stuff. They get loaded one row at a time. It’s very inefficient.

Spools, the code for those hasn’t changed in SQL Server seven. So, like, they don’t have any of the cool new, like, bulk loading and, like, other optimizations that things like temp tables have. So, like, if we come over here and we look at the properties of this and we look at the actual number of rows, we’ll see thread two got all the work.

And this isn’t just a quirk of this demo. This is every single eager index pool that gets built. It’s a little harder to show, like, the row by row loading, but you can see, like, taking 15 seconds for 8 million rows is not the sign of, like, a bulk load.

Right? Like, if you dumped 8 million rows into, like, a temp table just said, like, select into, maybe a second at the high end. Right?

And if you’re especially, I mean, if you’re an Azure, like, like, SQL database or managed instance, it might be longer because their tempTB is on, like, glue and dead frogs or something. But anyway, this would be a nice query plan pattern to have to take the, like, get rid of the eager index pool top one of these stuff and just sort of transform it into, like, a row number, like, type thing. Right?

Because this gets us the same query plan shape just without the eager index pool. Another particularly gross one is when you join on an OR clause. Right? It says, we don’t, we don’t like joins on OR.

Well, I mean, I don’t like joins on OR clauses because of what they do to, like, query plans and performance. But I mean, I guess fixing them is how I make money. So that’s, that’s okay.

But the optimizer really should be able to unroll stuff like this. The optimizer should be able to do a better job of, like, saying, hey, like, like, clippy style, like, like it did with, like, you know, one of the 2025 rule where it was just like, you wrote a left or join and let them check for nulls. Because I know what you meant.

I think I know what you meant in there. So when we run this query, right, and we say, select the stuff from the post table joined here on either the ID in the users table equals owner user ID or the ID in the users table equals the last editor user ID. Right?

We could join on this or this. Anything will do, right? Give me anything back. I don’t even know what this query means anymore. This query takes a rather long time to run. We’re at about 20 something seconds now.

And it’s still going. Chugging along here. Not easy like Sunday morning.

So 31 seconds later, we get this giant catastrophe of a plan. SQL Server should be able to say, hey, I think I know what you meant to do here. All right.

You should be able to unroll this a little bit. This is a very, very simple set of predicates. SQL Server should be able. And I’m using apply here sort of out of convenience. But if we write the query like this, it is no longer a 30 second ordeal. SQL Server just does a C or rather a scan of both of these indexes concatenates the rows and moves on.

We don’t have all that crazy junk with the constant scans and other stuff that we had before. The optimizers should be able to handle these situations, right? And I’m not, again, I’m not saying every super complex case should be covered by this, but there are some pretty easy ones.

Like, again, I’m a high school dropout and I can figure this out. People who work on the optimizer team can unroll this. We’re in trouble.

Right? Now, there is an optimizer rule that I’ve changed my mind about in recent years. I used to, you know, complain quite heartily about, you know, eager about CTE in SQL Server because there is no way for SQL Server to materialize them. Other database engines like Oracle and Postgres and I’m sure many others offer either automatic or hintable, like materialization of CTE.

This, of course, really hurts SQL Server when you reference CTE over and over again because you have to re-execute the query in them. They don’t materialize naturally in any way in SQL Server. So I wrote kind of a weird set of queries to try to find a good way to show you this.

But, like, if you run this inner part of the thing here, we get values 11 through 91 back. And if we run this query, basically what this is doing is showing me which IDs in the votes table sort of give me what the row breakdown is with different modulo math. And this will make more sense in a minute.

But when you look at these numbers, you can kind of see, like, okay, well, if I modulo by 91, I get 581,000 rows. If I modulo by 11, I get 4.8 million rows, stuff like that. So what I want to show you here is kind of why I’ve changed my mind about this in SQL Server.

And it comes back a bit to the eager index pool conversation where, you know, like loading data into the eager index pool, it couldn’t be done in parallel. It’s row by row. There’s no bulk loading, all that other stuff. It’s a bad time, right?

So, like, if, you know, Microsoft are willing to invest time in improving spools, then this would be a palatable optimizer rule. But because they’re apparently not, because again, instead we get fabric, you know, birthday herpes, we get stuff like this. So what I’m going to do is I’m going to write queries that will intentionally, like, load a bunch of data.

Like, this one’s going to put a bunch of data into a spool, but not return any rows. Because what I want you to see from the query plan is how, like, the difference in time between rows ending up in a spool and rows ending up in a temp table. So we do this. This one’s relatively quick, right?

The whole query runs for, let’s see, we have, oh, this thing gets all sorts of weird. But, you know, we have a clustered index scan of votes over here. This takes 1.1 seconds. And then by the time we get to the eager, we can ignore the gather streams operator here.

Just pretend this doesn’t exist. We spend about 600 or so milliseconds getting data into the eager index pool, right? The rest of the time in the plan, this is actually kind of a funny one, right?

Because we spend 1.1 seconds here. The gather streams is just like, oh, well, I take 424. Like, the timing gets all thrown off. Now, this is a completely roll mode plan.

So the time is supposed to go higher as you go from this part of the plan to that part of the plan. But things get all thrown off. And there’s a really funny one at the end, too, where, like, the entire thing has taken 1.6 seconds. But this is like 3.5, right?

But it gets a little bit more amusing, too, if we look at the query time stats. Because the query time stats, they largely agree with the query plan itself. Like, I took about 1.9 seconds.

I didn’t know. Where did 3.5 come from? I don’t know. But you can’t see that it took, like, 1.7 and then 1.6 here. The whole thing is a shambles.

Anyway, you can sort of figure out that it took about 600 milliseconds for the million or so rows to end up in the eager index pool. Which isn’t in the eager table spool, which is what it would take for CTEmaterialization. You couldn’t lazy load that.

But just comparing that with a loading data into a temp table, this does not take that long, right? Like, you see it’s 890 milliseconds here and then only about 100 milliseconds to get the rows in there. So, like, the slowdown is obvious, right?

And it gets worse as more rows come into play. So, if we do that same sort of top 99% query, and granted, like, even the 99% query is loading fewer rows because this is, like, loading everything where ID modulo 51 equals 0. This is only loading the top 99%.

I need that 99% in there to get the eager spool. It has to count the rows, right? So, if we look at this one, you know, we spent 900 milliseconds here. Again, we’re ignoring this thing.

And then we spent almost 8 full seconds loading 5.2 million rows into the thing, the eager table spool there. If we do the same thing with a temp table, it does not take nearly as long, right? It does not take 8 seconds to do that.

This thing takes, well, there’s 1.4 seconds here, and then there’s 1.8 seconds. Here’s, like, 400 or so milliseconds there. So, SQL Server does a much, much better job of loading data into temp tables. If we could make at least the eager version of spools, I mean, all spools should be equivalent with temp tables at this point, but the eager version of a spool were made more on par with the way temp tables get loaded, then CTEmaterialization would be a worthy effort in SQL Server.

But until then, it would suck, right? Not be fun. Not be amusing.

Like, I would be angry. There might be some okay trade-offs, especially with not rerunning some subtree in a CTE over and over and over again, but for the most part, I would be very nervous about big, eager spools getting data loaded into them.

Anyway, I talked for longer than I thought I would. It’s time for my protein shake. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And today’s Friday, so I won’t see you until Monday, but I do hope you have a great weekend. All right.

Thanks for watching. Bye-bye, Matthew Red Crossed Costord. means

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.

A Little About Locking Hints In Transactions

---

Video Summary

In this video, I delve into the fascinating world of locking hints in SQL Server transactions, sharing insights from a recent client project where we explored how to handle concurrency issues effectively. We start by creating a simple table and inserting five rows, then dive into demonstrating various locking hints such as `upd lock`, `row lock`, `repeatable read`, and `read past`. I explain why these hints are crucial for maintaining data integrity while allowing concurrent processes to run smoothly without stepping on each other’s toes. The video showcases how different isolation levels and locking hints can affect transactions, both within and outside the transaction scope, providing practical examples that highlight their importance in real-world scenarios.

Full Transcript

Erik Darling here with Darling Data, and we have a fun video. Today we’re going to talk about locking hints in transactions. I was working with a client recently, and we were talking about various ways to handle concurrency with transactions that might have to deal with each other in kind of funny ways. And when I started showing them locking hints, they were like, but wait a minute. If we start a transaction and we have a select query with these locking hints, isn’t it going to block the thing we’re trying to do afterwards? And it was like, no, it’s magic. And I’m going to show you that same magic here today. Down in the video description, you can find all sorts of helpful links where you can hire me for consulting, buy my training, support this channel, ask me offers hours questions, and of course, there are all sorts of things that you can do aside from that, aside from monetary exchanges, like subscribe and tell a friend, and then more people show up, and I don’t know, maybe one of them. We’ll do one of them will do one of the other things. And that’d be grand. That’d be wonderful. Leaving the house. Going various places. At least they’re closer than Seattle. Data 2 in Nashville, March 6th and 7th of 2026. And of course, Data Saturday, Chicago, March 13th and 14th of 2026. I’ll be doing pre-cons at both of those, so buy your tickets now.

Make me feel like a cool person who sold all sorts of seats and stuff. And I can say, hey, look what a good job I did marketing this thing. You can buy tickets somewhere around those links that are in the PowerPoint thing there. Datatune.conf and datasaturdaychicago.com. So I assume those are valid working links because I copied them from a very trustworthy web browser, internet browser. So anyway, let’s go talk about locking hints and stuff here. So what I’m going to do is I’m going to create a simple table and I’m going to stick five rows in it.

And what I’m going to do now is talk a little bit about transactions and whatnot. So because SQL Server does not have a select for update clause, when we want to, say, select a row that we’re going to do stuff with, that we don’t want anyone else to do stuff with, we have to take some additional steps. I’m beginning a transaction, declare, like having something to hold that value is good. So I’m declaring an integer up there called ID, right? And it’s null to begin with. And I’m going to grab row five. In real life, you would probably have some other logic here, like, you know, like, like, grab a row that needs work done. But for me, ID equals five works just fine.

And then after that select, there will be an update. What I want to do is just kind of show you like, like, a that like within this transaction, everything works fine. And what kind of queries outside of this transaction would be maybe affected by it. So let’s start just by beginning the transaction and setting the ID. So we’re just going to run to the select on this one, right? We haven’t touched the update. That’s kind of annoying. What I hate about this is that like, even though I’ve like done begin transaction, declare, select, if I try to run, if I’ve like paused now. And so if I try to run the update, it’s going to be like, well, you have to declare the variable ID. And I’m like, but I did. And I have a transaction open. Why don’t you remember that?

Anyway, we come over to this window. The types of queries that like that is designed to protect against would be competing queries. Like, so like if this is like a process that you’re writing, where like only one person should or one thing, it doesn’t have to be a person, like one thread or one worker should only be allowed to work on something at a time. The code outside of that process should like can access things normally. So like, I don’t even need a no lock in here. If I just say select from that table, I get all the rows back because the select with the upd lock, that hasn’t taken a lock that competes with a select just trying to read data. Right. So if I were trying to update row five, I of course would not be able to update row five.

But oh, sneaky weeky, I could update row four, right? If I do this and say, hey, go update row. Oh, wait, I have to cancel that first. It was it was being blocked. That’s why I just kept running without saying it did anything. But if I update this for row four, row four can go do something right. So that’s that’s nice. But if I were to try to run another select with an upd lock, then this this select would be blocked because the upd lock is like, wait a minute, I’m trying to upd lock.

You can’t upd lock to right. So like, you know, like if I had a where clause on this for where ID equals four, then let’s see. Let’s do a little experiment together. I didn’t plan on this. Just just occurred to me while I was doing all this. So if I say row four, well, that that that can go and do something right.

So but like the point is that if I were to try to take, say, row five now, right, because row five is the one I’m updating in the other window. If I were to try to do that now, SQL Server is like, wait a minute. Row five has an upd lock on it. You can’t upd lock that row two or you can, but you have to wait.

Now, coming back over here just for a moment, the hints that I have supplied to this query are upd lock, row lock, repeatable read and read past. Now, for a lot of processes, you know, the idea of serializable sounds great, right? Because, you know, serializable is quite strict and would prevent all sorts of strange phenomena.

But if you want this process to run nice and sort of like concurrently, the read past hint is often useful because it like you like basically you want people to be able to like just skip over anything that is currently locked. Right. It’s not like no lock where you can you’ll read the locked row. Read past is just like that locked row is none of my business. I’m just going to move on. I’m going to keep going here.

So like if we come over here and we were trying to say all this stuff with a serializable hint, we would get an error that says you can only specify the read past lock in a read committed or repeatable read isolation low. So that doesn’t do us any good. So but this query here, this will allow us to read everything but row five.

Right. Like if this were a no lock hint, we would get row five back with like I mean, I mean, didn’t change anything. So it would just be whatever data is in row five. It’s just a select query at this point. But so like we just we’re just like row five. You have a lock. We’re not bothering with you. Right.

You are not consuming any of our effort or energy here now. So like really processes like this. And what I want to show you next is just like within the transaction, those locking hints are fine.

But also like like for processes like this, you really have like, you know, to consider, you know, like like I mean, concurrency is obviously the most important thing, because if you want to make something like this multi threaded, you know, you want to have multiple processes, be able to take something and work on it.

Like, you know, design a queue type situation or design like a worker tables type situation. Then these types of locking hints become really important because a you want like every process to be able to go get work freely when it’s ready for work. But B, you don’t want it double working or overworking something that is a either like already in process or something that’s already been processed.

So like those are the things you want to like make sure you bake into a process like this. If I just hit commit on this transaction just to get rid of stuff, I’ll reload the table just so we start clean here. And then I’m going to run this whole thing.

But now with the update. Right. So now the update we have the begin transaction, declare an ID, select the thing we want where ID equals five and then go update stuff down below. So like this all runs fine. So like within this transaction, the locking hints that we’ve supplied with the select don’t don’t like mess up the update.

Like we don’t block ourselves in our own transaction. That would be crazy. It would be insane. Right. Just be bonkers. But we’re still like, you know, still holding stuff out here.

So like now if I try to run this query, now this query does get blocked because that update is doing stuff. If I want this query to do anything useful, I, of course, have to say with no lock. And now I can see the dirty, the dirty read down here where I have added one day to this.

Right. So these are all 1110, but this one’s 1111. Because if you remember, the update down here is just adding one day to that column. All right. Cool.

So this would also, you know, of course, block other updates to row five, row four. You know, we still get in there. But if we were to try to say like, hey, go talk about row, go mess with row five. This would get blocked. Right.

This just drags and drags because row five is currently locked in that transaction. Likewise, you know, you know, trying to select row five with an up D lock hint would also be be prevented. Right. Row four, we can still get at.

Oh, no, I hit insert. I’m never going to recover. But row four, we can still get at. Of course, this is still going to throw an error because you can’t mix serializable and read past. And this thing, of course, would get just not get blocked because we have repeatable read and read past.

But again, we’re just skipping over row five because row five has a lock on it. And we are just like ignoring. We’re ignoring that.

We’re ignoring any locked rows via the read past hint. So, you know, kind of a short video to talk about how process like processes like this generally work. Generally, you know, how to make them nice and concurrent if you want to multi thread them and how to make sure that those threads stay sort of within their own lane and don’t mess each other up.

But yeah, you know, just sort of a little primer for maybe some more videos down the line. Anyway, that’s what I wanted to talk about. And I’m done talking about it.

So now I’m going to go do something else. 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 are going to talk about optimizer rules that we don’t have. But well, we’re going to talk about one optimizer rule that we’re getting soon that I’ve talked about before.

But, you know, I want to talk about optimizer rules that I wish we had like that one. All right. Anyway, thank you.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

bit Obscene: When Will AI Fix The Query Store GUI?

---

Video Summary

In this video, I delve into the myriad issues and quirks of SQL Server Management Studio (SSMS), focusing particularly on its Query Store feature. As a seasoned database professional, I share my frustration with how SSMS handles wait stats and query performance tuning, highlighting specific examples where the tool falls short. I also explore potential improvements that could be made, such as better line graph visualizations for wait times and more intuitive default settings for frequently used features. Through this critique, I aim to provide a realistic perspective on what can be improved in SSMS while acknowledging the challenges faced by developers and database administrators in maintaining efficient and effective tools.

Full Transcript

All right, welcome back to the Barely On Life Support Bit Obscene podcast. My AI-generated co-host Joe Pilot is here today, and we’re going to be talking about the state of SQL Server tooling in, well, I guess 2025 is well nigh at the end, but, you know, not a lot of stuff gets done by the time the holidays roll around. So, I think we can call this a pretty clean wrap up on the year. Anyway, we are once again brought to you by Darling Data and Beer Gut Magazine, where you can find all of the finest beer and sports related content known to man. So, with that out of the way, take it away Joe Pilot. Tell us, tell us your thoughts on SQL Server tooling. Joe Pilot, You know, the great thing about AI is it doesn’t take holidays or overtime. I’ve even seen some government websites. They’re only open from like, nine to 5pm Monday through Friday. They literally just stopped working after, after like 5pm, you know, get some AI in there. We can get those government websites going.

Joe Pilot, You know, it’s like what Microsoft does with fabric when no one’s using it, they turn it off. Joe Pilot, So, yeah, it’s not it’s not an outage. It’s just, you know, there’s, there’s saving power. They’re going green. Joe Pilot, I’ve used.

Joe Pilot, SSMS not as long as some, I think a pretty long time. I think it’s been 2011. So, 14 years. Eric, do you remember? Joe Pilot, Oh, yeah, I do. Of course I do. I was I was sitting at my desk, and at a different job, of course, not this desk, much different desk. And I had been talking to my boss about, like some annoyance that I had with an Excel file, I think doing a VLOOKUP. Joe Pilot, And he was just like, Oh, you should just do that in SQL. And I was like, All right, how do I do that? And then a few minutes later, an email came through that said Eric needs SSMS installed.

Joe Pilot, This was SSMS 2005. And so this is this was around the year 2007 2008. And I got some some really nice IT worker. Well, I don’t even think he came by my desk. I think he just did a remote install. And he said, Go ahead and open it up. And I opened it up. And the rest is history. Joe Pilot, So yeah, so if you add it all up, we got like over 30 years of, yeah, SSMS experience. Joe Pilot, That’s true.

Joe Pilot, We definitely know what we’re talking about here. Yeah. Joe Pilot, I don’t remember the the date for this because it was such a traumatic memory. Joe Pilot, But you remember that the the dark rise of Azure Data Studio? Is that what it’s called? Joe Pilot, Well, do you remember?

Joe Pilot, I remember. Joe Pilot, I remember. Joe Pilot, I remember. Joe Pilot, I just blocked it out of my memory. Joe Pilot, Yeah. Joe Pilot, I was so s. And then that turned into 80s. And then. Joe Pilot, ADS needed an SOS.

Joe Pilot, So there’s foreshadowing. Joe Pilot, Yeah, a little bit. Joe Pilot, Yeah. Joe Pilot, And now, ADS is dead long live Visual Studio code. Joe Pilot, Yeah, I remember ADS and it was going to be like the new like, we’re not going to work on SSMS anymore.

Joe Pilot, Yeah, for the future. Joe Pilot, Why would you want to use that old yellow program? Joe Pilot, Multi platform, cloud native friendly. Joe Pilot, Yeah.

Joe Pilot, Yeah. Joe Pilot, Yeah. Joe Pilot, Yeah. Joe Pilot, Yeah. Joe Pilot, Yeah. Joe Pilot, Yeah. Joe Pilot, Yeah. Joe Pilot, The slow trickle on that was great too, where it was just like the future. And then it was like the future. Well, it’s more for developers, not for DBAs. DBAs still need SSMS. And then it was like, no one needs that. Joe Pilot, We got query plans, four years later. All right.

Joe Pilot, I mean, I was looking at an old stack exchange, or an old stack overflow question I asked. And yeah, it was about SSMS. And there was a very helpful comment by a gentleman, who informed me that Azure Data Studio was the future. So I really should just stop asking questions about this. Joe Pilot, Yeah. Joe Pilot, Why bother?

Joe Pilot, Deprecated, obsolete, old fashioned, no good, yellow SSMS program. Joe Pilot, Yeah. Joe Pilot, Yeah. Like, and like, I think, like, think about how much better we’d all be. If instead of doing their like, their like, their ADS SOS side quest, they just like worked on old, you know, good old yellow SSMS. Joe Pilot, Well, no, good old yellow SSMS has dark mode now. And it’s got, you know, a visual studio shell. It’s, it’s, it’s a hip happening place all of a sudden.

Joe Pilot, I mean, like, I’ve, I’ve, I’ve, I’ve, I’ve, this is true. I’ve heard a big tech company is like, you get attention as you like, to, to release a new product. Joe Pilot, You know, like, you know, like, I feel like, I mean, like, higher ups don’t use SSMS, like, couldn’t someone have just taken SSMS, like, just have to cut a blue paint on it, add Azure to the name and been like, hey, we developed this great new cloud first cloud compatible cloud native IDE for SQL Server, everyone gets all their promotions and bonuses. Joe Pilot, Yeah, it’s all great. We all celebrate and then we could like, do something useful. I mean, that’s Yeah, I’m really it’s just a matter of changing.

Joe Pilot, It’s like a matter of just changing a splash screen from SSMS to Azure SSMS or something, right? Joe Pilot, So I was, I was, I was going to share my screen, but the host is quite rudely. Joe Pilot, Oh, well, the host, the host is making you a co host, the host was unaware that you were trying to share your screen.

Joe Pilot, That’s the unpredictability of AI. Joe Pilot, So, I mean, let’s, you know, I’m sure I’m sure you can you can you see the yeah, no longer yellow and now dark mode. Joe Pilot, Yeah, I know.

Joe Pilot, It’s gorgeous. Look at that. Look at the sleek lines. Joe Pilot, Yeah, I like it. Joe Pilot, There’s a saying which I’m sure you’ve heard, which is I might have invented it. Joe Pilot, The opposite I might have said it first.

Joe Pilot, The opposite of love isn’t hate, but indifference, right? You’ve heard that one. Joe Pilot, I sure have. Joe Pilot, And I feel like that one applies both to SSMS and the query store. Joe Pilot, All right.

Joe Pilot, I’m going to open up the query store GUI. Joe Pilot, Oh, come on, Joe, we’re humans here, please. Joe Pilot, Why? Joe Pilot, Like, like, what? Like, why are you going to show me this graph? Like, like a, like a bar to like out of all the beautiful detail data available in query store.

Joe Pilot, You’re going to show me a big blob of green and a slightly less big blob of blue. Joe Pilot, But look at the green. Joe Pilot, And then a little tiny blob of an unknown color because it’s like I have never gotten any value whatsoever out of this.

Joe Pilot, No, no. Joe Pilot, Like thing that appears to be some like intern project that was done like 10 years ago. Like, like, this is not how people use use databases. Joe Pilot, So like, you’re not, you’re not wrong.

Joe Pilot, You’re not wrong. Joe Pilot, You’re not always to like go to here. Joe Pilot, I mean, you’re not entirely wrong because the query store GUI was actually the product of a Microsoft hackathon. Joe Pilot, It was not intended. It was not planned. It was an unplanned product pregnancy. And it somehow got a commit was made during a hackathon. I don’t know someone got drunk under a table. And now we’ve now we’ve got this, this thing.

Joe Pilot, I didn’t know that. Joe Pilot, Yeah. Joe Pilot, I might learn new things. Yeah.

Joe Pilot, This is why you’re a good man listen to. Joe Pilot, Yeah. Every once in a while. Joe Pilot, The thing is, like, every time I open this, no matter what database, it always defaults to. Joe Pilot, It does.

Joe Pilot, There’s no way to change that default. Is there, Joe? Joe Pilot, I don’t believe there is. Joe Pilot, No, there sure isn’t. Joe Pilot, And worse, I think worse is that there’s no way to search for anything meaningful in there. Like, you want to find a store procedure, you want to find some query texts, where do you go? Joe Pilot, You click one of these buttons. But if you, if you click this one, you’ll see.

Joe Pilot, Yeah. Joe Pilot, With additional details. Yeah. Joe Pilot, And you have the audacity. Joe Pilot, Mm. Joe Pilot, To say, Joe Pilot, Show me the max.

Joe Pilot, Oh, oh, dear. Joe Pilot, We, we, we, I can’t do that. Joe Pilot, Huh? Huh? Joe Pilot, No. Joe Pilot, You might be thinking, well, Joe Pilot, You know, I’m asking for the max. I have a pretty big, like, one rep max. You know, it’s a lot of effort. So, like, Joe Pilot, Like, maybe, maybe the men will be easier. Right? But it’s, it’s not easier.

Joe Pilot, Yeah. Joe Pilot, You know, like, no, no, but hey, if you want standard deviation, because, Joe Pilot, Hey!

Joe Pilot, Everyone knows that standard deviation is the most useful performance tuning metric. Joe Pilot, So I’m not entirely sure what a standard deviation is. Occasionally, I’ll try to figure out what an order of magnitude is, but they both escape me pretty well. I like the averages in there quite a bit. Joe Pilot, Well, these are the average.

Joe Pilot, Ah! Joe Pilot, But you know, if you want, you know, I can cook this and I can spend the next hour cooking these. Joe Pilot, Yeah. Joe Pilot, And it’ll always say, Can I connect database? So if you’re interested to know, Joe Pilot, This works in SQL Server 2016. It does not work in 2017, because 2017 introduced query store waitstats.

Joe Pilot, Sure did. Joe Pilot, And apparently, presence of the query store waitstats results in a generated query, which makes no sense. Can’t compile, it errors out. I think I actually traced the query and put in a blog post like eight years ago and like screamed to them. Joe Pilot, And then I said, I’m in the void about how dumb this was, and I used to be the fix. And, you know, as you can see, like, I’m on the most modern, you know, the sleek dark mode, SMS.

Joe Pilot, It says 2015. But I swear I download this recently. And this bug is still there. It’s… Joe Pilot, Yeah, so I don’t know. I don’t know about, um, what was it? There was a queries that were like missing top without an order by? Or was that something else? Joe Pilot, Might have been? Yeah, I remember being kind of snarky about it.

Joe Pilot, Yeah. Joe Pilot, Maybe that’s why I didn’t get it fixed. I’m getting my, you know, what I’m due. Joe Pilot, Everyone’s busy getting query store on AG replicas implemented. So…

Joe Pilot, I don’t know, like, it’s… Anyway, one thing that I’ll admit a little embarrassment here, like, I like to copy and paste sometimes, like, I’ll just, you know, like, grab this, copy it and paste it. Oh, I doubt it was fine. Oh, I don’t want to talk about what I’m doing there. Joe Pilot, Right. Here, this one. Look at that beautiful, you know, who needs more than one line? Who needs line breaks? Joe Pilot, No. Zero.

Joe Pilot, You don’t believe in line breaks. Joe Pilot, No. Joe Pilot, Now, to be fair, and this is actually something I learned while preparing for this. If you click this button, it then, like, adds line breaks. Joe Pilot, Oh, that’s fantastic.

Joe Pilot, I guess they’re not really, like, copy and paste fans. Joe Pilot, But what does that mean? Containing a line break? Joe Pilot, That’s a fun comment to put in there. Joe Pilot, That’s a… I’m like, and I got all the real, like, who communicates by object ideas? I have never had a single, like, oh, man, like, show me object ideas, please.

Joe Pilot, That was like, ah, you know, like, there’s just so many, there’s just so many opportunities here. Joe Pilot, For this, this one small, tiny parts of SSMS. There’s just so many opportunities for improvement. You know? Joe Pilot, Yeah.

Joe Pilot, There’s just so many opportunities. Joe Pilot, Like, when I, when I, when they, or rather, when they introduced wait stats, I was like, surely, there will be some line graph that will show me wait stats over time for the database. Joe Pilot, Oh.

Joe Pilot, But no. Joe Pilot, What if we put all the wait stats together in one bucket? And then that way you could hear which queries have the most waiting time? Joe Pilot, Yeah. But hey, is that min working?

Joe Pilot, Well, it works on the, and the grid format, but does not work in the, oh, with additional details. Joe Pilot, Oh, so no additional details available. Joe Pilot, Yeah, because I believe the additional details shows the wait stats on 2017.

Joe Pilot, Yeah, so I’m pretty sure it’s this column is the culprit. Joe Pilot, Excel, What are you doing? Joe Pilot, Just, uh, spray things.

Joe Pilot, Now, I can’t see that. Joe Pilot, I do try to be a reasonable person in my critique, you know, I work as a developer, I understand how to, I don’t want to be unreasonable. Like there’s some people out there who like, get mad that Azure SQL database or managers have up to like 60 seconds of IO latency. Joe Pilot, And like Microsoft calls these fine people unreasonable. Like I don’t want to be unreasonable like, like those people, you know, because yeah, yeah, I guess it’s a big program. I’m sure there’s a backlog of like 10,000 bugs. And they probably probably have like, I probably have enough people to work on it. Or like all the people were busy on SOS and ADS. And now they’re all working on a Joe Pilot. And they’re all working on a copilot. And you know, the, you know, the, the, the pleas of developers and DVAs to please fix this query store couldn’t connect to database very sad error, or simply unheard.

Joe Pilot, Yeah, you know, I feel like this is a great opportunity for AI. Because you know what, like, if you tell an AI like, hey, this, this, this query doesn’t work, fix it. Maybe you don’t fix it. Like, I don’t see how it gets any worse than Joe Pilot, It surely can’t make things worse. Joe Pilot, It’s like, or, you know, like, heavy, I optimize the, oh, wow, look at this. Like this beautiful, this beautiful thick bar graph showing you who’s boss.

Joe Pilot, There is one theory that matters here. Joe Pilot, Wow. That is something. Joe Pilot, So I mean, at the very least what they could do is say, is change the couldn’t connect a database screen to say thinking, and then they could say it’s AI ready.

Joe Pilot, Right? Joe Pilot, I mean, there’s all kinds of AI things, right? Joe Pilot, Like, you could have AI optimize, like, you could figure out, you know, like, which of these uses and user use, and you could default to that one, or it could guess your like business hours.

Joe Pilot, Or you could just have a little gear icon that says like, query store defaults. And you could pick your default. Joe Pilot, That’s, that’s not a modern solution. Joe Pilot, No, no, but no, wait, wait, wait, wait, wait, wait, wait, it could save that to a JSON file. That’s a modern solution, Joe.

Joe Pilot, I mean, it’s modern a while ago. Joe Pilot, It’s a modern solution for us. Joe Pilot, No.

Joe Pilot, Or like, I mean, I don’t have one of those cloud 24 seven apps. Thank God. Joe Pilot, Not yet. Joe Pilot, Like, I don’t care. Well, I don’t care what the server is doing it for him. Like, I just don’t care. So like, like, you know, like, like, like, all these, you know, like all these, like filters, you can have, like, change the date, which I like can’t find for some reason. Joe Pilot, I think it’s over to, is it over to the right under it’s under configure, isn’t it?

Joe Pilot, Or is it that drop down arrow? Joe Pilot, I don’t know why I’m having so much trouble. Maybe this is the new. Joe Pilot, Because I’ve done this like a million times.

Joe Pilot, Oh, dots. Joe Pilot, Oh, so many dots. Joe Pilot, Is this not in? Am I just an idiot? Because you know, I’m talking about the thing where they feel like the screen where it lets you pick the dates and the, wait, where is that?

Joe Pilot, All right, I’m gonna do an emergency stop share. Like, I’m gonna start the old reliable yellow SMS. We can figure out what’s going wrong here. Old faithful copyright 2022. No, no, that’s dark mode nonsense. Joe Pilot, This newfangled stuff. Maybe it’s just hidden in there. We don’t know. Joe Pilot, Yeah, all right. Where is this?

Joe Pilot, No, but like, you know, a lot of the complaints that you have about Query Store, that’s why I started writing the SP Quickie Store procedure. It’s like, you know, it doesn’t show you the stuff you need to see. It includes dumb things like statistics updates and index stuff. And like, you know, a lot of like background queries. It even shows like, sort of embarrassingly, it even shows like, like the Query Store queries in it. So like, sometimes you open up a Query Store and you’re like, what went what was so slow? And you’re like, Oh, it was Query Store querying itself. And you’re like, Wow, I can’t do that. I can’t fix that. So like, I don’t know, like, like, you know, like, not being able to just like find plans for a store procedure or not being able to like, you know, like, like search for some query text. All these things that very much irked me about Query Store trying to use it like in front of customers over the years be like, there’s this great thing. It’s amazing. You can query history flight data recorder. Wow. And then, you know, like, they’re like, Oh, well, can you find this in there? And I’m like, No.

If it shows up shows up. I got nothing for you. I’ve definitely used yourself before I’ve written my own stuff. Like, you know, like, Have you? It is nice to have You think you’re better than me?

I mean, I didn’t open source it. So what does that tell you? I just use it privately in shame. Like some other things. No, but like, you know, like, there’s something human about wanting an AI. I’m supposed to be role playing or wanting a UI. I’m supposed to role playing as an AI. So I guess not really filling the gag here. But I’m doing terribly. Yeah, it would be great if the UI works. But to your point, if you really are one of the serious power user query store, you know, that this UI isn’t gonna do it for you.

All right. So I finally found it. I think what happened is like the zoom cutout of our faces was black. Oh, okay. The new sleek blackness of SMS, whatever version this is, was throwing off all my muscle memory. Like, you know, like, these defaults aren’t bad, I guess. But like, I don’t care what happens. Like, when everyone’s asleep. Yeah, you know, like, like, give me some kind of like business hours, like, like, show me yesterday.

Yeah, that’s the kind of thing. Yeah, I care about. Yeah, like, all right. So what was I saying? Yeah, like, anyway, yeah, like, they probably have like, 100,000 bugs that haven’t fixed, and they’re on with developers. And you know, the super higher ups, want them to work on copilot and like, nothing else. So their hands are tied. But I think that should put those AI coding agents, throw them at SSMS, throw them at that, that, that, that backlog, have them fix all the bugs, all the annoying things that we’ve been suffering through for years and years. And then we can finally say that AI did something. Yeah, you know, I mean, it’s, it’s one of those, it’s sort of like funny things with software development, where, you know, there’s always time for new endeavors, but there’s hardly ever time for the backlog. Right? Yeah. And so like, you know, like, it happens to me to, you know, sometimes I’ll look back at like old blog posts, and I’ll be like, Oh, I should really update this to like, you know, like, add in some more words that make it make more sense, or like, you know, make it more technically correct or expand on some point. But then I’m like, like, why do I want to go back and like, rewrite this blog post that probably no one’s seeing and gonna see when I could like, record a new video that does all that, and get it in front of people who will actually see it as like a thing today. And so you know, a lot of like the I should go fix this old stuff gets thrown away, because I’m like, screw it, I can I can I can make double material. Like, I can have something new based on this and have more to it. So, you know, I understand why a lot of stuff like that just doesn’t get done. But I, you know, just if for the amount of effort that has gone into query store, generally, I do find it shocking that the query store GUI like the face of query store is so hideous. It is a purely anti human interface. It does not like people it isn’t like whoever designed it, I think just a very anti social person did not does not care about human experience, maybe even enjoys human suffering to some degree.

like, like, just, there’s just like nothing to it. It’s just like human intuitive. And I get I get very frustrated, because, you know, I spend a lot of time, like, you know, making the stuff that I produced to like, hopefully be human intuitive, or at least human readable, or at least lay things out in a way where like, if you like, you know, spend a little time with it, you can figure things out. But the query store GUI is just a wretched experience. And like, the one thing I guess that it has going for it, is it there is like some visualization to it, but the visualizations that it has are so I don’t know. what’s what’s what’s what’s what’s what’s the what’s the nice word for ugly visual?

Yeah, well, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, when when a visual looks good, people people people call it accessible, but a highly accessible. visual this is when I don’t know what they call it when they’re not accessible. You’re a eight year old kid can understand it. I mean, everyone understands a bar chart bar graph. Yeah, yeah.

And it even has the nice like bright colors. Yeah, it’s designed for like, yeah, an eight year old. How? Yeah. Wait, how? How is it? Is it eight? I don’t think it’s correct. I’m the goal of 10. Okay, 10 year old could do it. Yeah. Okay. Okay.

Maybe. Yeah, and such an old blog posts. I know what you mean. I only update mine when like Brent emails out in his newsletter, but then Paul White posted comments like something I was wrong. But like everyone see how wrong I am.

And even if I updated, I’m not able to update like Brent’s emails. I have to live with that shame forever. Yeah. Yeah. You know, that’s one of the risks of blogging. Yeah. That happened back in 2014. No, 2020. Well, probably happened then too, but it happened in 2024 as well.

There’s actually some JSON thing. That’s probably good for blogging about JSON. Oh, yeah. The JSON blob thing. Yeah. Yeah. The using more and more memory. Yeah. Fun. Yeah. Yeah. And then then Paul, Paul dunked on you and then it was too late. It was just memorialized.

It was too late. Yeah. That was the perfect way to put it. Too late. The shame is permanent. I mean, like on the, on that same subject though, it was, there was a very funny thing that happened this morning on LinkedIn. Uh, where this gentleman who has SQL Server expert in his, uh, LinkedIn title, uh, posted what is clearly LLM content, uh, about a new feature in SQL Server 25 to 2025 to do table level restores, uh, which doesn’t exist.

It was, it was, uh, it was an April fool’s joke by Brent, but the April fool’s joke apparently poisoned AI. And so this person was like new SQL Server 2025 features. I’m going to go test these out. And, uh, basically got made fun of until, uh, he deleted the post.

So if you’re trying to motivate me in the blogging, I think this is pretty effective. You’re saying that I can like poison AI. You can poison the AI. That’s, that’s definitely, uh, that’s, that’s changing the balance a little bit.

Yeah. Right. Don’t want to blow. I mean, maybe there’s an AI watching this video right now. You know what? There very well could be, we should just say a bunch of wrong stuff. I mean, that probably already happened, but, uh, yeah, we’re going to, there you go.

Yeah. Now everything we say wrong is on purpose to trick the AI. Yeah. That’s, that’s what we can go with. Yeah. We can call it the, the, yeah. So yeah. I mean, just LLM poisoning is I think the wave of the future.

That’s going to be my new gig. I mean, you know, how it is as an end user of SQL Server, you’ve been disappointed so many times, or even just software, right?

Yeah. Like being a Mets fan. You have like a bug or a suggestion and they’re like, yeah, well, if it’s SQL Server, it’s not going to respond.

Yeah. Like, you know, if you think back to the old, like glorious kind of connect days back when the site worked, it’s like, yeah, like this is a problem, but like, well, you know, like we’re not able to prioritize this or we don’t have developer bandwidth or whatever, you know, kind of saying, yeah, whatever, you know, but now with like these, these supposed AI agent coders or whatever, like they, they don’t need time off.

They, they don’t take holidays. You can spin up as many of them as you want. And I’m looking forward to a future where, you know, you, you, I complained about a bug or ask for a new future and you get an automated response that says, you know, we’re sorry, but all the AI agents are busy working on hard-party work and they, I just can’t get to your suggestion.

Your suggestion has been facing the queue. That’s what I think the future looks like. Yeah.

Most likely. Yeah. I mean, it’s, it’s, it’s, it’s, it’s, it’s incredibly cute to me because, you know, when, when you talk to people who build like real software, like SQL Server, I’m going to say real software, you know, and, and sort of like in the sense that a lot of people use it and it has the word enterprise in it.

So like, you know, they’re building real software, but you don’t like, when you talk to them about like real problems with it that need fixing, the conversation always comes back to like, like a developer time or sort of like developer budget, right?

Like you have so much money that you can spend on a feature, which is really like developer time on a feature. And like, and you’re always like, well, how come AI isn’t doing that? Like, where’s the co-pilot for that?

Like, is, I’m sorry, is this thing too important for AI? Like, is this thing too complex for AI? Maybe, maybe, maybe we’re not at that point yet. Maybe we shouldn’t have fired like 9,000 people who work on this like important things in favor to, because the AI is coming.

Maybe, maybe we could have kept some of them around to like, you know, sweep, sweep up the pieces while we wait for all this stuff to blow over. I don’t know. It’s, it’s, it’s, it’s amusing to me when you, when you get to that point. Well, I think what my hope is, and, and, and, and AI watches this video and AI on map recreates a bug report for SMS for the thing that doesn’t work and hasn’t worked for like eight years.

Yeah. And then, and, and AI fixes the query, which would be very easy to fix. I think I even fixed it in the blog posts. In fact, maybe AI could, could find my blog posts and I could get the fix from there and I could plug the fix in and then it’ll finally be fixed.

That is what I’m hoping for. I don’t know. It might, it might be that my, my SEO is so bad that not even AI can find me. Oh man. It might be, it might be that terrible.

Actually the, the new funny thing is like I used to get a, like, like, as soon as I signed up for LinkedIn, I, I, the first thing I noticed was that, excuse me. First thing I noticed is that I started getting a crap load of spam.

And so like, you know, I, I have a very well curated junk folder now, but every once in a while I’ll go like looking at it and the tide really has turned from like, like, let me fix your website SEO or let me fix this other SEO thing for you to let me fix your like chat GPT SEO.

Like you want chat GPT to mention you when people search for SQL Server performance. And I’m like, it does already. Like, like, like whenever I have like an LLM do like deep SQL Server research for me, nine times out of 10, there’s a, there’s a link from to my site in there.

And I’m like, you bastards. The best SQL AI review, the best SQL Server installs include individual actions, like, you know, like, I, I, I, I, I’m not a well traveled person.

Like, yeah, yeah. Excuse me. Um, I don’t know.

We have Greg Galloway. We have someone whose name I refuse to say for reasons I also refuse to say. Um, I, I, I don’t see you here.

Yeah. Sorry to, to break the news to you. Crap. Well, maybe, maybe, maybe, hold on, hold on.

It says AI responses may include mistakes. So obviously it was a mistake that your name wasn’t included. Well, you know, maybe, maybe, maybe if you asked for, uh, the best SQL Server performance consultant in the world outside of New Zealand, it would, it would show up.

But maybe. Yeah, maybe we can, we can hope. Right.

You know, if you take advantage of that, uh, the AI SEO, maybe by the time someone watches this video, the results will be different. Maybe, maybe, maybe, maybe that’d be nice. Maybe you don’t pay for, pay enough for chat GPT for them to give you a real in-depth answer.

You probably, you’re probably using like, Oh, I’m using some like free Google or whatever. Oh, okay. Yeah.

Well, I’ll, I’ll, I’m, I’m doing my best not to be offended by this. Okay. I’m crying inside. Hey, if you want to end the video, we can. No, it’s all right. Oh, I’m just gonna, I’m just gonna pinch my inner thigh real hard. So the emotion sort of tapers off.

Well, for the record, I think the AI is wrong. It is yet another example of untrustworthy. Yeah.

You know, there’s a lot of that. Uh, I don’t, I don’t know. Have you, have you been able to use AI for anything meaningful? And like your. No. Yeah. I mean, like I’m kind of an AI. Uh, I guess like, like I, I, I can’t really get over the, Oh, well, you know, here’s a lot of here’s some, here’s an answer to have, like might be wrong.

Like. It makes you think you can only use it for like. Things where it’s okay to be wrong or things that are easy to verify if they’re correct. Like, I don’t know.

Like if you asked for like a pound drone, that’s like 15 characters long, I guess, yeah, for that. And then it like, like spits out a word easily verify if it’s correct. Right.

Maybe like that’s a good use. Or if you’re doing some like proof of concept code that will never reach production though, as we all know, if the code works, you can. Ship it.

Say what you want about never reaching production and temptation will rear its ugly green head. And now you’ve got AI code in production. Yeah.

I think. I’m definitely the wrong person to ask, but I have, I have a very negative outlook on AI personally. Yeah. Uh, you know, the, the, a lot of the things that I’ve tried to use it for were things that theoretically it should be good for, but the, it just gives you such junk back.

Like, uh, you know, like I, I, I’ve, I’ve tried to use it to like, um, outline in detail training content, but it’s just such a dead end for that because like the, the topics that I would want to cover are not just like, you know, uh, the like DBA one-on-one stuff that you, that it’s capable of, um, you know, generating, uh, a lot of problems with things being factually and technically incorrect.

Uh, whenever I try to like, you know, get into it a little bit there and, uh, yeah, it’s just, you know, I’ve, I’ve, I’ve been very disappointed. By, by the, the things that it’s produced. I know that AI is very good at maybe that’s, I, I don’t know, maybe it’s sort of like the, like the coin toss thing where it’s very good at showing me what I don’t want.

So I can at least like, like, oh no, I need to do the opposite of this. This is fine. Right. Uh, but yeah, I’ve, I’ve, I’ve not, I’ve not had a lot of, uh, success getting it to, um, to produce things that are, um, up to, up to the par that I would like them to be.

I actually did have an interaction recently. Uh, you know how if you enable service broker, it has to make a super annoying lock, which is like very difficult to get.

Yeah. I guess. I don’t know. It’s like, uh, it’s like an exclusive database lock or something where like nothing else can be connecting. Yeah. It’s like turning on RCSI. Yeah.

Yeah. Yeah. Um, so I wanted to turn off service broker and I, I wondered, I wonder, I wonder if it needs the same super annoying lock. I suspected it did. I, uh, Google that Google’s AI summary shows up at the top and it says, no, you know, you can like while enabling service broker needs a lot, you can say, well, without taking lock.

And I thought that doesn’t sound right. I’m going to try it. And do you want to guess if it takes a super annoying lock?

I’m going to guess it did take a super annoying lock. It did take a super annoying lock. Well, I mean, you know, the power of AI at our service, you know, uh, you know, typically with SQL Server, uh, you expect the worst.

You don’t hope for anything. There’s no hope for the best. Expect the worst. You just expect the worst possible outcome. And then sometimes you’re pleasantly surprised when it does not reach that critical mass.

Like other. I think it’s better than these videos where our viewers can and should expect the best. Correct.

Right. Especially from your camera. Yes. Yeah. Yes. All right. I’m, I’m, I’m a, I’m a, I’m a, a stop recording. Okay. All right. Well, since Joe is ready to stop recording, Joe is tapping out on me. Uh, we’re going to wrap this one up.

Uh, I guess we got off the subject of SQL Server tooling a little bit, but, uh, I think we covered enough of that to at least live up to the name of the, the, the title of the episode.

So we’re going to get going here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Once again, thank you to our fabulous sponsor, darling data and a beer gut magazine. And, uh, we will see you in the next thrilling episode where, uh, Joe will show us more of his screen.

I hope. 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.

bit Obscene: Ordered Column Store

---

Video Summary

In this video, I delve into the intricacies of ordered columnstore indexes in SQL Server 2022 and 2025, exploring their benefits and limitations. I argue that while these new features offer some advantages, particularly for large ETL loads, partitioning tables by date or another logical key remains a more robust solution for most use cases. To illustrate my points, I walk through a demo using the latest SSMS preview (version 22), showcasing how turning on trace flag 58008 can prevent the creation of unpartitioned columnstore indexes in large tables, ultimately guiding users towards more sensible design practices.

Full Transcript

All right. Welcome, everyone, back to the unofficial maybe relaunch of the Bit Obscene podcast brought to you by Darling Data and BeerGut Magazine. I’ve got my temporary co-host, Joe Obish here. We’ll see how he does this time around. And today, Joe is really excited. You can tell by the look in his face. Look at that face. That’s an excited man. Joe is excited to talk about ordered columnstore indexes, which, correct me if I’m wrong, Joe, that, that, that, that, I’m sorry, I have to get rid of his background. It’s making me look like an insane person. It came up with SQL Server 2022. It’s made maybe some, some progress in SQL Server 2025 that, but no one knows, because SQL Server 2025 is, of course, the unofficial AI and fabric release. SQL Server is billed third on that one.

So maybe there’s nothing good for SQL Server in it. But anyway, take it away. Mr. Robish. First of all, Oh, shoot. Here we go. I learned that my dear friend, Eric is now living under communist rule. And I thought, what’s a small token gesture that I can do to appear like, like I care, and I want to help but not really do much. And I thought, Oh, I can just show up as a guest on his YouTube channel. So that’s why I’m here. I’m, I’m, I’m, I’m, I’m, I’m, I’m in full solidarity with Eric. I’m wearing the plain white t shirt.

He’ll probably be wearing one in a couple of months along with everybody else in New York City. But I, I, I, I’m not here to help Eric. We’re, we’re, we’re all pulling for you. All right. Thanks, Ben. Hopefully, hopefully, my, my means of production are seized in a gentle way.

So for, for order to come store, Eric and I actually both blogged about this a few days of party then back in 2022. And the way I remember it was, that’s right, no clues. The way I remember it was Brent blogs about it. And he was like, Hey, I tried this and it didn’t work. And, you know, Eric and I live to serve. So we, we both took our own little look at it.

But, um, I think Eric can have the links in the description. Oh, yeah. And the viewers can vote on who, uh, did it better. You know, we can get some engagement, maybe. Of course, there’s no, there’s no voting in communism, Joe. So that’s, that’s, that’s, that’s very, that’s a good point. I take it.

Oh, back. Uh, now I’ll admit that I haven’t looked at since. I remember ending the blog post like, Oh, this is CTP 2.0 or whatever. Maybe Microsoft will make it better. Wink, wink. Now they, they like never do. Um, but I haven’t looked at it. I’m assuming it’s not better. If, if they did make it better, feel free to let us know it in the comments.

Now, in terms of what, okay. So why does ordered columnstore exist? So there’s a very old workaround for columnstore where you create a clustered index on the row you want to order by first, and then you create a columnstore index with max. That one second, the idea there being, I was like an, it’s like an unofficial way to get like a, an ordered columnstore after you built it.

That’s where you create the clustered index on the table on some column or key columns. And then you do the clustered columnstore index with drop existing. Yeah. Yeah. Yeah. Okay. And like, you know, like that could be improved. You’re writing the data twice. It’s a bit of a annoying workaround. So I guess like from my point of view, you know, getting rid of that workaround could be a good thing. Um, because now you can just create a, just create a clustered columnstore next with ordering.

Problem is, and this is the thing that Brent blogged about. It’s not always ordered, which, which is not too good start. Um, but like, I’m even gonna set that aside. So I found that when you add ordering, SQL Server is making three changes. First being when you create the index, it’ll add a sort operator. It’s a soft sort. Um, there are all kinds of reasons why the data isn’t going to be fully sorted.

When you’re doing an insert in the table, it’ll again, add, add, add a, add a sort. And then once again, the data might not actually be sorted or it’ll be sorted in an intuitive way. And then when you’re doing a rebuild, it once again, adds a sort. And we’re a three for three and that sort may also not fully sort the data. So that’s what the feature does. Um, my big beef with it is how it changes the, the insert queries, but you had a comment here. Oh, I was gonna say we’re now at three sorts and actually no sorting. Yeah, yeah. So.

All right, I’m gonna ask you a non trick question. You ready? I know what that means. Why are clustered indexes generally useful?

Why are clustered indexes generally useful? Uh, well, uh, there, there are lots of reasons why they’re generally useful. Looking for the simplest, the simplest answer.

Well, it, it gives you a free access to all of the columns in the table in an ordered fashion. Yes. Yes. It’s because the data is sorted, right? The data is sorted globally. If you insert some old data, according to your key, it’ll do the, you know, page splitting and it’ll, the data is always sorted.

Now, what if I told you that we created a new type of index and it was kind of sorted. Sort of sorted. And whenever you inserted new data, it would sort the new data you inserted and append at the end of the table.

And it would, it would, it would keep doing that. So whenever you do an insert, it’ll locally sort that data and append it at the end. Does that sound very useful to you?

Let me, let me just make sure I have the right mental model of this. Let’s say you have a table with a million rows in it and it’s the numbers one through 1 million and they’re in order. And then you insert 10,000 rows to it.

Instead of those 10,000 row, let’s say they’re, they’re also the numbers one through 10,000. So they fit in with the numbers one through 1 million. So instead of the numbers being dispersed in the table where they should be, it would just be like an appendage on the table with the rows in order from one to 10,000.

So it would be like one to 1 million and then one to 10,000. Yes. Yes, that’s correct.

And that’s, that’s the fundamental problem. From my point of view with order to columnstore, like I find that kind of worse than though we’re going to not sort data always for you. Other people could disagree because you know, like it’s, if you have clustered indexes, you have your, your, your AK pages.

They can be split all kinds of great things can happen. You have columnstore. You have your compressed row groups.

Like there isn’t any way for, there isn’t any reasonable way. Like if you’re loading a million new rows, like it’s, it’s not going to find the most appropriate compressed row group uncompress it, add the new data and then recompress it. That that would take forever.

Like it’s just, it’s just not going to do that. Like it’s going to append the data at the end. I’m surprised though that on rebuild, it doesn’t like fix that. It could fix it.

It could. If, if, if your sort manages to work fully. Yeah. Okay. Great. Good. Good. So what, what makes us sort work fully or not? Uh, here’s a number of things, how much memory you have, how much data you have, what DOP you’re at.

Um, which brings me to something, which, man, I, I, I have these great notes that I’m like not reading. So, you know, like one of the, uh, like one of the annoying things about the old workaround to get like pseudo ordered column stores before 2022, you know, the cluster index and CCIs. You have to create the CCI at max top one, which, which could be slow for a big table.

Free the, free the documentation for ordered columnstore. It says, if you create the index in parallel, it’s not going to be ordered. And in fact, if you want perfect ordering, it.

Max top one. Yeah. Max top one. So like, so are you better off just doing the old message, the old method rather? The, the, the problem.

I view the sorts during insert as not that useful or, or even harmful in some cases. And you, you get this partial to full store when creating the index, but like the penalty for that is you, you lose online rebuilds. Hmm.

At least on 2022 and 2025, they’re adding online rebuilds or the columnstore. Okay. So if you asked me if it was worth it, I would say you should just partition your tables, but I’m going to try to make my case a bit better. Um, on this subject of insert.

So let’s say you, you have an ordered comp store index. Cause. You’re in 2022. Yeah. Use all the new features as soon as they come out. Read the documentation.

You’re hot on the heels. Why not? Yeah. Yeah. Why not? Now, if you’re inserting less than a million rows per partition, what’s going to happen? Well, it’s going to sort the data pointlessly. Yeah.

And that’s going to try to compress the new single row group. If you get some kind of, uh, what’s it called? Uh, pressure, dictionary pressure, memory pressure, whatever, you can get multiple row groups. But like in general, like that, that, that sort’s not useful.

Like if it’s, it’s, it’s, it’s, it’s almost like sorting, like a single, like a single 8K page before you insert the data, which maybe it happens, but you know, like, you know, like, like, it’s, it’s not doing anything for you. Now you want me to say, all right, well, what if I insert more than a million rows? What if I insert 10 million rows of time?

Well, It’s an ambitious insert. First of all, if you, if you, if you have like, you know, like, like a normal ETL load where you’re like, like, like a normal ETL load. Like yesterday’s data, it’s going to be like new data probably.

Right. So the thing you want to order by is probably for like yesterday’s day. So it’s, it’s not gonna matter in that case, or like, let’s suppose you’re loading a week of data at a time. Cause you’re, you know, you, you, you, you live under communism.

You’re not going to work every day of the week. Right. Right. So in that case, you, you, you have a week of data, 10 million rows. So it’s, if it’s ordered, then your row groups have like one to three days of data instead of seven.

And if you happen to run a select query, which is like, you know, looking at a single day in that range, then maybe you read a couple of fewer row groups. So it’s, it’s not, it’s not, yeah, maybe like, that’s not really giving you a whole lot in terms of performance. No, but I think, I think that gets, I think that gets back to your point though, about if, if this is something that you care deeply about, regardless of ordered columnstore.

Like, but if your table is truly that large and you’re truly, truly doing that sort of ETL with like what you’re talking about where there’s daily loads. And I think that’s, truly the most sensible thing to do is have that table partitioned by some date that, that, that gets you like, I mean, maybe, maybe not like from a data loading perspective, but from like a, like querying that data later perspective. That at least gets you the partition elimination and then segment elimination based on, you know, the criteria for your query, looking for data for some date range.

I agree fully. And I’m going to jump into my demo ahead of time. Ooh, my, my, uh, four notes.

They’re just, they’re just basically just, you know, the, the, a cast of Erik Darling is just down the memory hole. Yeah. All right.

Can you see the beautiful modern SSMS? And I can’t, is that 21 or 22? It’s the newest one. That’s SSMS 22 preview. It’s glorious.

It’s glorious. Look at it. Oh, it’s not 21. 21.6.1. Wow. I’m already out of date. I guess. Let’s do that. Check for an update too.

All right. All right. Um, I, I, I, I, I, I, I, I hear this stuff. All right. So what’s that trace flag? It looks, it looks, it’s that trace flag, Joe. Microsoft has heard my, my, my desperate please.

Yep. On the subject of partitioning is super important for columnstore. So what was, so why do I say that?

It’s the same reason you said it. If you, if you, if you partition logically, then that’s going to force all the word groups together. It, it, it basically gives you like, like, like, like a minimum sort of segment elimination free.

Like no matter how badly the developers load the data. If it’s a row at a time, it’s a hundred thousand rows at a time. If, if, if they don’t load the data correctly the first time and they delete it and update over and over again, they just still in that partition.

Even if you don’t get a partition elimination, you can still get segmentation. I mean, I’ll go further and say that I’ve never had a columns or forms issue get resolved by, well, you know, we, we had bad segment elimination because the data wasn’t ordered.

So we were reading like, we were reading like 20 row groups and then we made the segment elimination perfect. And now we’re reading three and performance is like amazing.

Cause we, we, we got rid of those 17 compressor groups. Like I I’ve never seen that. The things I’ve seen are you have a big table. It’s not partitioned at all.

Data is basically in a random order. You know, you can get hurt there. Um, or you have like a billion soft deleted rows. I never got cleaned up.

I’ve seen them production, you know, that’ll hurt you. Or even just like, like all of the normal performance issues that you can find with really any query.

Like for me, it’s, it has never been like I’m partitioning my data by quarter or whatever, but well, my data is too unordered within the three month window. And like, I really need that perfect segment elimination.

Like I’ve never seen that. Maybe there’s somebody with high frequency trading or online gambling, or who was like super hardcore. And that they really need those perfect pristine row groups.

In which case I would think that, you know, a sort that might not start isn’t good enough. Definitely not. And you, you gotta fix your UTL anyway.

Anyway, I’m a big fan of partitioning and Microsoft has heard my fleet, my please. They gave me permission to introduce this new trace leg 58008. We go ahead and turn it on.

All right, it’s turned on. And now I’m going to try to create a columnstore index, a big table, which I’m creating an, an, an unpartitioned index.

I’m trying to, that’s, that’s, that, that, oh, look at that. This is not safe for children. Look at that. The operation failed because the table is too big. Like, create a partitioned commister index instead.

Finally, SQL Server is finally stopping us from, from making dumb mistakes and bad decisions. Isn’t this great? This is fantastic.

Now, you know, I’m sure, you know, like, now, like, you know, maybe your DPA thinks they always, they always know better or, you know, like, it’s, it’s important to be able to, to, you know, have full control of the software.

So we had this new create bad index mode, uh, option. And if we, if we set it to on, then, uh, we can, uh, avoid Microsoft’s guardrails. So I, I’m really excited about this new feature.

We can finally, you know, encourage people to partition their damn tables, which I think is really, it’s probably like the, I’d say it’s the most important thing if you’re doing columnstore at any like reasonable size, you got those deals partitioned.

Great performance, ETL, maintenance. There’s just so many reasons to do it. And, uh, I’ve seen what happens when you, when you don’t do it and it is not, it is not pretty.

Yeah. I mean, uh, you know, I don’t, I don’t do a ton of production work with, uh, with column store. I do a bit, but, um, you know, most of the, most of the tables that I run into that are, are, that are column stored are well beyond like, you know, my ability to, um, have my consultant way with them and like, maybe go back and partition them or, you know, do anything useful in that way.

Um, you know, I wish, I wish more people would involve me in like the implementation of something rather than like the, Hey, all of a sudden this sucks. I think I’m almost like, well, guess what? At 3 billion rows, we’re a little past the point where we can easily do this. So, um, you know, not, not undoable, but certainly, uh, what makes, makes the experience a little bit more painful.

But, um, you know, a lot of what I run into is, um, people who have, uh, you know, like row store partition tables, cause they bought into the, the meme that, uh, you know, partitioning tables makes queries faster with Rome, with row stores indexes.

And boy, are they surprised when they start doing things like men queries and max queries, and even some, like, you know, some other, some other types of queries, all of a sudden they, they, they can’t just like, like start at one end of an index and get something like they, they, all of a sudden they have to scan every single like index and like the, like all of the partitions.

And you’re like, again, like, like, why didn’t you call me like, you know, five years ago when you first got this bad idea in your head. So for the, for the folks out there who have perhaps large columnstore tables implemented, but did not have, uh, the foresight and certainly, uh, at this point may or may not have the high hindsight to, uh, to partition their, their large clustered columnstore tables.

Uh, is there any hope? Is there anything they can do to make their lives better or easier? Or do you just recommend creating the partition columnstore table and loading data over and doing the old SP rename switcheroo?

I don’t remember how I did cause it was so long ago, but we had that same problem where we had like, you know, we had 300 customers at the data warehouse, nothing was partitioned. Some tables had billions or tens of billions of rows.

And we did ship the code to make that happen. Um, how did we do it? I don’t remember how we did it.

Uh, I, I, I think we just did the usual scam of, oh, you know, you’re now in a maintenance window and we’re going to rewrite all of your data. Um, I think we had like a lot of parallel jobs doing the inserts.

Sure. There is, there is some trickery there. It, it, it definitely wasn’t easy, but it’s, you know, if you want to make the best long-term decision, you gotta have partitioning.

It’s only going to get worse the longer it goes on without partitioning. Um, in terms of this new feature or columnstore, I mean, maybe it’s better than nothing. You know, there probably are some use cases where it’s okay or bar than status quo.

I do think it’s kind of fundamentally flawed and that, you know, as we talked about your word groups already compressed, you’re not going to uncompress them to get perfect segment elimination.

And that’s just like an unsolvable problem pretty much like, you know, the, the, uh, new data you load at best. It’s going to sort that, but it, it, uh, might not even do it. You know, you might get a partial sort, or if, if, if, if you’re doing parallel insert, it’s just, it’s just not going to work in the way you’re expecting.

That’s covered in the, in the blog post I did. Yep. So I’m looking, I’m looking at the 2025 notes here. And, uh, apparently Microsoft has really expanded its repertoire of, of 75% complete features by adding ordered non-clustered columnstore indexes.

So we’re very excited for that. Aren’t we? Yeah. Yeah. That’s a big, that’s, that’s, that’s going to change, change the data landscape forever.

I was actually trying to fathom like why order to columnstore even exists. Uh, I do remember hearing a rumor about the, the, uh, SQL variant data type where like some big, important customer gave Microsoft a really big bag of money and then Microsoft implemented the SQL variant data type just for them.

And all of us could depend on it. So like maybe it was that, uh, maybe there was some customer who was like, Hey, you know, we have, we got this big problem.

We really need to order to come store. And they managed to convince them with, without the big bag of money. Yeah. Maybe I have a third theory though, without, without any factual basis, what’s the thing whatsoever, but I feel like it’s my favorite kind of theories.

So I’m just going to throw it out there. Like they’re like, really there, there, there are like two things that, uh, DBAs love. Right.

First one is a good clean demo. Second one is attempting to remove fragmentation. And the problem with calm store is it offers you both. Cause like, you know, like, like what’s the easiest possible demo to do with calm store?

Oh, I load my data. Like, uh, like a total dumb ass. And now I’m not getting any segment of elimination. I can, I can, I can view the little print screen and it says segment with eliminate zero segments red 99 or whatever.

Yeah. Then I heroically load my data the right way. And now it’s ordered.

And now, you know, the, the, uh, little numbers and the segments, you know, I’m, I’m skipping segments, doing less work. Like, you know, like I’ve done that demo. You’ve done that demo.

Like everyone’s on that demo. It’s really easy to do that demo. Yeah. You see all these blog posts talking about how great and easy and important. The elimination is nobody talks about partitioning. So, so like, you know, like, like, like the, the important thing, do the ease of demoing it, become a single elimination.

And then there’s just anything, right? Who doesn’t like anger or fragmentation? How many people are still rebuilding all their indexes every night?

Too many, too many, too many Joe. You gotta get rid of fragmentation. Right. So I feel like there’s this way too much importance on that subject. Um, I mean, I, I already said, practically speaking, what I think people should do.

I think partitioning is, is way more important and, you know, like it’s, it’s, it’s even worse. Cause like, I don’t know, like I I’ve seen playing questions like what comes or indexes aren’t ordered. This is totally useless.

Microsoft needs that ordering. Like I’ve seen that come up a lot. And, you know, like, like maybe finally Microsoft listened to the community when they shouldn’t have. And now we had this super half baked feature that can like never truly work, which source the data sometimes and removes online index rebuilds and ads point in the sorts.

And, and so on. Um, you know, it, it, pointless sorts are like, I don’t know. I think a good chunk of the SQL Server source code.

Um, so in, in, in terms of what I want, I already showed what I want the, uh, magic new trace lag. So that’s, that’s great. Look forward to that.

Um, I kind of feel like maybe like, uh, you know how there, there, there’s the sword and, uh, temp TV option, which only applies during your next creates or rebuilds. Yep. I think that would have been better.

Like, Hey, you know, you, you’re creating columns or index, like say which court, which column you want to order by, it’ll make a. Attempt to do it. Mm-hmm . And it’s not going to fundamentally change every insert and it’s not going to remove online rebuilds because it’s just something that happens like once during creation. Mm-hmm .

And, you know, if you’re the kind of guy who’s like rebuilding your indexes every night, well, it’s going to be offline now. Like maybe that works for you. Maybe it doesn’t. I don’t know, but.

At maxed up one. Right. Yeah. Like, I don’t know. I, I don’t think. I don’t think this is a very good feature. And if, if you read the documentation, like there isn’t anything even about inserts, you know, it’s just all like, Oh, we’ll, we’ll sort the data and, or maybe we won’t, but even partially sorted data will improve query start performance.

Like they make it sound like a no brainer and. I, I, I, I just think it’s, it’s, it’s, it’s fairly flawed, you know, do the whole. Once again, we’re not going to uncompress row groups and, you know, slice and dice and surgically insert things.

No, I, I honestly like this should be, this should be one of those things where like when you use it. Uh, like. So there, there are other database systems where like you can.

Uh, like create a table and partition it all in one go. Right. Like, like SQL Server, you have to like create the partition function and partition scheme other databases.

You can be like create table with like, and like has like this, like with partition as syntax type thing, which is like, like when you write a window function and you’re like partition by blah, blah. Like you can write the table is like partitioning by that. And you can specify like the column and like the partitioning like ranges for it.

Uh, I think that. With ordered columnstore. Uh, like if it’s not on a partition table, like it should force you to choose a, like, like whatever columns you’re ordering. By it should force partitioning on those columns.

Like, I don’t care. I don’t, I don’t care if it like, like, I don’t care if it makes a stupid choice doing that, not doing that as a stupider choice than any choice you could make in that. Like just.

Bucket things somehow. Like just like why. That’s interesting. I mean, I don’t, yeah, you know, it’s. I think they’d be scared to try to pick partitions for you because you know, of course they would, but yeah.

But yeah, no, it’s, it’s definitely a good point. Possibly even superior to my, you know, you can be able to trace that to tell you when you’re being a idiot or not. So how did, how does that trace flag decide what a too big table is?

That’s, that’s, that’s a, under NDA. So, okay. It’s proprietary. All right. Gotcha. Gotcha.

Well, maybe, hopefully someday, uh, you’ll, you’ll be able to write anonymously on, on some blog about the thresholds for, uh, when a column is too big. Well, I mean, so, so, so the great thing is if I violate NDA on your blog, everyone should think that you wrote it. So I want to get in trouble.

That’s true. I won’t get in trouble. I won’t get in trouble. I don’t have an NDA. What NDA do I have? Oh, okay. All right. Can’t sue me if there’s no paperwork. Okay. All right. That’s, that’s, that’s, that’s the defense you’re going with, huh? Yeah.

Okay. I was drunk when I wrote it. I was just guessing. Uh, I didn’t sign any paperwork unless I was drunk when I signed the paperwork. So make your own decisions there. Well, I mean, I understand why the NDA is certainly annoying.

I remember I was once told by, uh, uh, Dr. Now that, you know, here’s some information that’s under NDA, but I already knew it. Yeah.

But you’re gonna tell me something I already know and now it’s under NDA and I can’t tell the people. No, like I already, I already had that information. I already knew it. That’s why you have to, that’s why you have to blog about everything.

Yeah. Believe it or not, we, oh, let’s see what you did there. Yeah. Believe it or not, we can figure out things on our own sometimes. So what, so the real trick of what to do is to write the blog post, but put this, the posting date is like a month before anyone said anything to you and then publish it and then be like, well, when did you tell me that? Oh, such and such a date.

Well, this post was published a month before that. It’s not covered by NDA. I bet everyone who’s watching this is learning a lot, getting a lot of good tips. Look, there’s one thing I’m good for.

It’s sound legal advice. So that was, that was all my complaints about, or account store. Um, I don’t think it’s very good. I think you should partition your tables and that’s going to do way more for you.

So, but I mean, you know, maybe there’s some workload out there where this is the best thing since that communism and they’ll get a lot of value out of it. Well, I think the only thing that we can say about this feature that it truly is for the people, it’s free kind of for $7,000 a core. It’s free.

Maybe not. All right. Yeah, I’m gonna, I’m gonna have to workshop that one. But, uh, I don’t, I don’t have any specific complaints about ordered columnstore. Uh, you know, my, my complaints are far more generalized than that, like, you know, like, okay, you’re gonna spend development time and cycles on another feature that’s like, you know, 60, 70% done.

And like, try to get us excited about it. And then, you know, they trot poor Bob Ward out to another conference and everyone’s like, Oh, like, what are the internals like get the debugger Bob. And then poor Bob has to sit there and be like, there’s no, there’s no debugger for it.

All right. Sorry. Like, you know, it’s just like one of those, one of those things. It’s like, we’re supposed to get excited about like this, this, this thing that, that, that is like not solving a problem for 99% of the SQL SQL Server. So I think the analogy you wanted before was, uh, you know, think about a, a, a parallel rebuild of an order to come for index.

You have all these individual threads. They’re all working together, but by the end of it, they haven’t accomplished anything. Just like communism.

Man, you’re good. You’re going to get me like flagged in the EU with this one. I’m, I’m going to, I’m going to get, I’m not going to be a big fan of the EU. You’re going to be allowed to travel internationally when, when this, when this goes live.

No, it’s, it’s the, you should have it go live after you’ve, uh, fled the earth. Uh, well, maybe, maybe Elon will take me to Mars. I can open up a saloon somewhere in a gully or that’d be nice.

Probably not have a lot of demand for SQL Server consultants on Mars. But there will be for saloons. Cause it’s me, the new wild west, nice place.

You can, you know, tie up your robot horse, buggy thing, go in for a nice cold beer, Mars, Mars beer, and take a load off. You know, we would be like just the ultimate. We’ll have one of those pianos that plays itself.

Be great. Got a whole vision here. Okay. No, my original, my original plan to open a bar on the beach in Bora Bora. I think that market is saturated at this point.

So I’m going for a saloon on Mars. I wish you well, you know, I don’t drink as you know, it’s true. So I’m not going to support you.

That’s why you’re going to be the perfect bartender. I can trust you not to drink my whole supply of Mars beer. Unless you know, Mars beer is just so delicious.

You change your mind or you get bored on Mars. Then again, you’re not, you’re not bored in Wisconsin. So you can, I don’t know. I don’t know where you could get bored. Sorry.

Did I give away too much personal? Oh yeah. I actually thought you stopped recording a long time ago, but apparently you’re still going. Oh no. Do you want me to stop recording? Yeah. Oh, all right. Cool.

All right. Well, thank you everyone for tuning in to the mildly resuscitated bit obscene podcast with my temporary cohost, Joe Obish. As always, this is brought to you by Darling Data and the kind folks at Beer Gut Magazine who supply me with stickers and magazine covers.

So thank you. And I’m going to hit the button now. All right. But I don’t know the beauty of the light in the internet. Now, I mean, we can do continues. Coming.

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.

SQL Server Performance Office Hours Episode 42

---

Questions:

•  My developers overuse table-valued parameters in application code. Are there any server/database settings I can change to make the queries using them run faster? I know about in-memory tempdb and memory-optimized table variables, but I don’t have latch contention issues so I doubt that they would help.
• why aren’t developers good at database performance yet?
• Why can’t you make an ErikAI to answer these questions?
• Do you have any training content specifies performance tuning efforts and limitations for SQL Server Standard Edition? I get excited to see posts from you and the community around Batch Mode in Rowstore only to get let down by licensing limitations yet again.
• Are you doing a Black Friday sale? (This was a blatant lie.)

To ask your questions, head over here.

Video Summary

In this video, I delve into answering five community-submitted questions during an office hours session, providing insights and advice on various database-related topics. We cover issues like table-valued parameters in application code, performance tuning efforts for SQL Server Standard Edition, and the limitations of licensing. I also touch on upcoming pre-conference events at Data Tune Nashville and Data Saturday Chicago, encouraging viewers to purchase tickets early. Throughout the session, I emphasize the importance of regular practice in database performance tuning and share my thoughts on why developers might not excel in this area due to time constraints and project deadlines.

Full Transcript

Erik Darling here with Darling Data. It is Monday in YouTube. Actually, it is Monday in regular land, too. Which Monday it is, you will never know. It’s between me and my Monday. Anyway, we are doing office hours in which I answer five community submitted questions and hope that I can give good answers at a reasonable rate. They’re free. Shut up. If you want to ask your own question, you can head down to the video description where these helpful yellow fingies are pointing. And there’s a link there where you can go and submit your questions to me. If you would like to participate in some sort of monetary exchange, there are ways to do that as well. You can hire me for consulting, buy my training, sponsor this channel, all sorts of other stuff. And of course, if you enjoy this content, please do like, subscribe, and tell a friend. Because that’s how the good word spreads, my friends. Anyway, coming up. Of course, by the time you see this video, Past Data Summit will actually be occurring. It’ll be actually Monday. I’ll be teaching a pre-con. Isn’t that wild?

So I should probably start promoting other stuff that has recently been acceptified. I will be doing a pre-con at Data Tune in Nashville. That event is taking place March 6th and 7th. And I will also be doing a pre-con the very next weekend. Data Saturday, Chicago, March 13th and 14th.

So buy those tickets now. So I can gloat about people buying those tickets now or something. I don’t know. Well, it would just be nice of you to do. Just buy the tickets. It’s great. Anyway, let’s get on with this whole database party here.

I need to go to the Excel file that has the questions in it. And let’s make sure there are, let’s see, one, two, three, four, five questions. And let’s go answer these.

Did it, did it. What do we got here? My developers. Oh, your developers. Oh, all right. See the way it is? My developers.

Let me tell you this. If they’re your developers, tell them to stop using table-valued parameters if they’re such a problem. All right. Not to spoil the question too much here. But my developers overuse table-valued parameters in application code.

Are there any server or database settings I can change to make the queries using them run faster? I know about in-memory TempDB and memory-optimized table variables. Oh, boy.

But I don’t have latch contention issues, so I doubt that they would help. Boy, are you, boy, are you right there. The problem with this question, though, is you haven’t told me what’s slow about them. What’s, what’s going wrong?

Some details would be nice. There are not really any server or database level settings that, you know, wouldn’t already be turned on if you’re in a position to have them that wouldn’t be helping you out. So I don’t really know what to tell you here.

Tell me what’s slow about them. Better yet, click on, click on that consulting link. And then we can do a fully detailed investigation into these table-valued parameters and figure something out for you.

I don’t know. This level of vagueness just can’t be, can’t be overcome. Why aren’t developers good at database performance yet?

Well, because they don’t practice it. They develop features. They need to get something working quickly because they’re under, you know, whatever, you know, time constraints and deadlines they have to deal with. And they have to make something work.

It’s the same reason why developers aren’t typically good at database security or security in general. It’s why, like, every time you read, oh, we found a database on the internet with no password. Surprise!

You know why? Because someone just had to get something working. Security gets in the way of stuff working, right? Like doing performance tuning. Oh, gets in the way of just making the thing work, right? It’s like, oh, I figured out how to do it.

I don’t know if it’s very fast, but it works on my machine. You don’t pry. Unless you are investing time and effort into learning and practicing a skill, you are never going to get better at it. That is true of any mental or physical endeavor in life.

If you are not regularly engaging in that endeavor, you stand no chance at progressing in that endeavor. Let’s see. Why can’t you make an Eric AI to answer these questions?

Have you seen the cost of training a model? Right? I mean, like an MCP server ain’t going to cut it. There’s not a lot of funding rounds here at Darling Data to get into an AI training situation.

So that’s way out of the question. And I don’t know. I always assume part of the charm was me showing up with my alacrity and effulgence.

And effervescence and answering these questions. From wrong, you just want AI to do it. You want a chatbot.

Well, I don’t know. What else? It’s funny because like LLMs are basically an MLM, right? Because like, I mean, just like the industry in general at this point is an MLM.

But like the people making money off LLMs are like selling prompts. Like you’re selling me a question to ask them? Like you’re just like, oh my God.

Anyone who does that is an absolute scavenger. All right. But that’s why. All right. Because Eric AI would be too expensive to train. Do you have any training content specifies performance tuning efforts and limitations for SQL Server Standard Edition?

I get excited to see posts from you in the community around batch mode and rowstore only to get let down by licensing limitations yet again. No, I don’t. Because 90 something percent of the time performance tuning with Standard Edition is exactly the same as performance tuning with Enterprise Edition.

You have some additional restrictions and limitations around hardware and what is automatically available to you. And even some restrictions on, you know, things like batch mode where it is limited to a DOP of 2 in Standard Edition. But most Standard Edition performance tuning is no different from Enterprise Edition performance tuning.

There is just not a whole lot that you have to do differently. You know, you work within your restrictions and limitations and you make the best of it. You know, a lot of the training that I have would work just fine on Standard Edition.

There’s nothing specific about it. There’s nothing specific enough about it, especially since Microsoft gave up on some of the earlier restrictions that existed with it. Some of the programmability stuff with 2016 SP1 that became available in Standard Edition.

There’s really not a whole lot too, too different there. Microsoft was even kind enough to give UDF inlining to Standard Edition. So, you know, there are some, of course, some additional considerations.

But, again, my rates are reasonable. Final question of the day. Oh, boy.

Are you doing a Black Friday sale? No. My training is already priced so that normal people in the world can afford it. I already bake a lot of coupons and discounts into stuff.

You know, I don’t want you to need, you know, a payday loan or a stipend from work in order to buy my training. I want lots of people to be able to buy it and benefit from it. So I don’t price it at a point where, like, a Black Friday sale would really get a lot more butts through the door.

So, you know, right now, no, I can’t see a reason to discount things further. You know, I do have a family to feed and all that. You know, I have actual children.

You know, actual life. There are people, like, you’ll never see on camera who I have to take care of. So, no, I can’t see a Black Friday sale being a very provocative. I can’t see too many provocative incentives in me knocking stuff down further.

So it already sells fine at the very low and reasonable prices that are set for it. So, no. No.

I’m not embarrassed by my prices. So, anyway. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video, which, if I have things right, will be a very special revival video of sorts. So stay tuned for that.

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.

Dynamic SQL Insert Bomb

---

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.

T-SQL Inline UDF Plan Caching and Reuse in SQL Server

---

 

Since it’s also Christmas, enjoy my favorite Christmas song.

Video Summary

In this video, I delve into the nuances of inline table-valued functions (inline UDFs) in SQL Server, focusing on how they differ from scalar and multi-statement table-valued functions when it comes to parameter sniffing and plan caching. By demonstrating with an example, I show that inline UDFs are automatically inlined into the calling query, which means traditional methods of tracking execution plans using extended events become less useful. Instead, I highlight how running queries with `OPTION (RECOMPILE)` can yield multiple execution plans, providing a more dynamic and realistic view of performance under different conditions. Through this exploration, I aim to help you understand when and why certain query plans are chosen by SQL Server, offering insights that can lead to better optimization strategies for your database operations.

Full Transcript

Erik Darling here with Darling Data. In today’s video, we are going to finish up our amazing, thrilling coverage of various user-defined function parameter sniffing and plan caching scenarios in SQL Server. And we’re going to finish up by talking about the type of UDF that I generally prefer, which is the inline table-valued function. To put it briefly, it is the inline UDF, the actual inline UDF. If you like this type of stuff, well, I do this type of stuff all day. Occasionally, I do get paid for it, too. If you would occasionally like to pay me for it, you can hire me for consulting. You can also buy my training, become a supporting member of the channel, ask me office hours, questions, and if you enjoy this type of stuff, please do like, subscribe, and tell a friend.

Past Data Community Summit, geez, closer by the day, ain’t she? Of course, Seattle, Washington, November 17th to 21st, two days of T-SQL pre-cons, the likes of which you have never seen. It’s a great sell line presented by me and Kendra Little. I’m not sure if that’s a great sell line. You should do, what was one from Dune? We got T-SQL pre-cons the likes of which God has never seen. God has not seen? I forget, forget the exact quote. I’m a terrible nerd these days.

but anyway you you go you come you show up you learn things it’s good for everyone anyway let’s talk about inline udf’s here so i’ve got an inline table value function here denoted by the fact that the the returns portion of the function body just says returns table right we do not return a table variable we are not returning a data type we are returning the result of a select the select that we’re returning the result of is exactly what we’ve been doing in prior iterations where we’re just doing this whole thing right so we’ve got that going for us now the first thing that i want to say here is that inline table value function the reason why they’re so different is because the code from them is automatically inlined into whatever query you run so this extended event that we’ve been using to track other executions is no longer useful to us right so if we run this query and we look at the query plan right uh we will see that we got this query plan back right but we actually got the query plan back here right with with other types of udf’s the plan for the udf is hidden away from you and but like for for this we don’t get anything back because the filters on this are for where we want the query post execution plan for the object name of co that contains cohort score and even though the name of our udf does in fact contain the name cohort score uh we we it does not execute as a separate object the the the query inside of this is like inlined into the the query that calls it right so we don’t have to worry about this extended event anymore we can close that out and please god don’t crash ssms please god don’t crash thank you always touch and go with these things but let’s run all three of these with option recompile just because um you know i talked about how um the other other versions of this i needed to make specific versions of the functions with the option recompile hint to see different query plans and write big loops and everything but this one we just naturally get three different query plans with option recompile and the end of the query right this one up here uh it’s a parallel thing we scan an index we scan an index we do some hashing this one down here has a bit more going on in it uh you know we’ve got some compute scale hours and we’ve got a seek and then we’ve got this parallel zone in the plan and then this one down here uh sort of a weird mix between the two except this one uh the sorry the second query i wish this thing would like hold its framing a little bit better but uh this one here uh is just like uh index seek loop uh loop index seek you know we’re done or rather index seek loop loop index seek loop index seek this one down here is of course uh index seek loop uh hash join down to the post table so we got three different plans with the recompile hint right there and we could see the plans without having to use extended events to track what’s going on inside the function because with other ones we wouldn’t see the actual execution plan for the function without the extended event so we’re getting somewhere yay uh so if we uh just because we’ve done it every single time we run dbcc free proc cache and we select the top one here this is kind of annoyingly slow and we’re going to talk about this later but this takes like seven seconds and it takes seven seconds because sql server chooses i mean really like a non-parallel plan and it does some silly things right like uh like like this part is probably fine 152 milliseconds but then like we we did a you know single threaded scan of 17 million rows in the post table maybe not a great idea sql server maybe maybe not such a great idea but the the point here is that we get this version of the plan back and we have the whole query plan nestled in with our query now again we’re not tracking stuff in extended events like we were with the scalar or multi-statement uh valued functions but now when we run this with uh the the wider range of things really because this is where the reputation is over 800 000 people we get back results really quickly and sql server chooses a different execution plan from the one we just saw right like this one here we’re not doing that big ugly hash join single threaded scan of the entire post table we do a seek and a loop and a loop and a seek and a loop and a seek and all this returns relatively quickly right like we could do some performance tuning work on this but i intentionally had to make parts of this not perform great to show you like the differences and the different plans if everything had a perfect plan there wouldn’t be much of a demo here would there no be bored there would be no youtube video crap couldn’t have that what would you do what would you do with your life so now let’s try this for three right so now we’re going to do this and you know and in prior runs when when we did like the the scalar and the multi-statement one for just three it was usually really quick but for this one we get the same kind of crappy query plan back that we got back for reputation equals one right it takes like seven seconds and that’s that’s not so great either i’m like not thrilled about that but again when when we run this like with like in the context of like the full query we we get a we get a good planning or we get a better plan again and this all finishes relatively quickly for the four rows that we care about right again it’s kind of like a you know seek loop seek loop blah blah so on and so forth right you get you get the idea but this all works out a lot better now uh we are going to request performance assistance i’m going to say performance assistance please and we’re going to try to figure this thing out so um like you know you might start by thinking well sql server just you know it it didn’t do a loop join it should have done a loop join right needed a loop join like the the hash join plan is stupid sql server please just use loop joins but when we just hit the loop join at the end uh this ends up taking a little bit longer right that was the last time we ran this it was seven seconds and now that’s like 12 and a half seconds so that loop join hint was clearly not effective and sql server has made an even sillier mistake here now it has done all this right again all single threaded scanning the post table aggregating some stuff uh you know then a loop join down here and then another thing down here that takes like three seconds and so this all adds up to about 12 and a half seconds of time down in this portion of the plan now normally i would do this re i would do this i would do this hint inside of the function body but just for sort of like code brevity sake uh i’m going to just take the function body and inline it into the query and add the hint to that that i would usually put in the function body and that is to just add a force seek hint right here on the post table because really what this hinges on is sql server seeking or scanning the into the post table and with a lot of uh inline table valued function rewrites you do have to do this sort of hinting because like despite them being like the superior type of function in just about every single way imaginable you know they can return more than one row they can return multiple columns there’s no table variable involved they don’t force your query to run single threaded for any reason this is just the optimizer picking a bad single threaded plan for whatever reason the optimizer is doing that for um i haven’t gotten so far as to experiment with the parallel version of these plans because uh i i wanted to get this part done and recorded so uh you know not terribly interesting there uh yet anyway but um if we add a force seek hint to this query and and we run this well now now we’ll now we’ll get sort of the plan that we want and like i said normally i would take this and i would put this in the the body of the function and i would say sql server uh where every time this function runs i am going to force you to seek into the post table because we have a great index there for that now i like i i did try other stuff in this you know like i’ve been talking a lot about row goals and oh you can add top to this and that and top and speed things up top did nothing here top was ineffective uh across this entire thing fast one hints ineffective uh i tried all sorts of hinting around things and really the force seek hint here was the only one that kind of stuck and made things tolerable so so um the bottom line here is that uh because inline table valued functions even though they accept parameters they get inlined into the code and you don’t really see the same type of parameter sensitivity plan reuse thing that you would uh when you deal when you are dealing with scalar and multi-statement table valued functions so we can tell that this is the end because down here is where i was working on the loop code that i showed you in the first video we’re not going to look at that again because we just don’t need to see that but uh i i i think that’s the end so um thank you for watching i hope you enjoyed yourselves i hope you learned something and i will see you in tomorrow’s video i have a short video on sort of a funny take on sql injection that i hope you enjoy too um it’ll be it’ll be it’ll be it’ll be a friday short we’ll call it right because it’s not long so because it’s not long it’s a short all right i think i think i’ve i’ve taken enough of your time thank you for watching i’ll see you tomorrow all right that’s that’s good goodbye you

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.