sql temp table Archives | Page 46 of 138

Performance Tuning GREATEST And LEAST Functions In SQL Server

Posted on July 18, 2024January 22, 2026 by Erik Darling

Performance Tuning GREATEST And LEAST Functionalist In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the exciting world of SQL Server’s new greatest and least functions introduced in version 2022. Erik Darling from Darling Data shares his insights on how these functions can be used effectively, especially when working across two columns in a single table or joining tables. While demonstrating their usage, he also highlights some performance considerations and suggests ways to optimize queries for better speed, such as using early aggregations. The video is packed with practical examples that showcase the functionality of greatest and least, along with tips on how to emulate these functions if you’re not running SQL Server 2022 yet. With a touch of humor and self-deprecating remarks, Erik keeps things entertaining while delivering valuable information for database professionals looking to improve their query performance.

Full Transcript

Guess who? Erik Darling, Darling Data, recording a video about something in SQL Server. Shocking, I know. Shocked me every day that there’s stuff to talk about still. Uh, you know. Maybe someday there’ll be something new, or maybe someday there will be nothing left to say, but until then… Gotta keep going, I guess. Until I rub the eyebrows off my face. Uh, alright. So, uh, in this monster of a video, this absolute savage unit of a video, we’re gonna talk about, uh, well…

For the… for the sake of demo length, we’re gonna just talk about the greatest function, which is new to SQL Server 2022. But the greatest function is… was accompanied in… in release by the least function. So you can do greatest and least, uh, right? So, uh, every other database platform for, like, 20 years has had the greatest and least functions. And in… sometime in 2020, 2021, uh, Microsoft said, Oh, yeah, we better play catch up on this… this basic database functionality stuff that, uh, other databases are wiping the floor with… floor with us with.

Um… Yeah. So, great. Um… SQL Server 2022 has the greatest and least functions. If I had a kazoo, or a party… one of those party things, I would… I would… I would do that right now. Uh, but I don’t have either one, so just use your imagination. Pretend I am, uh, interacting with, uh, with party noise-making, uh, devices. Assuming that you’ve been to a party and you know what I’m talking about, um…

Maybe, maybe, maybe, maybe, maybe one time you saw a presentation about parties and, uh, noise-makers were part of… part of the presentation. Uh, you could… you could fill in that blank if you… are… are… are not a… hyper-social being. Who works with databases would be shocking, wouldn’t it?

Uh, it’s like… it’s like that joke about, uh… Well, uh, I don’t know. I guess IT people in general, it’s, uh… Uh, how do you tell the difference between an introverted IT person and an extroverted IT person?

And it’s that, uh, an introverted person stares at their shoes while they talk to you, and an extroverted IT person stares at your shoes while they talk to you. So…

Uh, yeah. There’s that. So, uh, we have the greatest and least functions. And I’m gonna show you two examples of them. Uh, one is just across two columns in one table. And the other is across two columns, uh, in join tables.

And, uh, honestly, for the single table thing, it turns out fine. Uh, but for the double… the join table thing, uh, I am a little bit less in love with the query plan. Um, it’s… it’s a bit slow for me.

Right? I don’t… don’t enjoy slow queries. Uh, and this is… this is gonna be true of… Uh, well, other examples. Well, I’m gonna show you. Well, I’m gonna show you.

So, if you’re not on SQL Server 2022, right? And you’re… or whatever other problems you have in your life, uh, there is a way to emulate the greatest and least function… greatest and least functionality.

Whew! Um, it’s… it’s too early for me to be drunk, so don’t… that was just a mouth malfunction. A mouth function. Uh, you can emulate the greatest and least functionality, uh, by doing the old cross-apply trick.

Um, supplying the… the columns that you need to greatest and least as values, right? And then aliasing, uh, that as a single column to reference. And then, um, from there, uh, finding the max of whatever is in that aliased column.

So, whatever comes out of combined date. You can’t emulate greatest and least functionality doing that. Uh, the problem is that, uh, you know, performance for this sort of thing is not all that hot.

Um, you know, again, for the single table query, it’s not that big a deal. Uh, but for the multi… for the join and if you’re, you know, doing other stuff, that’s a three and a half seconds. The greatest version of this was, like, was only about 500 or so milliseconds faster.

So, performance isn’t necessarily great for that. Uh, and, uh, batch mode does change the picture a little bit, but not enough to, um, not enough to make me happy. So, if you do need to emulate the greatest and least functionality in SQL Server, you may want to write in, you know, you’re unhappy with the speed of the query.

If you write it the norm… the way that I just showed you and speed is fine, like, you’re not worried, like, like, performance is good enough, don’t worry about this. But if you’re unhappy with performance, uh, you can always rewrite the query a little bit to do some, uh, earlier aggregations. Um, I have another video, uh, about SQL Server performance where, uh, it just… the optimizer just doesn’t spot an opportunity for, uh, earlier aggregation and, um, performance sucks.

And we have to kind of tell SQL Server what to do. Uh, so we have… we’re gonna… what we’re gonna do is we’re gonna do… we’re gonna aggregate early on our own the max score from posts and the max score from comments. And then we’re gonna use, uh, the… the cross-apply just to continue with the emulation of the… the greatest and least functionality.

We’re gonna… just wanna do this. And, uh, this will be a lot faster. Right, the early aggregation allows us to kinda hit these two tables, uh, bring the rows that we need to figure out the… the max of, uh, a lot faster, right?

So we… oh, geez louise! Zoom it! I hit control! Why are you messing with me? Why? Uh, we do this. We do the early aggregation here. Uh, and then we do a secondary aggregation to bring things, uh, down to one row even further here. And, uh, this all takes about… well…

It’s another real curious case of… uh, uh, operator times and execution plans. Looking real stupid. Real bad. Uh, kind of embarrassing, actually. Uh, this… this query doesn’t take two seconds, as the gather streams operator might have you believe.

It really does take 1.059 seconds. Uh… Yeah. No. Summer interns. Uh…

Well, yeah, that’s about that. Alright, well, now I’m just depressed. Um, thanks. Thanks, SQL Server, for that. Uh, let’s just wrap this up with the demo to show you…

Uh, that that can also work well for a single table query. Uh, not that this was slow before, but, uh, you can… you can express, uh, the… logic in many different ways that can… they can help with performance in many different situations.

Uh, usually, I find, when I’m query tuning, that, uh, I… I need to guide SQL Server towards doing aggregations a lot earlier, uh, than it sometimes does. And, you know, usually, uh, that… those… those, uh, those… as long as there is a meaningful reduction in the number of rows via those aggregations, uh, you should see pretty decent performance improvements. You know, like, comparing, um, this plan up here…

with the greatest and least. Alright, and… Run this again. Uh, this is real goofy, because SQL Server scans both indexes. Uh, there is no early aggregation before the join.

And the aggregation only occurs at the very end. Uh, for some reason, SQL Server doesn’t think to do anything between… getting…

17 million rows and 20… almost 25 million rows, and… joining those fully together and then aggregating stuff. The optimizer’s like, no, this will be fine! Alright, so we have our initial aggregation here, and then our final aggregation here.

Uh, but, um… Like I said, my general experience is that it is a lot better and faster if you guide SQL Server towards the correct… uh, the correct early aggregation strategy like this.

Um, even though this filthy idiot liar of a gather streams operator is still trying to convince us that our query… took two seconds to finish. It really took one second.

So… Alright, well, I wasn’t drunk before, but I think I’m gonna go start on that now, cause… I’m gonna go start on that now, but…

I’m gonna go start on that now, and I’m gonna go start on that now, and I’m gonna go start on that now. Well… I mean, look at that. Look at that thing in front of you. I don’t know…

The thing… The thing is real ugly. Uh, we’re gonna… We’re gonna… We’re gonna have to… Have to drink to cope with that. Sorry to say. There’s no other alternative. Uh, I’ve…

I’ve jumped out of every single window that I have available. The glass is shattered. Uh, it is no longer as dramatic and exciting… An exit, as it was when the glass was there. Uh, so now…

Now we’re just down to drinking. To deal with… These inconsistencies. So… I’m being dramatic. Calm down. Uh, anyway. Thank you for watching.

I hope you enjoyed yourselves. Hope you learned something. I hope that… Uh… You will not… Be as offended as I am at these operator times and query plans.

They are… Truly… Truly awful things… Sometimes. And, um… If you like this video…

Thumbs ups… Are good. Um… Inspirational comments. Live, laugh, love. Um…

Add years to your life and life to your years. Peanut butter and onion. Uh… All nice things to hear about. So… Um… If you like SQL Server content… Uh…

Despite… This monstrosity that you see before you… Uh… You can subscribe to my channel. And we can at least laugh at this stuff together. Uh…

I probably won’t be drinking on camera. Cause that would be very unprofessional. But… Um… What… What happens below this line… Stays below this line. Lucky for you.

So… Um… Yeah. Anyway… Uh… Subscribe. Like. Comment. What else… What else can you do on YouTube? I don’t know. Anyway… Uh…

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.

A Little About Parameter Sensitivity And Startup Expression Predicates In SQL Server

Posted on July 17, 2024January 22, 2026 by Erik Darling

A Little About Parameter Sensitivity And Startup Expression Predicates In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the world of parameter sensitivity in SQL Server stored procedures, focusing on a common coding pattern that can lead to suboptimal performance. Erik Darling walks through an example where a procedure uses startup expression predicates based on dynamic parameters, which can cause significant issues with query plans and execution times. I explain how these predicates can make your job easier but complicate SQL Server’s optimization process, leading to poor estimates and wasted resources. The video also covers the use of `OPTION (RECOMPILE)` hints as a potential solution, while emphasizing that this is not always the best approach. By sharing practical examples and insights, I aim to help you understand how to write more efficient stored procedures and avoid common pitfalls in your SQL Server coding practices.

Full Transcript

Erik Darling right here with you, cranking away, having a great time, trying to figure out where to put stuff so it’s less distracting than my desk. One of the most distracting things on my desk is the lens cap to my camera because it’s not quite a fidget spinner but it has this very pleasant squeezy motion about it so you can really get a pretty decent two-finger workout. on something there. I don’t know. I don’t really know what good that would do you. Picking up Legos. Pick up those Legos. In this video, we are going to talk about parameter sensitivity in the context of startup expression predicates. If you don’t know what those are, don’t worry. I’m going to answer that question. Relax. Calm the hell down. crazy. Alright. So this is a coding pattern that I see at a lot of client installations. I don’t know what the hell else to call them. Client sites. Like every client is just a website I visit. And they’ll, you know, coming back to my favorite SQL Server adage that I’ve ever said, is anything you do that makes your job easier, makes SQL Server’s job harder. And this is one of those cutesy, like, you know, got your nose optimizer things. Maybe that’s what this is. Got your nose. And this, this will just do you no good. So I see this pattern a lot where they’re like, oh, we’ll only join if this thing is true. Problem is, sometimes this thing will be true. Sometimes it’ll not be true.

And when you ask SQL Server to create query plans based on this type of logic, you will be quite unhappy in the long run. Things will not go well for you. So let’s make sure that we have the correct version of this procedure running. It happens, it happens a little too often to me where I forget to create the crappy original version of the procedure. And I go execute it and I’m like, wow, that was amazingly fast. Did the optimizer change? No, I just, I just didn’t overwrite my fixed version of the code. So we’re going to make sure the crappy version of the code, we’re going to make sure your version of the code is in there, not my version. My version’s good. Yours sucks. Stinks. Look at this thing. Ugh. Disgusting. Throw it in the trash.

And so we’re going to just, you know, because we are incredibly superstitious people, we are going to recompile this procedure. And what we’re going to do is execute this. Once to look for posts and once to look for comments. Now I realize, a lot of you are probably sitting in the back screaming, yelling, ugh, but what if you check for posts and comments? Or what if you check for neither? Or what if this, blah, blah, blah, blah, blah, blah, blah, blah, blah. Well, you know what? This is, at some point these demos have to fit on one screen. And at some point these demos have to fit in one video.

If I go through every single iteration and permutation possible of parameter combinations, not only are we going to be here for a long time, but no one’s going to watch it because it doesn’t get to the damn point. All right. So just keep that in mind that none of the demos I’m going to show you have proper handling for both and neither. Right. It’s either one or the other. Okay. That’s just what we’re dealing with in this one.

So here’s the execution plan. And what we have is the first one that finished relatively quickly because SQL Server cashed an execution plan for the first execution of this procedure where we were hitting the post table. All right. And everything turned out pretty okay for the post table. But if we look down here, we still have the comments table in the mix. All right. And if you look at what the estimates are for the comments table, we have all, well, I mean, it’s all one row.

All right. So this is not a very good, not a very good estimate at all for future executions of this store procedure. Now, again, you may be out there in the world and the void, the void beyond the screen screaming. You can just throw an option recompile hint on there and you absolutely can.

And if you do that, I’m not going to argue with you. It’s a fine choice. It’s totally okay with me. I am okay with option recompile. Do it. I don’t care. If it fixes things quickly for you, go for it. Right. Do it. Right.

What am I going to do? Spank you? That costs extra. All right. Family friendly SQL Server consultant. If you want that kind of action, big bucks. All right. So the second execution of this query takes about nine seconds.

All right. 8.7, which is close enough to nine. And the thing down here is that that bad, that one row estimate on the comments table from the first execution really hemmed us up bad there. All right. Because we got a lot more rows. We got a lot more than we bargained for in here.

All right. So this is probably a good point to tell you about startup expression predicates. All right. Because that’s what I said was going to make this thing parameter sensitive. And that is what’s happening here.

So in both of these query plans, you’ll notice we have these filter operators. All right. There’s one up here and there’s one down here. Usually when I see a filter operator, I get nervous.

Well, I mean, yeah. Usually when I see a filter operator, I get nervous. Unless I know that it’s there for a reason, like we have a having clause on some aggregate, or we have a windowing function where we’re filtering to, like, for example, row number equals one, where you need to generate that expression at runtime and then filter on it later.

Whenever I see a filter without one of those things, I get a little antsy, because that can often mean that we have written a predicate that is very complicated or very non-sargable, the opposite of sargable, anti-sargable.

And usually that means performance is going to suffer in some weird way. It can also mean that we’ve written a left join with some sort of null or not null check on the left join to table, in which case we should be using not exists or exists or whatever would logically fit the scheme of the query.

But in these cases, what we have here are these startup tooltip. You know, you show up when I don’t care about you and when I don’t need you, and then as soon as I need to show you, you disappear on me.

So in these filters, we have a startup expression predicate, which means that only rows that pass through this filter will touch the table, right? So if we can just keep hitting this filter with nothing, and eventually SQL Server will say, okay, well, nothing passed, so we’re not going to do anything with that table.

There should be no I.O. for the post table in this execution, because nothing passed this predicate, right? We had a whole bunch of rows try, but nothing did, right? They all failed.

None of them passed the predicate. For the comments section, where we have check comments equals one, well, a whole bunch of these do pass, way more pass than what happened before, and this is part of the cardinality estimation process.

SQL Server comes up with an execution plan based on these parameter values, and it says to you, well, comments score, sorry, if check comments is zero in this procedure, then I don’t expect any rows to pass that.

Sorry, you’re going to get a plan for that, and performance will inevitably suffer. So what a lot of people think is also a good and cute, well, cut your nose, that’s why we do our squeezy finger exercises, so we can be really good at grabbing the optimizer’s nose, right?

Boom. So what a lot of people also do is something that they think is clever and cute and will work like this, where they’ll separate these things out.

And again, I know, I don’t have a code blocking here that checks for both equals one or both equals zero. I get it. I don’t have any handling in here for those scenarios.

You’re just going to have to live with it. You’re going to have to take these two fingers and pinch whatever part of yourself helps you cope with the fact that I am not writing the most robust implementation of this stored procedure that I possibly could.

All right? Deal with that fact for a few more minutes. Thing is, this doesn’t help either.

Bare naked if blocks suffer a similar fate as the sort of startup expression predicate type code, where, and I’m just going to show you an estimated plan real quick. All right?

So SQL Server will compile and figure out a query plan for both of these branches on the first compilation, regardless of if both branches are explored or not. You get a fully-fledged execution plan based on whatever parameter values get passed in the first time, and you end up in a situation that’s quite a bit like the non-if block startup expression predicate portion.

All right? So if I execute these two things, we’re going to see a very similar outcome to the above query, where the one for the initial compilation for posts, totally fine.

The second run for comments, not so hot, right? It’s about 9, 10 seconds again. So if we look at these two query plans, almost identical outcomes just in two execution plans rather than one. All right?

We have 741 milliseconds here and 9 seconds here. So clearly the if block thing does not work so hot either. Now, again, if you want to throw a recompile hint on the first query, you can do that. If you want to do the if branch thing and throw a recompile hint on both queries, you can do that.

I don’t care. Whatever you want to do, roll with it. One way that you can solve this problem without recompile, you can even keep your if blocks, is to just use dynamic SQL parameterized, of course, so that we don’t get anything terrible happening to us.

And you don’t even have to, like, start declaring variables and all sorts of other things. I took an incredibly lazy way out of doing this. And I just, instead of setting a parameter to this, I’m just putting the whole string in there.

Screw it. If I’m going to make you angry with not having error and code checking for every potential combination of check comments and check posts, well, I’m just going to do this too.

All right? And if we create or alter our procedure, this is the one that would have run the first time and made me look somewhat foolish. But if we run these now, SQL Server is able to cache and reuse these execution plans over and over and over again.

And performance for the comments run no longer sucks because this is now treated like its own individual query. And whatever happened before doesn’t actually matter all that much. For the post run, this is just about as fast as it’s been for anything else.

Is there more that we could do here? Yes. Absolutely.

There are indexes we could add, tweaks we could make to the query. Sure. There’s probably lots of stuff we could do. There’s probably thousands of things that we could do. What they are, I can’t even possibly begin to enumerate the billions of alternatives we have for tuning this query.

It’s just an insane amount of, insane variety of things that we could do to fix this thing up. But that’s probably for another video. So in this video, we learned that anything that we do that makes our job easier, like writing shortcut code like this, makes SQL Server’s job harder.

We end up with startup expression predicates, and these startup expression predicates are based on the passed in parameter values. And if we do a big switcheroo and we sometimes join to one table and sometimes join to the other table, whatever table we compile the plan for first is going to be okay.

Whatever one we didn’t compile the plan for first is probably going to suck. If you want to throw an option recompile hint on this, you have my blessing. I will write a letter to your employer.

Say option recompile is okay for a small fee. If you want to do this, it will also not work without option recompile hints. You will have typed more.

You will have looked busy for another 30 seconds, but you will not have solved a problem because bare-naked if branches like this where you just execute raw queries in them do not actually separate, does not actually give you any sort of performance fencing.

The logical fencing is still there. The performance fencing is not. SQL Server compiles a plan for both of these on the first execution. If you want true separation, you can execute something else, like sp-execute SQL with dynamic SQL.

You could put each of those queries into a store procedure and execute that store procedure within that if block, and that would give you true separation. You could also add option recompiles to absolutely everything.

I won’t care. I like option recompile a little bit. It’s kind of a good thing. So, if you’re going to write this kind of code, you can always also hire me to fix it.

Thank you. I love you. I hope you enjoyed yourselves, as usual. You might be able to tell I enjoyed myself a little bit. I do hope you learned something, or maybe picked up on something new.

What else? Gosh almighty. So many things to say. So little time. Sometimes I just want to pull up a chair and talk to you all day.

If you like this video, thumbs-ups are nice. Comments are nice. Smiley faces. Emojis.

Lots of emojis. Just a crap ton of emojis. Totally cool. If you like this sort of… If you like SQL Server… I don’t know. This sort of content. If you like SQL Server content, because I have a wide variety of SQL Server content, most of it is performance tuning.

None of it is backups, like maintenance, high availability, disaster recovery, because what a snooze fest that stuff is.

It’s all pretty much like performance tuning, query writing, stuff like that. Subscribe to my channel. Like almost 4,000 other people have. Because it’s a nice thing to do.

For a handsome young consultant, so we can continue to afford getting haircuts to look good in these videos. We can’t have ugly consultants on video, can we? Bad idea.

Anyway. I’ve got things to do. I’m going to go do them. Before my wife yells at me. Thank you for watching.

Going Further

A Little About Index Design Patterns In SQL Server

Posted on July 16, 2024January 22, 2026 by Erik Darling

A Little About Index Design Patterns In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into index design strategies and their impact on query performance in SQL Server, specifically focusing on how indexes affect filtering and ordering. We explore why missing index requests often fall short of expectations and delve into the nuances of creating effective indexes for complex queries. By analyzing two sample queries and experimenting with different index configurations, I demonstrate that while some index designs can significantly improve query speed, others may not yield the desired results due to data distribution and query specifics. The video also touches on common misconceptions about index leading columns and the limitations of SQL Server’s missing index feature, providing valuable insights for database administrators looking to optimize their indexing strategies.

Full Transcript

Erik Darling right here with you, live, sort of. I mean, I feel pretty lively. Today, what are we going to talk about? I don’t know. How about some index design strategy? All right. We’re going to talk about how indexes affect filtering and ordering, and we’re going to talk about why, I think for the 10-bajillionth time, why SQL Server’s missing index requests are quite often underwhelming in their very existence. And how there is only one person to blame for that. He doesn’t care. All right. So, let’s make sure we got nothing going on here. Oh, God, I had some statistics. Look at these awful things. I bet they’re duplicates. I bet someone should delete those for performance. That was a callback to another video. Welcome. Welcome to my brain. It’s awful. It’s not a good place to be. It’s treachery. All right. So, we got two queries here. Let’s make sure query plans are enabled because those are important for everything that we do. They mostly function correctly, which is nice. Sort of like my brain. Mostly functional.

So, we’re going to run these. And we got no indexes right now at all. Zero indexes. Both of these queries return the required 5,000 rows. The problem is that neither one of these queries is great and fast and amazing. When we search for a sort of small amount of data, we scan a clustered index. We sort all our data, and it takes about 600 milliseconds. Not the end of the world, but you know what? Not every query to any extra exercise needs to start with something that takes 35 minutes and ends with someone being like, dude, I added an index. It was three seconds. It’s not every story. Not everything ends that way. It’s annoying. And when we search for more data, and we have to do some more sorting, we end up spilling a little bit here. Ouch. Ouch. I chihuahua. And then this one takes about 1.1 seconds. Almost twice as long as this one. Monocounted this sorty spilly thing over here. And I guess there’s like 100 milliseconds on that. Now, these queries have both asked for the exact same index. On post type ID, last activity date, as the key columns, because they’re in the where clause. And then include score and view count, because they are in the query, but they are not in the where clause. Our only columns in the where clause.

Right here. Right here. Post type ID and last activity date. We are ordering by score descending right here. Which seems important. It seems like something that an index would help with. Doesn’t it? Seems like we maybe had an index. Maybe we wouldn’t have to sort that data. Maybe we wouldn’t have to risk spilling that data to disk. Maybe we could have these queries run and not ask for any memory. Wouldn’t all of these things be grand? The thing is that the index that SQL Server asks for, which is just about this one. If you remember the green text above, here’s our where clause columns. And here’s our oh, they just happen to be there columns.

So let’s create this one. And I’m going to name this index whatever, because I’m allowed to do whatever I want. It’s my computer. And in the words of John Crook, I’m taking my ball and going home. So this index takes a minute to complete because, well, you know, there has been some chatter recently. I had a video about why some indexes are slow.

And of course, Paul White saw that video and absolutely dumped on me with the technical details for why the actual technical details for why it’s slow. Not just me being like, yeah, because they ended up on like three threads and it stunk. He like broke down the entire algorithm of parallel index creation. I will find the link to that and put it in the show notes because, gosh, gosh, is it impressive.

All right. So now we have the index that SQL Server asked for. Both of these queries will use that index. And both of these queries will, well, sort of benefit from it. This query does spectacularly with it. Does great. Four milliseconds in a seek, six milliseconds in a sort.

This one gets about, this one does better too, right? It’s about twice as fast as it used to be, right? It went from 1.1 seconds to about 600 milliseconds. So now this query with the index is about as fast as this query was with no index, just the clustered index on the post table.

So, and this is a, this is the sort of mixed bag that you get from most of Microsoft’s advice. Some things, yeah, they get better. Other things, not so much. It’s not great. It’s not fantastic. It’s just okay. So let’s, let’s try an index that I would, I see a lot of people go for when, when they’re dealing with this stuff.

Because a lot of people have this strange misconception about how indexes work. And a lot of people will think that if they have to sort by something, that that sort column has to be the leading column in the index. Right? So because we’re ordering by score descending, they think, whoa, well, we’re ordering by score descending.

That better be first so that we have that index sorted by that. So let’s, let’s recreate the index with score descending first in the index. And we’re going to see what happens in this case, which is, well, I should probably not hover down too low.

Because that’s going to completely spoil the final index that we’re going to create. And, I don’t know, ruin the whole video. Way to go, me. All right. So let’s run these two now.

And with score very first in the index. Well, it kind of flip-flops here, doesn’t it? Ugh. This one slowed down a lot.

This one is slower than it’s ever been. Now, both of these, both of these queries are single-threaded now. And the second query, well, this one, holy cow, zoom it. What are you, what is on your mind?

This one, really fast. Right? This one’s crazy fast now. This one, crazy slow now. The reason for that is because, well, to talk about post-type IDs a little bit, post-type ID 4s are pretty rare in the data.

There are not a lot of post-type IDs 4. Most of the post-types in the Stack Overflow database are, one, where there are about 6 million, two, where there are about 11 million, and then 3 through, like, 8 or something, which there are, like, a few hundred thousand.

So post-type ID 4 is rare. So once we sort by score, it still takes a whole lot of, like, it still takes a whole lot of, like, scanning through to find all the, find post-type ID 4 columns in that index.

So that’s not a very good index choice either. So we still have very mixed results. We have, we’ve created two indexes now. The first index helped the first query and sort of helped the second query.

The second index completely just neutered the first query and really helped the second query, right? The second query is now as fast as the first query was with the first index.

Promise that makes sense. So let’s go with the third index here because one thing that a lot of people overlook about B-tree indexes is that when you navigate them correctly with equality predicates, order is preserved throughout the, throughout the, throughout the, the second, the column after the equality predicate.

So where, you know, columns for, like, post-type ID where there are a lot of duplicates, you’re going to have, like, a whole lot of the number 1 and then all the scores and, like, for that range of number 1 are going to be ordered descending in this case, because we said descending in the, in the index definition right there.

Look at us, look at us go. And so that, that helps, right? Because we seek to the number 1 first in the index and then we have score in the order we care about and then last activity date, I mean, that’s an inequality predicate over here.

So, you know, it’s, it’s maybe not the most ideal situation to have score gatekeeping last activity date. Maybe some situations where I wouldn’t want that.

But now, with that index in place, both of these queries are equally as deliciously fast. Now, the important thing is, let me bring these up, let me get it in there, squeeze in there, everyone hug.

Big hugs for everybody. So the two important things that this index did for the, for the query that we’re running here. One, it allowed us to seek to the post type ID that we care about.

Two, notice that there’s no sorting in this index, in this query, query plan rather. There’s no sort operator. There’s a top, but we don’t need to sort the data because after we seek to post type ID, we have score in the order that we want it to be.

Then we’re able to evaluate our last activity date predicate as the final part of the seek. But it is a residual predicate, right? It is a predicate way up here in the residual nosebleeds.

And if Zoomit would listen to me, we would have a much better video. We would have high quality videoing. So last activity date is a residual predicate up here.

Post type ID is a seek predicate in here. And yeah, we don’t do any sorting. So without needing to, without actually having to sort data, again, the stuff that we avoid, you know, if this were a properly parameterized query, we would avoid, like, you know, parameter sensitivity issues with memory grants.

We would avoid, you know, potentially spilling the disk, potentially asking for way too much memory for some executions. We would not avoid green screen fuzzies over there. Those are annoying.

Anyway, a little bit about index design there. A little bit about how to approach different index design choices. Remember that the missing index request feature has a lot of blind spots and does not aim to please every area of your query.

It only aims to please the where clause. Other relational areas that are great, relational areas, yuck, that are good to index for, like order by, group by, joins, things like that.

It just ignores them and sticks those columns in the includes, which isn’t cool at all. So careful, careful with those. Careful with the green text, as they say on 4chan.

Anyway, I’m going to go do something with my life. I don’t know what it is. Might be good, might be bad, might jump out a window again.

We’ll have to wait and see. Anyway, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. If you like this video, compelling comments, thumbs ups, are great, great ways to tell me.

If you don’t like the video, and you feel so utterly driven to click the thumbs down button, I mean, you can tell me why if you want. I don’t care.

You can also just click thumbs down, and I’ll just say, oh, you’re that one person again. But you can also share your thoughts and feelings with me. I want to know. I want to know what love is.

Actually, for you, it’s probably hate or something, right? You’re giving me a thumbs down. If you like SQL Server content about performance tuning, indexing, query tuning, what other spam SEO words can I throw in this?

I think I’ve run out. Subscribe to my channel. A lot of other people do. We’re coming up on like 4,000 people. 4,000 people get notified whenever I say something, which is strange, to say the least.

Anyway, thank you for watching.

Going Further

Join Me At Data Saturday Dallas Sept 6-7

Posted on July 15, 2024June 26, 2024 by Erik Darling

Spring Training

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

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. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Stop Worrying About Duplicate Statistics In SQL Server

Posted on July 12, 2024January 22, 2026 by Erik Darling

Stop Worrying About Duplicate Statistics In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the peculiar behavior of SQL Server statistics and why deleting duplicate statistics might be a pointless exercise. I share my observations on how people often get fixated on minor performance tweaks that don’t significantly impact their server’s overall performance. By creating a simple utility table and running queries to generate various statistics, I demonstrate that SQL Server only updates one set of statistics even when multiple are created for the same column. This video aims to provide clarity on why focusing on duplicate statistics might be more about performing a micro-optimization than addressing real-world performance issues.

Full Transcript

Hey, it’s Erik Darling with that Darling Data Company who does the SQL Server Consultant Training and Education. And that’s him over there in the corner, losing his mind. In today’s video, which might be the second video I’ve recorded today, there’s no way for you to know. Good luck with that. We’re going to talk about, I mean, this is really just sort of like a funny behavioral thing. I see a lot of questions out and about in the SQL Server internet where people have this very strange fixation with deleting statistics. They’re like, I want to delete old statistics or I want to delete statistics based on something. Right? Like systems, duplicates, duplicates, like this thing, this problem they have where they just refuse to like, uh, buckle down and like do anything that might meaningfully help their server go faster. They get like micro fixated on these dumb things that aren’t going to help you. Right? It’s like when, like, when people have like the most basic ass performance problems and they’re like, gonna look at spit locks. You don’t like for what? Like you haven’t added a single index to any of these tables. Your queries don’t have where clauses. Like, uh, your SQL Server is a single core with four gigs of RAM. Uh, your VM admin hates you. Like you, you just don’t, you’re not, you’re not really gonna help anything by doing that. Um, if you’re, if you’re SQL Server is, uh, is tuned up to the point where you have time to sit back and think, I wonder if duplicate statistics are dragging me down. Uh, I want you to go on vacation or get a new job or maybe learn about a different database thing in the world. I don’t know. Like there, there are a lot of things that I would do, uh, rather than go looking for duplicate or I don’t know, like somehow try to figure out if statistics are going to be a little bit better.

or if the statistics are unused or not. And, um, and delete them because it’s, it’s, it’s just such useless performative garbage that, uh, I, I can’t take it seriously. Every time I see that question come up, I’m like, oh, you just don’t have no idea what you’re doing then, do you? You just, you’re just clueless in the world. It’s floating, floating on the ocean, wherever the breeze takes you. Um, so, uh, this is just kind of a strange little video about, um, like how SQL Server doesn’t update duplicate statistics. All right. So we have that to look forward to. So what I’m going to do, I’m going to create a little utility table called user stats. Uh, the definition of the table, um, the contents of the table have very, very little, uh, importance. Um, it’s just a, it’s just a table that I can kick around without worrying about like messing up data in the actual users table.

Because I need to run some updates. All right. So we just created this table. We just inserted rows into it. I think anyway, we should check the query plan to make sure something actually happened there. And it did. We put all 2.4 million rows in the users table, uh, into the user stats table, but just for a few of the columns, right? We only have ID downvotes, upvotes and account ID, right? That’s all we need for this one. So, uh, let’s look at statistics currently, right? Because this table has a clustered primary key on it. The thing is, we have not yet run a query that would cause SQL Server to generate statistics. Now, we created a table, and we created an index, and we loaded data into that index. If we had created the index after the fact, it would do a full scan, stat sampling of the data in that column, and we would have something here.

But, because we have not queried the table in a way where we had the index first, the data load second, we haven’t run a query to say like where ID equals one. SQL Server has not generated statistics for this index. All right? So, but this is not the, this is not the statistics object that we care about. We’re going to mess with a different column, and we’re going to run this query, where we’re going to be looking for, uh, account IDs within a certain range, and we are going to get this incredibly lucky number back. All right? Incredibly lucky. Uh, Chinese stuff, eight is, eight is a lucky number. Uh, when, when I lived in Chinatown, it was very funny, because like in, in American buildings, uh, the, we always skipped the 13th floor, and the buildings that were like built in Chinatown, they always skipped the fourth floor, because four is unlucky, but eight is very lucky.

So we have a very lucky number here. We have four eights. The only thing that would make this luckier is maybe a fifth eight, but I don’t know if there are rules around how many eights would be an unlucky number of eights, because I’m just not that culturally aware. Um, it was never really explained to me. It’s a little, a little strange. But anyway, uh, with that query run, um, and, uh, we can come back and look at this. And now we see that we have, we still have nothing on the primary key, which is okay.

We are never going to have anything on the primary key, because we’re not going to query and filter on the primary key. But now we have this system statistic called WASIS-OOF-47-BIF-90. Uh, it has all those rows in it. Uh, not all of those rows were included in the sample. Right? That’s a much smaller number than that. And, uh, it has had no modifications against it. All good there. All Gucci all the way down.

Now, I’m going to run, uh, a couple updates against the account ID column, and I’m just going to mangle the hell out of it. All right. And that’s, it’s all well and good. This is going to run for a few seconds. And now we’re going to look at the same statistics thing. And now we can see that we have a whole bunch of modifications.

Now, the only reason why I have where one equals one at the end of these is because if I don’t do, like, I know that there’s an option in SQL prompt to, like, not get yelled at. But when you have, like, a modification query without a where clause, uh, I just think it’s funny to leave that on and have SQL prompt say, oh, where one equals one. Cool where clause. No warning for you. That’s just kind of amusing. Anyway, uh, where was I?

We have now a bunch of modifications against that statistics object. Right? So if we rerun that query, SQL Server will re-update. Oh, sorry. I don’t need to show you the query plan for that. I’m, it’s such a weird, uh, muscle memory thing that I look at the query plan for everything.

I didn’t need to show you that query plan. But if we look at the stats now, uh, we will see, um, some, some rows sampled. Uh, I think that’s a smaller number than before. And, uh, now we’re back to zero modifications because we just, uh, updated those stats, uh, when that query ran. And I guess if you needed any proof about when this video got recorded, there it is. Um, the time is a little bit off because of my server.

For some reason, all my Windows servers installed in Pacific time. I have no idea why that happened. I didn’t choose it. I know I could change it. I just choose not to. Okay. Great. So here’s where the duplicate stats thing comes in. Right? So let’s say we have this index. We’re going to create an index on account ID. Right? We already have a statistics object on account ID, uh, because that’s the thing that we’re querying. Right?

So SQL Server created a system stat on account ID. And now we’re going to create an index on account ID and a statistics object on account ID. We’re going to do both, both things. Right? And now we’re going to run this, uh, not that query. We’re going to run this query again to look at these statistics objects. And I want you to just note, uh, that, you know, we have, uh, now three duplicate things. Right?

We have the system stat on account ID. We have an index on account ID, which produces a statistics object. And we have a custom user statistic on account ID. Right? And, uh, the rows sampled for these are both going to be equal to the number of rows in the table because they were made with, I mean, the index is full scan by default.

And these create stats thing, I added the full scan option to that. So we got that there. Okay. Cool. Let’s run these updates again because these updates are going to take a little bit longer than, uh, before, because now we have, now we have to update the index on account ID and that slows us down a little bit.

Actually makes it, it makes it go twice as slow or half as fast. However you want to, however you want to, uh, however you want to call that. And now if we look at this, we’re going to see all three of these stats objects have a whole bunch of modifications against them. Right? All three of them have modifications. All three.

The thing is if I run this query, you get a count, which finishes pretty quick. And I’m going to remember here that you don’t need to see the execution plan for this count query. Good job, Eric, darling. You did it.

And now we go look at this. Well, what do we have here? SQL Server only updated statistics for one of those.

Great. Great. Love it. Love it. Love to see that happen because we know that SQL Server didn’t update three separate statistics objects, uh, on, on, on the same, the same duplicative field.

Uh, we, we are a little, we, we are allowed to be a little bit disheartened that, uh, SQL Server, we, we, we used to have this nice full sampling of the statistics down here, but now we have this, this boohist, uh, default sampling of the, of the, of the, of the, for the statistics object there.

Not that that’s the end of the world. You know, it just kind of sucks that you go from like the big full scan of stats to like the little default sampling of stats. Now I know that there are settings in SQL Server where you can say, no, no, no.

I want you to maintain like whatever sampling percent I choose every time stats are updated, even if they’re auto stats, uh, because, no, I, I demand that level of control. No.

So you do have that option available to you, whether you use that or not. It’s up to you. You might find a great use for it. You might never find a use for it. Uh, I don’t care unless you’re paying me. Then I care a lot.

It’s like faith no more. So when might duplicate statistics be something that you care about? Well, it’s, it’s really hard to figure out from a query optimization standpoint, um, why or when you might care about, uh, duplicate statistics existing. Uh, it doesn’t really add that much, really doesn’t, it doesn’t add a whole lot to the, the, the query optimization conundrum.

Uh, the one thing that might be kind of interesting is, uh, you know, doing statistics updates maintenance, uh, where, you know, I, I, I do, you know, reasonably agree that it would be maybe not the most beneficial use of time for you to update. Uh, so like, you know, like if you’re, like you’re using maintenance plans or older scripts, you know, and you say, hey, well, you know, look for things with modifications. These are modifications and these would get updated, whether they get updated and used or, uh, whether they take a long time to update and they mess up your maintenance plan window.

I don’t know for this table, definitely not, but in general, uh, it’s just, it’s just really not going to make a difference, uh, to, to the general, general query performance on your server. If you go around and start getting rid of duplicate statistics, SQL Server doesn’t, is, is a little bit smarter than that.

Uh, at least, at least in this one regard, which is, which is, I guess, a nice regard to be smart in. Um, I guess if you’re, if you’re really concerned about maintenance, um, you could, if you wanted to, um, well, I mean, I guess, I guess you could delete duplicate system statistics, let SQL Server recreate any that it might need.

But, uh, if, if, but that would only really make sense on, like, gigantic tables where, you know, um, you know, updating those statistics with, uh, with, with, with, even, geez. The, the, if it’s, if it’s still really slow with the default sampling. Yeah, sure.

I guess. But, like, if you’re using full scanning, you’re like, well, it’s slow. You’re like, well, maybe, maybe if you’re going to be such a control freak that you need to do a full scan on everything, you should be picking specific statistics to do the full scan on rather than just saying, hey, store procedure, go find anything that’s been modified and update it.

Take a little control. Take a little more control, you freak. Take a little more control.

And maybe, maybe I’ll just take the weekend off. Wouldn’t that be nice? Me just hanging out.

So anyway, uh, thank you for watching. Um, I hope you enjoyed yourselves. I hope you learned something. And, um, I will see you in another video at another time.

Going Further

What’s The Point of 1 = (SELECT 1) In SQL Server Queries?

Posted on July 11, 2024January 22, 2026 by Erik Darling

What’s The Point of 1 = (SELECT 1) In SQL Server Queries?

Thanks for watching!

Video Summary

In this video, I delve into the age-old question of why one might see `1 = SELECT 1` in SQL Server queries, a topic that garners about 70 to 80 comments per week. I explain that this snippet is often used to avoid trivial plans, which can hide important optimizations or cause confusion due to simple parameterization kicking in. By incorporating `1 = SELECT 1`, we ensure the query optimizer makes full cost-based decisions, leading to potentially more optimized execution plans. The video walks through examples where `1 = SELECT 1` is crucial for demonstrating certain behaviors and clarifying complex queries, especially when presenting or debugging issues. I also discuss how this technique can help in maintaining clarity during demos and presentations by ensuring the exact query text used matches what was executed, avoiding misunderstandings and confusion among viewers.

Full Transcript

Erik Darling here, fresh from a full day of celebrating freedom, and back to work. For you, because, I don’t know, do I work for you? I might. I might work for some of you who watch. Maybe not enough. Maybe I should work for more of you who watch. That’d be nice. Then I could just do this all day, and I wouldn’t have to, like, do stuff over there on the computer you can’t see. So that’d be cool. I don’t know. Anyway, we’re gonna, in this video, I’m gonna answer a question that I answer 70 to 80 times a week. And it is, why do you have 1 equals select 1 in your queries? And the funny thing is that everyone who asked me that question asked me that in a comment. What amuses me, I suppose, is that if you were to type what’s the point of 1 equals select one into any search engine, even dumb Bing can find it. You would find my New York Times bestselling blog post called, what’s the point of 1 equals select 1 in SQL Server queries? And you would see, you would see, you know, the same thing.

nearly the same text instead of demos appearing in this SQL Server Management window in handy, easy-to-read blog format.

So in this video, I’m going to read my blog post to you, and hopefully you will watch it, and hopefully this will be available as a secondary resource for anyone who looks at one of my demos and is puzzled by the presence of 1 equals select 1.

So here we go. We are already using the correct database. I believe we have already dropped all of the indexes we can possibly drop, so we’re good there, right?

That’s excellent news. We’re in good shape, you and me. So the main reasons for using 1 equals select 1 in SQL Server queries is to avoid two things.

One is a trivial plan, because trivial plans can hide all sorts of, or preclude the inclusion, academics, of certain optimizations that you only get when the optimization level is full.

So that’s one good reason. And I often use it in my demo queries because I want to write the simplest possible demo query to show the behavior I want you to see is possible.

The trouble is that sometimes when the simplest query doesn’t work out, either because the trivial plan does not get me the optimization thing that I want, or people see the simple parameterization thing kick in and get very confused.

Like, is that forced parameterization? Like, what’s wrong with your database? Is it broken?

Why is that parameter there? And it’s kind of funny. In some ways, I think that writing slightly more complicated queries would be less distracting to the casual viewer than just putting 1 equals select 1 in there to do what I want.

The trouble with writing more complicated queries is they become more prone to failing. The demo gods are harsh gods.

They, I don’t know, they hate me sometimes. So, yeah, there we go. Anyway, so some examples of, you know, things like I’m talking about.

1 equals select 1. Important stuff. Things you should know. And I’m not suggesting that you should put in 1 equals select 1 in all of your queries, but if you’re writing demos or you’re just testing stuff out, it can kind of be a neat thing to see if it changes anything.

So, here’s an example where I’m going to run these two queries, and we’re going to look at these two execution plans. And, of course, as promised, this query up here is simple parameterized.

You might be able to tell by looking at some of this stuff and realizing Erik Darling is not a dork and does not put square brackets on around absolutely everything in his query. And Erik Darling is the kind of guy who properly uses as when aliasing.

Aliasing things. Asleucing things. So, this query is clearly not exactly the one that I wrote.

It’s also got a little parameter over here way at the end called at 1. Fascinating. Absolutely fascinating stuff.

You might be even more fascinated to learn that this is a trivial plan. All right. You can see the optimization level trivial here. And we can see kind of a strange thing with the parameter list where SQL Server inferred the data type of the number 2 as a tiny int. If we were to write queries with a number 1 higher than the max of tiny int, small int, int, and big int, we would see the data type change for the parameter of each one of these.

At some point with the int max and the big int max, it starts using weird decimal types, though. It doesn’t explicitly use big int.

Sorry. So, that’s one reason why. Right? So, we can see that SQL Server clearly does slightly more thoughtful optimization with the second query that has 1 equals select 1 on it because the second query, of course, SQL Server says, Hey, have you thought about adding an index to make this faster?

Now, you know, 188 milliseconds isn’t terribly slow. Fine. I know.

But sometimes it’s the thought that counts. You might also notice that this query is written much more in the style of Erik Darling, where we have a proper as, for our alien, as-lesy-fiziting, and we don’t have dorky square brackets around things that don’t need them.

Right? So, cool. SQL Server gave me my query back. Stop enforcing its stupid query formatting on my beautifully written and formatted query.

Bug off, SQL Server. Sought off, Swampy, as a wise man once said. So, what gets fully optimized?

Right? Aside from, like, you know, 1 equals select 1, all sorts of things get fully optimized, but generally they require SQL Server to have to make some sort of cost-based decision about what the cheapest way to do something is.

So, join, subqueries, aggregations, ordering without a supporting index, lots of stuff that, you know, where all of a sudden SQL Server has to do more than figure out, I just have to select some rows from one table where this column equals a thing.

Easy peasy. I don’t have to, there’s not a lot of cost-based decision making in that process, unless there are multiple indexes involved. Of course, your tables all have multiple indexes involved.

So, the likelihood of you needing to write 1 equals select 1 and, like, a production query are pretty low. So, let’s look at these two queries. Right?

We’re going to select the top 1,000 IDs grouped by ID, which, of course, is meaningless because ID is all, what do you call it, unique values. It is the clustered primary key of the table.

And the reputation column, of course, is very ununique. Very ununique as a column. And so, you know, these obviously return different results because we’re doing different things.

But this query right here, if we look at this, we are with a trivial plan once again because there is no cost-based decision to make. This one down here is not a trivial plan. This is a fully optimized plan.

And the reason this one is fully optimized is, of course, because SQL Server had to choose what to do in here. Right? This operator represents a cost-based decision. And this operator is why.

SQL Server was like, oh, I’m going to fully optimize this thing because I need to think about how to group this column. Am I going to use a stream aggregate? Am I going to use a regular hash match?

Do I want to use a partial aggregate first? And at the end, it shows a hash match flow distinct. Right? That was apparently the cheapest one. So, happy times there.

Happy, happy times. So, one reason, or it’s a good way to put this. One situation where using 1 equals select 1 isn’t necessary is if you have an index, if you have multiple indexes on a table.

Now, this index right here has absolutely nothing to do with this query. We’re not.

Like, this is just on creation date. And the rest of these bottom two queries have nothing to do with the column creation date. For the first two queries, it do have a lot to do with the column creation date. If we run these, we will see the first query.

Again, look at this ugly, awful, square bracket, dork formatting. And no as with the alias. Shame on you, SQL Server.

And the bottom query, of course, does. With the 1 equals select 1, this looks more like what I wrote. Right? We can see the literal for the date. We don’t have this thing get substituted with a parameter.

And so this is one of those things where 1 equals select 1 takes a little bit of the confusion out of either me zooming in, doing a video like this, presenting live, taking screenshots for a presentation. And when I zoom in and show the query text of a query, sometimes it’s kind of confusing when people don’t see the exact query that they just saw me run. And so a lot of times, just for clarity, it makes a lot more sense.

Even if I don’t include the 1 equals select 1 portion in the screenshot, it makes a lot more sense for me to take a screenshot of just this part so that you can see that it is actually the query that I was just talking about running with the literal values. If I told you I was running a query and then you saw this in the screenshot, you’d be like, where the hell did that come from? Is that in the store procedure now?

No, Eric, that looks nothing like the query you executed. Are you insane? Right? So there’s reasons, right? There’s reasons for these things.

Some of these reasons are presentation layer reasons. Others of them are truly query optimization reasons. And now, if we look at these two queries, now look, I agree that the second query is absolutely, absurdly ridiculous, right? There is absolutely no reason to ever use this index for the query that we’re running because it’s only on the creation date column.

But having this superfluous nonclustered index around actually makes, right? Because this is a valid plan choice, right? SQL Server would cost this choice and say, oh, maybe no.

But having that around is a reason why this top query, now to make things even kind of weirder. Here, this is where your noodle is really going to get baked because look what we have here, right? This looks like simple parameterization.

But over here, we have full optimization, right? So sometimes, even when you get full optimization for a query, sometimes you still need 1 equals select 1 to get rid of this ooky query text with the terrible dorky square brackets and the lack of an as in the alias. So 1 equals select 1 has some extra powers to it that even getting full optimization for a query doesn’t have for presentation stuff like this.

So that’s another good thing to keep in mind. Now, the other problem that you might run into with trivial plans, and this is something that I see a lot. So, like, you know, I think they used to be a lot more common.

I forget exactly. There were a few people who would always write articles comparing the query optimizer, the query optimizer’s abilities with, like, MySQL or Postgres and SQL Server or Oracle or DB2 or, like, you know, a whole bunch of different relational query engines. The problem is that, like, they may have had some specialty in, like, MySQL and or Postgres and or Oracle and or something else.

But they were pretty stupid about SQL Server. There were things that they didn’t know to look for and there were things that they just didn’t have the expertise in to, like, understand what, like, why things were different between certain engines. Now, granted, you probably shouldn’t need a very, very deep understanding to understand why SQL Server might look at a check constraint in one engine but then not do it in SQL Server.

But that was the case for a lot of things. And this is a pretty good example of that. So if I add this constraint to the users table, right, which just validates that every reputation in the users table is greater than or equal to one and less than or equal to two million because at this point in time, John Skeet still does not have two million reputations.

I forget what he’s up to. It’s been a while since I looked. Maybe I’ll check in after this video.

But then if I run these two queries and look at the execution plans, both of them return zero rows. And, of course, here’s where the demo gods have absolutely betrayed me because you know what I didn’t do? I didn’t drop this index on creation date.

So let’s remember to add that to the demo script next time. And let’s make sure that Erik Darling does 100 push-ups. Ah, my own petard.

There we go. That’s what I wanted. So this first query obviously scans the entire clustered index looking for where reputation equals this substituted parameter. Now, SQL Server needed a plan, right, since this is a trivial plan with simple parameterization.

SQL Server needed an execution plan that would be safe, that would be cacheably safe for any other execution of this query where it would maybe hit a rep. Maybe it would be looking for a reputation where, you know, what do you call it? Like it might exist in the table.

So, like, I’m searching for zero here, right? My search is for someone with a reputation of zero, and this query rightly does a constant scan because this query doesn’t get simple parameterization. This query doesn’t get a trivial plan.

And so SQL Server can logically detect that this query is not going to return any rows. We can just skip the whole thing. Again, with this query, of course, it can’t do that because of the parameter substitution over here. It has to say, well, if someone searches for reputation equals one or two or three or ten or five million next, we might need to actually look and see the return rows from the table.

We have to go figure that out. So if SQL Server wants to cache and reuse this plan, it can’t be the constant scan because the constant scan doesn’t touch the table, doesn’t return any rows, and blah, blah, blah, blah, blah, blah, blah, blah. Yeah. It’s a lot like how, well, I mean, not a lot like how, but it is reasonably close to sort of the neighborhood of why if you create a filtered index on, let’s say, creation date, and then like a stored procedure or in an entity, like an ORM query, you pass a parameter to search for creation date.

SQL Server can’t use that filtered index because, of course, you know, like it has to cache and reuse a parameterized plan where some parameter values might qualify to use a filtered index and some might not, right? So like it’s sort of similar to that where like the cached and reuse plan has to be safe for anyone, but the cached and reusable plan has to be safe for everyone. But, you know, a more specific, like, you know, more optimized for the literal value plan, like if you put option recompile or something on it, like that would be like a more, a plan that’s more specifically geared towards the query you’re running than a good, than a general plan that would work for any set of parameters.

So, I’m glad I got that off my chest, finally. It feels good. Feels real good. I feel like I stretched. I don’t know. I feel like I slept 18 hours. I’m just kidding. After my July 4th, it’s going to be a while before I feel like I slept 18 hours.

There was a lot of mezcal and brisket, which is a bit of an odd combo, but trust me on this one. They go well together. I am a fan. I am a newfound fan of mezcal and brisket in one mouth. All in the same mouth.

So, with that additionally off my chest, Eric’s cooking tips. Put mezcal and brisket in mouth. Mix, stir thoroughly.

Thank you for watching. I’m glad you made it to the end with me. I hope you enjoyed yourselves. I hope that you learned something. And of course, you know, as usual, if you like this sort of SQL Server content, please subscribe to my channel.

Join the nearly 3,824 other data darlings who get notified when I present these little bits of my love to you. When I show you my love.

If you like this video, comments, thumbs ups, things like that are nice. And as promised in my last video, I got a haircut. The whole thing.

Whole head. Granted, it’s looking a little dicey up there. I might do something about that. Because that’s a little bit much for a man of my incredibly young age. If I want to retain my beer gut magazine accolade of being the youngest and most handsome SQL Server consultant in the known universe, then I might want to put some serum on that.

Maybe grow a little bit of that hair back. Also, like, if I don’t do that, you know, maybe my hair guy will, you know, go out of business and starve and lose his house and his car.

That’d just be depressing. I got to keep getting haircuts to make the world keep going around. You know? That’s why we all do the things we do. Keep the ball spinning. So, anyway.

I’m going to go now. Thank you for watching. And I’ll see you in another video shortly. Thank you. Goodbye.

Going Further

Parameter Sniffing, Predicate Selectivity, And Index Key Column Order In SQL Server

Posted on July 10, 2024January 22, 2026 by Erik Darling

Parameter Sniffing, Predicate Selectivity, And Index Key Column Order In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into how predicate selectivity can significantly influence SQL Server’s execution plans and decision-making processes. We explore a variety of scenarios where different selective predicates lead to unexpected missing index requests and confusing parameter sniffing issues. By examining specific queries and their corresponding execution plans, I highlight the importance of understanding both predicate selectivity and indexing strategies in optimizing performance. The video also touches on the quirks of SQL Server’s missing index request feature, which often doesn’t consider column selectivity when suggesting indexes, leading to potentially suboptimal choices. Throughout the discussion, I provide practical examples and demonstrate how different indexing approaches can drastically affect query performance, making it crucial to carefully evaluate and manage your indexes based on real-world usage patterns rather than generic advice.

Full Transcript

Erik Darling here with Darling Data. In this video, we’re going to talk about how predicate selectivity can lead to a number of things. SQL Server might choose different execution plans based on it. You might get some inopportune missing index requests. Not because of predicate selectivity, but because the missing index request feature is garbage. And how it can also make index choice and parameter sniffing issues quite strange and confusing. So we’ve got a few things to cover here. So we might as well get started before anything weird happens. I don’t know, maybe an asteroid will hit my building. I don’t know. Times are, times are, times are strange, my friends. Times are strange. All right. So, we have, I’m going to do this just in case, because, you know, why not? Who knows what I may have forgotten to do before? My brain isn’t what it used to be. Local factors and such. So what I want to show you is first that I have some varying selective and non-selective predicates on these two columns in the POST table. So parent ID, less than parent ID, less than one, parent ID, greater than, well, that number, parent ID, score less than one and score greater than 19,000. And these all give slight, well, I mean, these ones give sort of similar counts, right? So about 6 million there, about 6.2 million there, 23 there, and three there. So some, so like some filters on parent ID are selective and some like filters on score are selective, others not so much.

When, when, when people give you sort of like stock, run of the mill indexing advice, and they say things like, oh, always put the most selective column first in the index. Well, sometimes that’s hard to do, because you search different columns differently. And even sometimes you might have equality predicates that match far, far different numbers of rows from one, from one to another. And so like, you know, what really, I just want you to understand that the sort of stock indexing advice stuff is, is just what it is. Stock, it’s not necessarily what you should, what you need to follow in every circumstance. So with that out of the way, let’s look at a stupid missing index request. And the reason it’s stupid is because the optimizer is not very helpful when it comes to deciding on key column order for missing indexes.

So if we look at these two queries, we can see that we have mixed and matched, right? Actually, let’s go, but go to the results because I named all these columns, the name of these results very helpfully. Non-selective parent ID with a selective score, we still returned one row. And then a selective parent ID with a non-selective score where we still returned one, well, I mean, obviously one row, but a count of one. So we filtered this down to one single row with those predicates. But SQL Server asks for the exact same index for both of them, right?

One is on parent ID comma score, and the other is on the exact same thing, parent ID comma score, regardless of how selective or non-selective these predicates are. Now, there is a very good Q&A with a fellow, well, I don’t know. I mean, I met him a few times. I don’t know. I don’t really know how to define that relationship. I’ve met him a few times in person. I haven’t seen him or heard from him in forever. A guy named Brian Reebok, like the sneaker Reebok, but with one E instead of two.

I don’t know. Maybe he’s a secret heir to the Reebok fortune and some uncle died and he’s like, well, screw SQL Server. I don’t know. I don’t know what happened. But yeah, so there’s a Q&A on Stack Exchange where he asks a pretty good question. And what we find out from Stack Exchange is that basically SQL Server chooses the order of columns in the missing index request by the column’s ordinal position in the table.

All right. So it’s all written out for you here, so I don’t have to go repeat anything. But that’s the basic gist of it. So because the post ID column, sorry, the parent ID column is like further up in the list in the tables definition, like the create table definition, the ordinal position of the column is first. SQL Server is just like parent ID, you first, no matter what.

It doesn’t think about things any further than that. I mean, that does separate equality and inequality predicates into different things, but within each of those bunches, it’s really just column ordinal position in the table that dictates things, not column selectivity or any further thought or assessment from the missing index request feature. So just to prove things out a little bit, because that’s what I like doing here.

I like to make sure that you get plenty of proven pudding from me. We’re going to create two different indexes. I apologize for leaving these highlighted, but one is on parent ID, then score, and the other one is on score, then parent ID.

All right, so stick with me on this, because this is all leading up to something very useful knowledge for you. Smart things you’ll be able to take immediately to your job for the rest of today before you’re missing some fingers tomorrow, because it’s the 4th of July. All right, so let’s run these four queries.

And what I’m going to do with these four queries is I’m going to execute them, and what I want to show you here is that for the first two queries, I am letting SQL Server choose the best index that it possibly can. Right? We have no hinting on these things.

SQL Server is free to choose whatever it wants for an index. On the second two queries, I am telling SQL Server which index to use, and I am obviously, I’m playing favorites here. I’m maybe spoiling the results a little bit.

I’m sorry about that. I often do that for clarity here. But if we look at these four queries, they all return the same number one. But if we look at the query plans, the two where SQL Server got to choose on its own used two different indexes.

This one used tabs. This one used spaces. And they both finished very quickly.

All right? Look at all those zeros. Goose eggs across the board. Couldn’t possibly be faster than that. Nolan Ryan would be jealous of all those zeros. And then for the two queries down here where I told SQL Server which index to use.

Right? I chose backwards. Right? These are backwards index usage up here. These both, I mean, it’s not disastrously slow in this case, but it is noticeably slower.

Right? These are about half a second where these are zero seconds. And if, you know, you’re talking about queries that execute quite a bit, you know, or like, you know, the schools of thought when it comes to looking at looking for queries to tune is, you know, you can look at what uses the most total CPU or has the most total duration. Or you could look at what uses the most average CPU or what has the highest average duration.

And you could kind of go from there and try and start to figure out, like, okay, like, what do I want to go after? Now, if you go by total CPU or duration, what you’re going to find is generally queries like this where, you know, like, they may execute the most and use the most CPU or have the highest duration in total. But every individual execution is pretty fast.

These ones are tougher to tune generally. Right? Like, this one wouldn’t be tougher to tune. You just, you need a better index for that. You switch the index order.

One would be zero seconds or, I don’t know. Maybe you whack the person who put the wrong index hint on these queries over the head and then delete the index hint. They’d be fine.

But, like, these are the kind of queries where, like, you know, you might see server CPU usage drop pretty significantly. If you have, like, something that executes hundreds or thousands of times a second and you bring it from 500 milliseconds to zero milliseconds, that could bring resource utilization on the server down pretty well. Now, like, you know, counter that, if you go by, like, average duration or average CPU and you, you know, start tuning those queries, then you see, like, you know, like big chunks of CPU come down because you have these queries that used to run for, like, 30, 40 seconds or longer or probably sometimes much longer.

And, like, data’s gobbled CPU the whole time. And, like, you’re no longer doing that. Right?

Maybe you got a parallel query to a fast single-threaded plan or just a much faster parallel plan or something like that. Just as an example, today working with a client, you know, we had a query that was running for a minute and 20 seconds. And after a little tinkering and forcing the use of the legacy cardinality estimator, it went from, like, a minute and 20 seconds to eight seconds.

Right? So it was a, you know, handsome use of a temp table and the right cardinality estimation model. This thing was flying.

And so, like, for that query, you know, a minute and 20 seconds of just this thing chugging along, eating CPU up, we no longer had that. It was down to eight seconds, so we no longer had those sustained bursts of CPU getting chewed up. So there’s all sorts of different ways to approach that.

And sometimes there is some glory in tuning these queries that don’t run for, like, hours or minutes or something because they might run a ton. And you might be able to have, like, a nice, like, you might be able to kill some of the mosquitoes in that swarm by tuning those up. So what we care about in this one is more along the lines of this, where index choice and predicate selectivity can make parameter sniffing issues a little bit more difficult to sort of figure out.

And what’s interesting here, to me anyway, is that I see indexes like this a lot. And when I see indexes like this a lot and I start trying to talk to people about, like, okay, well, you know, these both pretty useful to queries. Like, you know, even, like, you look at the index usage stuff, you know, like, they both might get used, but you don’t know if they’re being used well.

Right? All you can see is that queries choose them. You don’t know why they choose them or what they do with them. So you need to be a little bit careful with how you choose to either keep or remove or merge these indexes in together.

Because until you see the queries that hit them and, you know, understand how those indexes get used by those queries and if the usage is good or not, well, that’s, you know, there’s a lot to figure out. Right? So we have this store procedure here.

And this store procedure only takes one single parameter on a column called score. And what we’re going to do is look at two sort of different executions of this thing. Right?

We have one where we use a very selective score and one where we use a very non-selective score. Now, the store procedure itself isn’t doing anything all that interesting on its own. We are selecting the top 5,000 ordered by reputation descending from post joined to users.

And we’ve got some columns in there. And I don’t know. I mean, this calculation isn’t really doing much of anything weird or interesting or even particularly useful. But we’re looking for post types of two.

And we’re just filtering on score out here. So that is about it for what the procedure does. But you may notice that we are still executing these two.

And, well, we’re not actually finished executing this one. We have not finished executing this one yet. But we just did.

So lucky me. I was able to talk my way through that. So let’s look at what happened. And SQL Server chose this execution plan. This is a serial nested loops plan.

And what SQL Server chose to do was start with the post table, seek into there, find some rows that we care about, do a key lookup, and then join to the users table over here. And that worked out pretty well for when we were looking for a very selective score. So, right, typical parameter sniffing thing, this is a good plan for something that’s very selective.

This is not a very good plan for something that’s not very selective. The times change pretty drastically in here. We spend 1.8 seconds seeking into this table, way longer than before.

We spend 18 seconds in this key lookup. We spend almost 8.5 seconds in this seek. And we just spill some in the top end sort.

And that’s a pretty rough gig. Right? Now, this might be an example of when you have bad parameter sensitivity. But you might not always hit that bad parameter sensitivity.

So, let’s say that sometimes you run for the big value first and you get execution plans that are generally fast for everyone. Now, these execution plans look different in many ways. The order of joins is different.

This is a parallel nested loops join. There’s a sort really early on. And we still have the top over on the far side of the plan over here. But we have a sort right here now that we didn’t have before.

So, there’s a lot different between these two plans. And if we come and look at those, and we’re going to run these both with the recompile hint on them so we don’t parameter sniff anything, we can sort of start to parse these differences out.

And this is just another thing that, you know, parameter sensitivity and, you know, having indexes that, you know, we don’t know where they came from, what their lineage is, why they’re there, why they exist, what queries they get used by, how they help. This is just sort of a good example of how that can make parameter sniffing problems weird and confusing.

Because you might get, you might see this query run and it takes 52 milliseconds. And you’re like, wow, that’s great. That’s awesome.

It’s not awesome sometimes when a bigger value needs to get passed in. And then you might see this running. You might be, well, 206 milliseconds. That’s not bad either.

I’m not going to kick that one out of bed for eating crackers or for going parallel, I guess. And so, like, there’s just a lot to think about in here. So this one, of course, uses a different index, right? So this one uses the smooth index.

This one uses the chunky index. And we just have sort of different things going on in these plans. But this is how, you know, how selective your predicates are and how good your indexing job is. These are things that can make parameter sniffing problems way easier if you’re doing a good job or way harder if you’re not doing a good job to troubleshoot.

And, you know, granted, there are all sorts of cool ways with, like, query store and plan guides to force plans for things. But it’s all about you making sure. It’s, like, you know, I would much rather see you, you know, if we dropped this smooth index because we’re like, you know what, this index is just not maybe doing us any favors in general.

You know, it’s being used but maybe not being used well because, like, you know, when we have parameter sniffing on this, it’s pretty ugly. But I’d much rather see you start to, you know, clean up those indexes, make sure that the indexes you have are the indexes that you need. I don’t have numbers for that sort of thing.

I don’t have, like, a number of indexes or a number of columns that I care about. I’m more of a quality over quantity guy. If your indexes are good and queries are fast and everyone’s happy, then maybe you’re doing a good job there.

Maybe you’re all right. But anyway, I don’t know. I started to ramble a bit there.

I apologize. Got off script a little. Blame it on an empty tummy. All right. I’m going to go do some stuff now that’s more like working. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this video, thumbs ups and what do you call them? What do you call those? Positive comments.

Positive comments are appreciated. If you like this sort of SQL Server content, you can subscribe to my channel and you can join nearly… Hold on.

We have to get the updated count here. Nearly 3,814 other data darlings who subscribe to this channel. So you can get notified every time I post one of these videos and the first 15 minutes are pretty good and the last two minutes are a little bit of a ramble. I am aware of these things.

Anyway, thank you for watching.

Going Further

A Little About Parallel Exchange Spills In SQL Server

Posted on July 9, 2024January 22, 2026 by Erik Darling

A Little About Parallel Exchange Spills In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into exchange spills in SQL Server, specifically focusing on parallel exchange operator spills. After addressing a question from one of my viewers about how to fix these spills, I explain that the solution depends heavily on understanding why they occur—whether due to poor cardinality estimates, overly complex queries, or missing indexes. I then analyze a query plan with all three types of parallel exchange operators spilling data, highlighting the issues and potential fixes such as using hash joins or setting the query to run at a lower degree of parallelism (dop1). The video also covers weight stats, emphasizing their importance in diagnosing performance issues related to spills.

Full Transcript

Erik Darling here with Darling Data. Another day in paradise. Today’s video we’re going to round out. I’m going to roundhouse kick. If I had the office space, I would roundhouse kick, but I don’t have the office space for roundhouse kicks. I would knock over my camera and then we wouldn’t get to talk to each other anymore. It’d be sad. It’d be so sad. We’re going to round out our series of videos. I’m going to round out our series of videos on spills in SQL Server by talking about exchange spills or parallel exchange operator spills if you want. If you want to say, add a few extra words on that. You can see the query plan over here for it. Now I did get a question on another spills video. It was actually a fair question. It was, how do you fix spills? Well, the answer to that is a little bit longer than just… …than it would seem, isn’t it? So, I mean, first, you know, you want to make sure that you are getting the actual execution plan and that you are making sure that you…the spill that is occurring in the plan is running for an amount of time where if you fix it, people will say, wow, that’s much faster. Thank you. You’re the best.

So those are the first two things. And then how you fix it, of course, does depend on why it’s happening, right? There are all sorts of reasons why spills might happen. Probably most common, you might have just a really poor cardinality estimate. And that can happen because your query is very complicated or because you have done something that intentionally stifles SQL Server’s ability to make a good cardinality estimate. Local variables, table variables, things like that. You know, you could have an overly complex query because you stacked all those super readable CTE together and screwed the whole joint up.

You also might have a very obviously missing index somewhere. I don’t know. But, you know, how you fix them really does depend on, like, what got you into that situation. You know, it’s like fixing anything else. You want to make sure that you understand how you got there and what needs fixing and why it needs fixing. It’s pretty important stuff, right? Otherwise, I don’t know. You’re layering everything with duct tape and hoping that it stays together, much like my life.

Anyway, we’re going to look at this parallel spill plan. Now, I want you to just, you know, take a moment to ponder the majesty of this magnificent pagan beast. Look at this thing. This is the trifecta, right?

Because I have spills on all three types of parallel exchange. There is a gather streams that spills right there. There is a repartition streams that spills right there.

And there is a distribute streams that spills right there. That is all of the available parallel exchange operators in SQL Server. And they have all spilled for me because I’m pretty good at writing demos.

Now, if you have been watching my videos for any length of time, you will have heard me say how much I hate parallel merge joins. And this is why I hate parallel merge joins. All right. They are awful when it comes to this stuff.

And the reason why they’re awful is because parallel merge joins, or rather merge joins in general, much like stream aggregates, expect ordered input. Input has to be sorted so that everything can be done in a nice orderly fashion and flow right through all the merging and the blah, blah, blah, blah, blah. And so what you end up with is not necessarily – there’s no sort in this query plan.

There’s like no explicit sorting, right? Nothing in this query plan says sort or sort distinct or anything like that. But there are things that preserve order in this query plan that are a little tricky to spot.

So not this one, but if you look at this parallel gather streams operator, it has an order by at the bottom of it. And this order by means that it is preserving the order of the user – the ID column in the user’s table as it passes through here. Preserving order across parallel threads is what leads to things getting all gummed up and slow.

So even if this query weren’t spilling out to disk or the exchange buffers weren’t spilling out to disk, I would still be pretty concerned that – like when I see this stuff, especially if it has to deal with a lot of rows. Because you’re dealing with a lot of rows and if there’s any skew or if there’s any – just anything weird about the way rows are arranged across threads, that – those intra-thread dependencies, keeping those – keeping all those rows in order across exchanges and buffers and threads and all that stuff gets really, really nasty.

So that affects this one, right? This one only has – this one does not have an order by, but it does have a partition column right here. Now, this is the first one that spills.

And you can see operator used tempdb to spill data during execution with spill level 0 and one spilled thread. What is spill level 0? Weird, right?

What is one spilled thread? I don’t know. Which thread spilled? Couldn’t tell you. It’s all a mystery to me. But then these two other – these two other parallel operators, these have much bigger problems. So this one only runs for about 15 seconds because you’ve got 17 there.

Well, 14 and a half. 2.5 there, 17 there. So about 14 and a half seconds in this one. These ones have it a lot worse. This one – well, let’s see.

Why did you change colors? No, you don’t change colors on me. This one is really – I mean, assuming that all of these times are honest and that nothing is screwy about the operator timing code and SQL Server, and we all know what it is, then this one only runs about 8 seconds, right?

Because we’ve got 124 here and 132 there. This one here is really the problem, right? That’s like a full minute and 12 seconds.

Most of the effort is between these two. This one only gets it a little bit, but we still spill on this one right there because I’m good at writing demos. Something.

So this one does have an order by, right? This is the especially long-running one. Oops. I did not frame that correctly. We have an order by at the bottom here doing the same thing, keeping the users.id column in order. And then we have our merge join here.

And then we have our gather streams here, which also has an order by. And so I do want to recount some of the spill level stuff. This is spill level 0 and 6 spilled threads, so 6 out of 8.

And this one is spill level 0 and 8 spilled threads, right? So all of these order-preserving operators and order-requiring operators like these merge joins, these are all why I hate parallel merge joins because it makes queries very susceptible to issues like this.

All right? So the sort of unfortunate thing is that when you run across this stuff, there are certainly ways to fix it. And they range from just adding a trivial hint like option hash join.

So hash joins, they don’t require anything to be in order. And they’re sort of like the unordered equivalent of a merge join in that they support two reasonable-sized inputs and big scans of stuff, right? So that would be like I would try a hash join there, option hash join.

If the hash join wasn’t giving me what I wanted, I might even try just setting this query to run at dop1 because dop1 might still be slow, but it’s probably not going to be slower than a query that spills on every single exchange operator because that’s pretty painful, right? It might also be a case where, you know, you might want to add some indexes that do not keep your join keys in primary sorting order.

So that you might, like this, like if you can’t add hints, sometimes if you add indexes that would in other circumstances be considered suboptimal, you could get a hash join plan naturally because SQL Server wouldn’t have nice ordered input for things as it is. So like when we’re looking at this query specifically, I have indexes on the users table, nonclustered indexes on the users table and the post table, and these both lead with the column that’s being joined on, right?

So this one leads with the ID column. This one leads with the owner user ID column. We merge join those here. Those inputs are already sorted, so the merge join works without having to sort. And then up here, we’re just using the clustered index of the users table, and the clustered index of the primary clustered index of the users table is also on the ID column.

So we just get, like, all this stuff is ordered in SQL Server. It’s like, woohoo, merge joins everywhere. And that sucks. One other thing that’s important to sort of pick out here is what the weight stats look like.

So what I am not doing, I’m not looking at the query level weight stats here, because the query level weight stats here are going to be disappointed, because our dear friend Sam, that’s someone at Microsoft, decided that some of these weights that are important would not be important to show you, right?

So when we look through these, we quickly exhaust any useful amount of waiting time on these. Milliseconds down here, tiny milliseconds. The only thing that we have that looks meaningful at all to me, anyway, is CX packet, which we have a whole bunch of milliseconds of, right?

Look at all those CX packets flying around. Let’s see, 1, 2, 6, 5, 8, 5, 7. That is a seven-digit number of milliseconds in CX packet. So we are eight threads CX packeting around, right?

Doing all sorts of crazy CX packet stuff. But the weight stats for the actual weight stats, the session-level weight stats for the query itself, have a lot more interesting stuff to tell us, right?

So when we look at the session-level weight stats, we have some more interesting things in here. We see our dear friend CX consumer. Look at all the CX consumer time we had in there, right?

That’s a lot of time. And the max wait time on both of these is quite nauseating. And, I mean, if you want to talk about something else that’s kind of interesting, the signal weight time on CX consumer and sleep task is also quite interesting for these parallel spills.

But sleep task is another one. We’ve seen this with the hash spills. With the exchange spills, we also see a lot of sleep task weight time pileups.

About 15 seconds of sleep task weight time in total. And almost 14 seconds of that 15 seconds is signal weight time. So waiting for CPUs to say, yes, you can do something now.

And that’s also very interesting with the CX consumer weights. That’s nearly 29 seconds of signal weight time out of the, let’s see, what is that? Oh, sorry, I messed that all up.

Let’s see, 452959. It’s a six-digit number. So that’s 452 seconds of weight time on CX consumer. The query plan doesn’t show you.

Someone at Microsoft, man, why? And like 30 seconds of that 452 seconds was just waiting on CPU signal. It’s just bonkers.

The CX packet doesn’t have that signal weight time thing up there, but it just has a whole lot of actual wait time. And so more signs that your queries might be having problems. So we talked about, over the course of these videos, we talked about sort spills, hash spills, and now exchange spills.

Hash spills in row mode will show you a lot of the sleep task weight. Apparently, exchange spills will too. Apparently, there’s a lot of sleep tasks involved with exchange spills.

The thing is that the sleep task weight, like I’ve said before, is associated with a lot of other stuff. So you can’t necessarily look at a server and say, oh, sleep task bad. But if you’re looking at individual queries and you’re seeing this weight crop up a lot, maybe you’re just running who is active and you’re seeing lots of sleep task weights or something, or IO completion if it’s a sort spill.

That might be a pretty good sign that whatever is happening in there, unless you’re getting an actual execution plan, you’re not going to be able to see what operators are spilling in a query. So you might have to run SP who is active, get plans, look at whatever plan SQL Server has running for that query, and then assume that one operator that requires memory, like a sort or a hash or an exchange buffer, is spilling. And then that’s what the either sleep task weight or the IO completion weight is on about.

If it’s batch mode, you’ll see the BP sort weight. If it’s batch mode hash something, you’ll see a whole bunch of these different HT, memo, delete, repartition, all sorts of crazy HT weights in there. So just stuff to keep an eye on.

Like, you know, when you’re looking at queries running, the weight stats aren’t always going to tell you exactly what’s wrong. But just, like, being able to understand that sometimes they can and what to look for in the query plan based on the weight stats that you see is pretty important. You know, it’s sort of like what I’ve talked about in videos about eager index spools where you see exec sync weights pile up in a parallel execution plan while an eager index pool is being built.

Like, if you’re looking at queries running and you see that exec sync weight in the same way that you might see any of these weights up here, up at the top here. Like, if you see those piling up, you know, just knowing where to look in the query plan based on the high weights you’re seeing is a really valuable thing for performance tuners. So at least something to, like, you know, take away from this video is, like, all the videos that I’ve done on spools so far is just, like, either looking at, you know, weight stats as a whole for a server or looking at queries that are currently running with SP who is active or, like, looking at the weight stats at the query level, you know, anything you’re doing there.

Like, knowing what the weight stats that you’re seeing can mean in the query plan as far as the bottlenecks goes, really, really good stuff for you to know. So, with that, I’m going to jump out a window. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content and you would like to express your undying gratitude to me for publishing it, I do enjoy thumbs-ups and I do enjoy helpful commentary. Not hurtful commentary.

I like helpful commentary. That’s the best kind. If you like this sort of SQL Server content in general and assuming I survive my jump out the window, you can subscribe to my channel and you can get notified along with, let me drumroll, please, as I update my subscriber numbers here, nearly 3,809 other data darlings. You can join them unanimously and with perfect synchronization getting notified when I post these videos.

So, that’s about it. What was I going to say? Oh, yeah, nothing.

Thank you for watching and that’s all for today. Okay. Sticking the landing on this one pretty good. All right. Cheers. Some seltzer for your troubles. All right.

Thank you.

Going Further

Join Me At Data Saturday Dallas Sept 6-7

Posted on July 8, 2024June 26, 2024 by Erik Darling

Spring Training

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Going Further

A Little About Hash Join Spills And Bailouts In SQL Server

Posted on July 5, 2024January 22, 2026 by Erik Darling

A Little About Hash Join Spills And Bailouts In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the complexities of hash join spills in SQL Server, providing a detailed walkthrough of how these operations can lead to significant performance issues. We start by examining a query that selects from a table containing large text data, which quickly demonstrates the severe impact of hash joins on system resources when dealing with large datasets. Through extended events and a series of tests, I illustrate how even minimal data volumes can trigger extensive recursion and bailouts, leading to prolonged execution times that are both frustrating and time-consuming to observe. The video also touches on batch mode and row mode operations, highlighting the consistent unpleasantness they bring to query performance during hash join spills.

Full Transcript

Erik Darling here with Darling Data. Still, uh, feels like a perpetual situation. I’m not sure, I’m not sure that this can ever be resolved. At least not peaceably. Anyway, in today’s video, we’re going to talk about hash join spills. Because, oh, I don’t know, those seem pretty important. Because, uh, much like hash aggregate spills, if these really start piling up, they can ruin your day. Not in a good way. Not like, uh, if your car breaks down in front of a bar, and you’re like, I’ll just go inside and call AAA from the payphone. And you end up having a great day inside the bar. Because you’re like, well, I don’t have any quarters. And the bartender points to a sign that’s like, no change. With a payphone. And so you have to buy a beer that costs, 75 cents to get a quarter to call the, to call the tow truck company. Then you just decide to hang around for a while.

That’s my kind of day. Anyway, uh, yeah, hash join spills. Sorry, I was, I was, I got a little carried away there. I actually got, I actually got lost in that moment in my head. I was like, because I can, I can, like, picture the bar and the bartender. Did something for me. Did, did something special for me. So, uh, hash join memory grants. There is a lot to say about them. And thankfully, uh, I don’t have to say all this. Uh, there’s a link. Well, my, my hand goes away where I want to point. But, uh, there’s a link up in the, that I’m going to put in the, in the show notes, as it were. Uh, written by, uh, Craig Friedman, who actually played the part of the bartender in that, in that scenario that we just talked about.

Uh, where he will, he will tell you in great, well, I don’t know. Is that great detail? It’s pretty good detail. How memory grants for hash joins, uh, are calculated by SQL Server. And, um, this is, and what happens when they spill. So, there’s all this great stuff that you will learn from Craig. Uh, I’m not going to repeat all this stuff because that would be weird plagiarism. I’m just going to tell you that it exists here. If you want to pause and read it and like, like, like, like type in the URL from there, but just, just know that my, my source is cited. All right. I’m not, I’m not claiming that this green text is mine.

Definitely not. Definitely would never write all that stuff. So, uh, like I promised in the video about hash aggregates, uh, we are going to use an extended event, which is over here. And that is going to show us, uh, when we hit hash warnings, uh, in, uh, with, with the hash operator that spills out and starts, and starts going through different levels of recursion and then hits a bailout point.

And the bailout point is, uh, when the hash join switches over to some, uh, naive kind of nested loops join. Uh, so that’s, it’s not, it’s not a, not a fun time. I promise you.

So what I’ve done is I pre-run four queries and, uh, I’m going to, I’m going to show you what the queries are because they, they relate to what is up here. Where like the size of the data that is going through all the hashing stuff has a big part to do with how much of a memory grant the, the hash join queries need. So I’ve got two queries here.

Uh, they both do just about the same thing. They join from votes to comments, but they join on some really low selectivity columns. Right.

The post ID column and the votes table and the post ID column in the comments table are like, there’s only eight possible like numbers in there. So there’s a lot of matches in there. These are not unique columns where there’s like very few buckets of matches.

Right. And some of the buckets of stuff are going to be way bigger than other buckets of stuff because there’s way more of certain post types than others. That’s something that we’ve looked at a million times in these videos.

So, uh, I ran these two up here without any memory grant hints on them. Right. So these ones get the full memory grant that they want to run. And then there are two down below that are, that are capped.

It’s essentially the same two queries in the same order, just with caps on them. Now, if you remember from the hash aggregate video, the, the, I mean, aside from the row count and aside from the intent of the table, uh, the, the main, like the focal point difference, the, the crucial difference between the votes table and the comments table is that the votes table has like five or six integer columns in a date time column.

And the comments table has like four or five integer columns, a date time column, and then an envarchar 700 text column, string column. So the, and the string texty columns inflate memory grant needs way higher because of the way SQL Server estimates the column fullness. And because it’s a string and strings are a mistake and you shouldn’t put strings in databases.

It just screws everything up. So I’ve got these four queries already run and we’re going to examine the query plans just a little bit. So this first one where we select just from votes takes 7.7 seconds.

The one where we select from comments, right? We see the C dot star here and the V dot star here indicating the, the alias of the table we selected from. Okay.

Got that. And, uh, the, the one that selects from comments does take a couple seconds longer. Um, you know, not real, really any real reason other than like the chunk, the chunkiness of the chunkiness of the data. Right.

So, uh, we can see that happening sort of all throughout the plan, uh, where, you know, the stuff takes longer. Right. And, um, if we look at the memory grants for these, the one that just selects the columns from the votes table gets about a 4.5 gig memory grant, almost 4.6 there. And the one that selects from the comments table gets nearly a 10 gig memory grant.

Right. 9,855 megabytes. It’s about 9.85 gigabyte. Uh, yeah.

Almost 10 gigs. Yeah. 9.8 gigs. Yeah. Close enough. There we go. Math. I can do that. Sometimes. Sometimes I remember things. So, obviously, uh, like SQL Server’s memory grants here, nothing spills from either of these. The hash joins are fine here, which means that, and then also another good thing to point out is that there are no warnings on the selects.

So, sometimes if SQL Server, um, is like detects after query execution that a memory grant was either too big, way too big or way too small, uh, it’ll throw up a warning on the select operator. And it’ll tell you that, like, the memory grant was too big or too small, and if you have some sort of, you know, um, memory grant feedback mechanism in place, it’ll start adjusting that. If not, it’ll just twiddle its thumbs and stare at you and be like, hmm, guess you should have paid for Enterprise Edition, hmm?

Hmm. Oh, you’re not using the newest compatibility level? Hmm. Weird. Weird for you.

Yeah. Oh, that’s too bad. Hmm. Hmm. Yeah, I’m just, I’m, I’ll be over here if you need me. All these, all these features and capabilities, duh, you’re not in the right compat level, or you didn’t pay $7,000 a course, so I’m just gonna hang on over here and wait for you. Someday you’ll get there.

Real, real helpful, real nice, real cool. Psyched on that. So, uh, obviously, stifling the query that selects from the comments table is gonna hurt way more from a memory grant perspective than stifling the comment, cycling the query that hits from the votes table, because that string column in the comments table is gonna really whomp things up.

So, if we scroll down and look at what happens to the two sort of nerf-balled queries, uh, SQL Server begs for an index on this one, right? It has not begged for an index previously.

And, uh, if you look at the hash join operations, uh, this one spills for, uh, this query, a whole thing spills for, like, nearly a minute. But the one where we select from the comments table spills for nearly four, over four minutes. Nearly four minutes and fifteen seconds.

Nearly. One second off. Uh, and if we look at the, uh, spill levels on these, uh, this one spilled to level three, and, of course, all eight threads spilled. And keep this number in mind, 663-800, right?

So, uh, that’s how many pages got spilled. Now, if you were to look at, uh, like, the hash warning thing for these queries, the level, uh, the spill level would match the recursion level that SQL Server notes for, uh, the, for, in the hash warning thing. Um, and then, if we look at this one, this is also, oops, oops, that, this thing keeps reframing, and that, that messed me up a little bit.

This one spilled to level four, and with eight, of course, still with eight, all eight threads spilling, but that’s way more pages, right? The last one was, like, 663,000. That’s 3, 1, 1, 9, 4, 8, 8.

That’s a seven-digit number. I only have these fingers left. So, that’s 3.1 million pages. So, that’s pretty tough there, right? We spilled a lot more because that text data takes up way more space on the pages.

You need way more pages to hold on to it. So, this is obviously not a very good situation, but, uh, none of these, neither of these queries, even when we nerf them down to, um, 0.1 max grant percent hit, do we hit the hash bailout. Now, the first thing I want to show you is that hash bailout is not just for hash joins.

So, you may remember this query from, that runs for about 30 seconds from the video about hash aggregates. We’re going to run this again, and we’re going to watch the extended event that I have over here. And, uh, about 5, 10 seconds in, this will start showing stuff.

And, uh, we’ll see it go through the different levels of recursion and then the bailout. There we go. There’s recursion one.

Eh, no, this thing runs for 30 seconds. And then you have to wait for extended events to, like, you know, get its act together and put the stuff in there. Ah, there’s two.

And now it finished. Hey, look at that. All right. So, what happens in here? Let’s, let’s open this up and let’s take a slightly closer look. There we go.

So, here are our threads. And here, well, we only, let’s do the top one, so there’s only one. And, uh, I mean, kind of awkward, isn’t it, right? Uh, we have a bailout and then another recursion. And then, well, some recursions here and a bailout and then, uh, then some more bailouts.

So, uh, I, I, I don’t know. Maybe it showed up out of order or maybe, maybe things are just weird. Uh, but anyway, uh, you can totally get bailout and recur, recur, recursion and then bailout with just a hash aggregate.

You don’t need a hash join for it. But now, let’s behold the real majesty here. Oh, not that one.

This one. This, this one, this one’s some real good majesty. So, we’re going to take our, our really crappy query that selects from the comments table. Right? And we’re going to run this one.

And I’ve, I cleared out the data in here. So, there’s nothing in there anymore. And if we run this, uh, this thing will, uh, almost immediately, uh, start recursing and recursioning and bailing and outing. Uh, it does not take much for, for this one to kick in.

Uh, now, uh, I had, I’m going to come back to that one in a second. Now, I had run this same query with the, uh, with batch mode going for it. Um, this one fared okay.

Uh, you know, the, the weight stats. Uh, there’s one kind of new one in here. Um, so, when we saw the, the hash problems, uh, with, um, just the hash aggregates, I believe it was HT build and HT delete that were way up top. Uh, went with the hash join.

We’re starting to see a lot more HT memo and we’re starting to see this HT repartition weight. Uh, sleep task is still a big deal for, um, for both the row mode and the batch mode hash join spills. So, the sleep task is still a big part of that and it’s still not in the query plan XML for either one of those.

So, just something that you should be aware of there. The sleep task thing is still a fact. The sleep task weight type is still a factor there.

But now, let’s come back and look at this. And we can see that, uh, this query has been bailing out for quite a while now. So, uh, we hit some recursion and then we just started bailing and bailing and bailing and bailing and bailing.

This query will run for a very long time. This query will run for longer than I care to stand here. This query will run for longer than you would care to watch.

Um, it, it, it would, it would be awful. So, uh, we’re not going to watch this thing finish because it takes too long to finish. Um, there’s, there’s.

Probably a good joke in there that I’m not going to make. But, uh, if we look at all this data, we can see all of the bailing out happening across all of these threads over and over and over again. And, uh, we have just hit, we have hit a point where we, we no longer care to live.

So, uh, I don’t know. That, the, those, that’s what happens during hash joins or hash join spills. Um, you know, the, the weight stats are the same as, uh, as they are for hash aggregate spills.

And, um, yeah, they’re unpleasant. Uh, batch mode, still not good. Row mode, no fun at all. Uh, and again, probably way more worth, uh, paying attention to, uh, hash, different hash spills than different sort spills.

Unless the sort spills are in, uh, batch mode. Um, next video, uh, we’re gonna, we’re gonna look at exchange spills. Which are when parallel, uh, exchange operators, uh, run out of memory buffer space.

And begin spilling all over the place. Like, often, awful drunken bar patrons who swore they just needed to use the payphone 57 beers ago. They haven’t left.

I don’t know. Anyway. Uh, maybe, maybe someday that’ll be me. If I ever, if I ever build a time machine and go back to, like, 1981. That’ll, that’ll be my plan.

Gosh, the car broke down. I’ve, I’ve got all these bills from the, from the year 2030. No?

Alright. Whatever. Uh, okay. Um, yeah. Uh, thank you for watching. I hope you learned something. Uh, if you invented time machine, please take me with you. Um, what was I gonna say?

Uh, hope you enjoyed yourselves. I would enjoy myself if you made a time machine and took me with you. Um, that was, that was, that’s really the crux of this whole thing. Um, uh, if you like this video, for some reason, if you like learning about how bad SQL Server can be at things, uh, feel free to give me a cordial thumb up.

Or a cordial comment. I like those. Feel good about those. They really make my day. They brighten my whole mood.

Uh, and if you enjoy this sort of SQL Server content, uh, you should hit the subscribe button. Because we’re, we’re, we’re getting awfully close to 4,000 here. Which would, uh, I think break, break, uh, break my, uh, tie.

Or break my current sort of standing with, uh, Amiga repair channels. So, we’re, we’re gonna get up there. We’re gonna, we’re gonna break through to a new level of SQL Server fandom.

You and me. All together, my, my data darlings up there in the world. So, uh, yes, you should, you should like, you should, you should subscribe. You should, you should cordial comment.

And, uh, you should see me in the next video where we talk about when, when parallelism gets real, real messed up. All right. Thank you for watching.