how to create temp table in sql Archives | Page 52 of 144

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

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 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:

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

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.

Going Further

Happy Fourth Of July From Darling Data 🫡🇺🇸

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

Erik Is Not Here Today

Please enjoy this reasonable facsimile of what I’ll be hearing.

Thanks for sizzling!

Going Further

A Little About Hash Aggregate Spills and SLEEP_TASK Waits In SQL Server

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

A Little About Hash Aggregate Spills and SLEEP_TASK Waits In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the world of hash spills within SQL Server, specifically focusing on hash match aggregates and their behavior in row mode. With a bit of a reflective tone, I share my experiences as Erik Darling from Darling Data, discussing how these spills can significantly impact query performance during what should be a relaxing Friday afternoon. The video covers various scenarios, including the differences between batch and row modes, and delves into the disappointing lack of detailed weight statistics provided by SQL Server for these operations, highlighting the frustration with Microsoft’s decision-making in this area. Through multiple queries and detailed analysis, I explore how adding more columns or text data can exacerbate hash spills, leading to substantial increases in execution time and page volumes spilled to disk. The goal is not only to understand these issues better but also to advocate for clearer insights into query performance bottlenecks.

Full Transcript

Erik Darling here with Darling Data. Feeling real bubbly and effervescent on this joyous, I think probably the final Friday of June 2024. Where did this year go? What happened? This year disappeared on us. Feels like slowly, slowly disappeared. Anyway, my eyes feel weird. My left eyelid won’t stop twitching. I think I have some form of irritation in there. But today, as you can see from the giant floating zarzad head of green text, in today’s video we’re going to talk about hash spills.

Because what the hell else are we going to do on a beautiful summer Friday except talk about hash spills. Yesterday we talked about sort spills. At least I think it was yesterday. It was probably yesterday. If anyone can remind me. What we did yesterday. That’d be wonderful.

Alright, so before the Don Julio shows up and everything changes, we’re going to talk about hash spills. Now, this video is only about hash match aggregates. This is not about hash joins. Hash joins are going to be in the next video. Not today. Another time.

Because hash joins… What’s really interesting with hash joins is looking at extended events for the hash bailouts and recursion stuff. Because that’s where you can see the sort of spectacularly bad performance that can come out of hash joins when they spill. So, in general. And now, I’m just… Specifically for row mode.

For batch mode, hash and sort spills make me very nervous. Because if you remember the sort spill video from yesterday, the batch mode sort took forever. It was like five minutes. Whereas, like the equivalent query in row mode only spilled for like a few seconds or something.

So, both batch… Both hash and sort spills in batch mode make me incredibly nervous when I see them. In row mode, hash spills tend to make me more nervous than sort spills. Because, as you’ll see in this video, like, hash spills of… Like, when we cap memory at the same amount.

And sorts usually require a lot more data. Because remember, you’re sorting all the columns you’re selecting by the columns you’re ordering by. But it’s usually like a much closer to size of data operation.

Whereas, with hashes, when those spill, for some reason, they just… Whatever algorithm is responsible for the hash spills tends to really beat performance up. Even for like similarly sized spill amounts.

So, like in pages, right? So, let’s look at this first query here. And we have query plans turned on.

Thank God, if I forgot that. If I forgot that and I had to restart recording this video, I don’t know what I would do. Alright, so, this query takes around 4 and a half seconds. And, you know, I’d really like Microsoft to recall the summer intern who did the operator times here.

Because these make no gosh darn sense. And I’m not quite entirely sure how to interpret this. Because in the row mode plan, the times are supposed to be cumulative.

So, the repartition streams should not… Should have technically run for as long… Should contain the time from the clustered index scan.

So, it should be at least 1.626 plus whatever time gets spent in there. I don’t know where to put that 1 in the accounting. I don’t know where that 1 goes.

I don’t know if we add it to the 1.6. I don’t know if it’s part of the 1.6. I don’t know if the 1.6 is part of the 1. I don’t… I just don’t know what to do with it. Alright?

So, let’s just say that the hash match aggregate ran for 2 and a half seconds or something. Right? Or 2 seconds. I really… These numbers are too depressing for me to think about too much. How we ended up here, I don’t know.

I like the batch mode version better where each operator is its own time. Because then I don’t have to worry about whatever mess this is. Alright.

So, relatively simple. Relatively straightforward. Now, we’re going to look at these next two queries in other windows. Because these next two queries took more time than I want to fill up dead air for.

While I’m waiting for them to finish. Alright? So, what we’re going to do in each of these windows is run the query and then look at our session weight stats for what happened when these queries ran.

And there’s going to be some real disappointing stuff happening here. Alright? And it’s not just related to the timing here where once again this number is lower than this number for some reason.

And I don’t know how to munch those numbers together into some sense. So, let’s just say that the hash match aggregate took, I don’t know, about 25 seconds. With the spilling.

Right? So, in the query where it didn’t spill, it took like two, two and a half seconds. In the query where it did spill, it took a lot longer. Right?

About ten times as long. Alright? Sort spills don’t usually hit you for ten times as long. I guess hash spills are different in that way. This hash spill looks about like so. Spill level two!

We had to make two passes of the spills. All eight threads did that and about 290,000 pages spilled out to disk. So, I don’t know.

That seems pretty slow for 290,000 pages to be honest with you on that. I don’t really know what to say there. Whatever. Not having a good time internally that hash spill. But what’s really disappointing is something that we’ve seen before in here where our old friend Sam, someone at Microsoft, that glorious idiot, decided to hide information from us.

Because the top weights that you see for this query over here are CX import, CX packet, and SOS scheduler yield. But they don’t really tell you where the time went in this query, do they? They don’t really account for the 30 seconds that we spent in here.

What comes a lot closer to accounting for the 30 seconds we spent in here are these things up top. Like Sleep Task and CX Consumer. That our good friend Sam said, It’s for your own good.

It’s in your own self-interest. All you wanted was a Pepsi, but you don’t get these weight stats. You can have the Pepsi. You’re not getting these weight stats in your query plan.

So the Sleep Task and the CX Consumer weights. Don’t show up in your query plan. Because Sam is an idiot. We don’t like Sam, do we?

Sam is not our friend. All right. So looking at this same query, essentially, but this one running in batch mode. So again, I’m using the query optimizer compatibility level to get batch mode on rowstore.

And I’m making this thing spill a whole bunch. All right. Spills are the name of the game.

They are the word of the day is spill. They’re also the number of the day. They’re also the special of the day. They are everything.

Everything for us. Now in this one, let’s look at the weight stats here first. So now we have some kind of new weights, don’t we? We have some HT weights. All right.

These are related to batch mode. These are very batchy mode-y related weights. But we still have a bunch of Sleep Task. And we still have a bunch of CX Consumer. Now let’s go look at our query plan and see what happened in here.

All right. This thing spilled for about 10 seconds. And this one isn’t too bad. Where they get really bad is with the bigger spills, which we’re going to take a look at in a minute.

But if we look at the weights over here. And we look at the weight stats. There we go.

That’s the button. We get htbuild and we get htdelete. Apparently these ones were considered important enough to show. But we still get no Sleep Task. And we still get no CX Consumer. Even though those are at the very top of our waiting query game.

Right. So to recap, we get this and we get this. We do not get this or this. We are all quite sad by that.

Especially because, you know, if you look at some of these wait times, these max wait times, that’s almost 11 seconds on CX Consumer. Right. And if you look at these total wait times in here, that’s a lot of time to not account for in an executing query. Right.

Kind of. It’s not cool, Sam. It’s not cool at all. We don’t like being lied to, Sam the man. All right. So let’s move on a little bit.

And let’s see what happens when our hash spills involve more columns. Remember yesterday when we had sort spills, the more data we added to those sort spills, the worse they got. It was from like a time perspective, from like a weight perspective.

Like they just dragged on and on and on and on and on. These, well, at least for the non-spilling query, even here we add some time to it. Right.

The first one was four and a half seconds. We’re essentially doing the same thing. We’re just selecting more columns and this took about a second and a half longer. Right. Like everything in here, despite, like, holy cow.

It worked on this one. I don’t know what happened. I don’t know what magic happened, but look, the time is actually cumulative on this one. That wasn’t true for any of the other ones.

What happened in here? I don’t know. I don’t know. Sometimes it works. Sometimes it doesn’t. These racy conditions, I guess. What’s happening inside your head?

It’s like trying to figure out what a toddler is thinking. It’s amazing. But anyway, this one did take a little bit longer. Right. This one did.

This one did take. It’s a little bit longer than the one where we were just selecting one integer column. In this query, we’re selecting one, two, three, four integer columns and one date time column. All right.

So now let’s go look at this one in another window. I’ve already pre-run this because, again, I don’t want anyone sitting around bored. And now look what happens here. That went from taking 30 seconds to taking one minute and 33 seconds.

This one took a full minute longer selecting more columns. All right. More stuff in here spilled because we are selecting more stuff.

This one got to spill level three. Right. This is a full level higher. A full spill level higher than the one before that. And the number of pages is also about tripled.

Right. This one went from like about 200 and something thousand to 832,000. Right. So a lot more stuff spilled, though, because we had a lot more in the hash to spill. So when it was just one column we were grouping by, we didn’t have a lot.

I mean, we still ended up messing things up pretty good. Good job, us. But we didn’t. But we didn’t. But this one, because we have more columns that we need to group by and all the other stuff, we end up doing way, way more work.

And in the results, we have way more sleep task and way more CX consumer than we did in the other query. Now, since this one is in row mode, we don’t have the HT weights, which is OK.

Like, we don’t need to know that. But again, these weights aren’t going to show up in the query plan weight stats because Sam needs to get talked to by someone.

Sam needs a talking to. Sam, Sam, Sam. So now let’s look at what happens when we start messing with text columns.

All right, so in this query, we are going to, if I recall correctly, I don’t know, again, some of these queries were written yesterday. Just kidding.

They weren’t written yesterday. I just can’t keep everything in my head all the time. Now that we’re selecting a text column and we’re grouping by this text column, this text column in the comments table.

I don’t know if you remember the sort spill video. We looked a lot at the like average length and the like, you know, like how SQL Server estimates memory for these things. And, you know, and especially at how even like the text column stuff in row mode tended to make spills worse because you’re dealing with larger data when it spills off to disk.

All right. So this query takes about 8.8 seconds. And this doesn’t spill. And somehow, miraculously, the repartition streams is working here.

Maybe it just takes like more data to make a repartition streams to work. Maybe like something has to really slow down in order for the code and read the repartition streams one to work the way it should.

I don’t know. It’s really weird. But at least it’s cumulative. I don’t know if it’s right, but at least it tracks, right? At least it’s logically cumulative going from here to here to here. At least we have that going for us.

But this, we know that by the time we get past the hash, it takes, we are a few seconds ahead of where we were when we were not messing with any text columns. Right?

So now we’re going to look at this thing running in batch mode and spilling. And, oh wait, this is the one I was supposed to close. Ah, nuts.

Here we go. So this is what happens when batch mode hash match spills a text column. Look at that.

2 minutes and 24 seconds. Ain’t that something? That’s crazy, right? That’s nuts. Like, like it’s, it’s right up there with how bad the batch mode sort was. 2 minutes and 24 seconds.

Can you imagine waiting 2 minutes and 24 seconds for this? Now, the spill level on this is back to, is back to spill level 3. But there’s a lot more pages in this, right?

Because we had that text column involved. And there, like there’s definitely some differences between the votes table and the comments table. The votes table is like 53 million rows about.

And the comments table is like 25 million rows. So the comments table, even though it’s smaller, because it has, we’re spilling that text column out. The data pages that we’re spilling out are way bigger.

I mean, not like way big, like there’s a way bigger number of them because the text column makes the pages, like adds more space, right? So the, when we’re dealing with like a whole bunch of narrow data types, even though we did spill a lot and it took a long time, it’s still not quite as disastrous as when we spill out like, like anything that involved text data.

Like the, the high end three, level three, eight spilled threat, eight spilled threat hash join from the votes table was like 800 something thousand pages. This is like 2.6 million, almost 2.7 million pages. So that text column adds a lot more page volume to the spill and really messes things up.

And of course we have in here, our friends. We have sleep task, ht build and ht delete, and cx consumer. And you know, again, for this query, the ht weights will be available in the query plan x xml, but sleep task and cx consumer, because our enemy Sam at Microsoft doesn’t want us to see these weights.

They are not going to be in the query plan, and a lot of the time that you would, a lot of the time that you would, would account for like what, what went wrong with this query. A lot of things that you would, you know, maybe see peripherally, like, you know, like when you go and examine a query plan, that would help you determine stuff are just not in there, right? So it’s always good to know where this stuff comes from.

Now, you know, I do a lot of experimentation with running queries and seeing what their weights are, and that’s sort of how I figure this stuff out. And that’s why in SP pressure detector, you know, the list of weights that I have in there, and with like a sort of description on them, will, you know, decode some of this stuff for you. So like if you, if you were looking at this query plan on your own, when you might like, like, look, the spill is visible, there’s an exclamation point on it, the operator time is visible, you can see how long that thing spilled for.

When you look at the weights, you don’t get the full story of what weights show up when these things happen. And that’s what you kind of have to know because when you’re looking at a server from the top down, if you’re like, you know, you just get on a server, and like you use whatever script you want to look at weight stats, hopefully it doesn’t screen any of these out because someone at Microsoft is a jerk. But maybe it would show you like, you know, these weights in total.

And if you saw the HT weights, and if you saw the CX consumer weights, and if you saw the sleep task weights, you saw the IO completion weights like from yesterday’s video, it would give you a better indicator of like maybe where queries are struggling as a whole. Right? And like that maybe like gives you a place to focus.

Right? Maybe it helps you figure out like, you know, like where the stress and strain on the server is. Now, especially if you see a lot of these spilly type weights, right, like IO completion, sleep task, the HT stuff, if it’s batch mode, you know, and you also see a lot of the page IO latch underscore whatever weights, that’s a pretty good sign that there’s just a constant battle going on between the buffer pool and query memory grants. And that’s that server probably doesn’t have enough memory in it in general.

You know, it might, you know, like, there might be all sorts of other ways you could go to try and get those numbers under control. But like, it just might be a sign that the server is completely underpowered. And that’s where you need to start.

Like, that’s where the quickest performance win is just like, just get some more memory in this thing if you can. Right? So, let’s go look at one last query in here. And we’re going to close this out.

And this one is particularly interesting to me because this one will get a hash match flow distinct. And that hash match flow distinct will spill. And we’re playing kind of a weird trick on SQL Server here with top.

And the bigger you set this number to, the worse this spill is. I had to find something in the middle. And then we’re going to say optimize for top equals one. And even with a recompile hint, can’t figure that out.

Or rather, it is still under the spell of the optimize for hint. And if you look at what happened in here, we, of course, have a number of things that we’re going to do. Once again, a whole bunch of sleep tasks up at the top.

10.991 milliseconds. And I think the reason why, like, you know, this one is helpful to look at is because this one is single threaded and a lot of the other ones run in parallel. So, it’s a little bit more clear, like, where weights go in here.

And so, if you look at the results where we have, you know, fully 10 seconds of sleep tasking, we can probably figure out just how much time was spent actually spilling on that single thread in there. Right?

So, but, you know, once again, if we look at what happened here and we look at the weight stats for the query, the only thing we will see is 4 milliseconds of SOS scheduler yield. All right?

There that is. There’s that 4 milliseconds of SOS scheduler yield and a query that ran for 25 seconds. All right?

So, we took 6.7 seconds here and we took, well, I mean, 25 is 19. And so, we spent about 10 of the 19 seconds in this operator spilling to disk. Isn’t that exciting?

Isn’t that exciting to know about? And, of course, the spill level for this is spill level 5. One spilled thread. Anyway, that’s about enough about hash spills.

Now, again, this was purely about hash aggregate spills. Tomorrow’s video, or actually, no, tomorrow’s Saturday. So, probably not tomorrow’s video and probably not Sunday’s video.

Maybe Monday’s video will be about hash join spills. So, I hope that you’ll join me for that. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you ever meet someone at Microsoft, I hope you have a good talk with them about the weight stats that they’re including in these query plans. That’d be nice.

You know, we deserve better. Us people paying, well, I mean, I don’t pay per core, but you probably pay per core. So, you deserve better.

Apparently, I deserve whatever I get. That’s okay. Yeah, if you enjoyed this video, I do like thumbs ups and I like encouraging comments. Up to and including you, go girl.

If you enjoy this sort of SQL Server content, you can join. Let’s see. Let’s make sure we have this refreshed up until the absolute most current. You can join nearly 3,790 other of my data darlings by subscribing to this channel and getting a notification every time I publish one of these.

And I would just like to apologize to anyone not in an East Coast time zone who gets this notification late at night, like someone in Europe maybe. Or even further away than Europe. Past Europe.

I don’t even know what time it is in New Zealand right now. Australia? Who can tell? So, I’m not sure if anyone else subscribes to me from further away than that. Probably not.

Anyway. I’m gonna go start Friday-ing, because it is Friday and it is time to Friday. Thank you for watching and I will see you in the next video about hash join spills. It will be just as exciting and riveting.

I promise you. I would never lie to you. I’m not from Microsoft. Or the government. Or the government. And that’s smart because I would take care of them. Welcome to this band right now. Now, what we’re aware of is that these are folks that we can play for with our muutest territory where we’re not Arabia Thank you.

Going Further

A Little About Sort Spills And IO_COMPLETION waits In SQL Server

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

A Little About Sort Spills And IO_COMPLETION waits In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the fascinating world of sort spills in SQL Server, explaining how they can impact query performance and offering insights on when to address them. Starting off with a bit of personal frustration over Microsoft support’s suggestion to shut down an Azure instance to save costs, I highlight why better support is available through services like mine at Darling Data. The video then focuses on demonstrating sort spills using two queries—both with hints to ensure one spills while the other doesn’t—and explores why the spilled query can sometimes run faster than its non-spilled counterpart due to factors such as local storage speed and the complexity of sorting multiple columns, especially those containing text data. Through detailed analysis and practical examples, I aim to provide viewers with a deeper understanding of sort spills and their implications for database performance optimization.

Full Transcript

Erik Darling here with, as you may have guessed, Darling Data. In this video we’re going to talk about sort spills. Now, if you’re wondering why I sound a little bit low right now, oh boy. I just got off a very depressing client call where Microsoft support suggested out loud and with a serious face that one way that they could say, save money on their Azure bill would be to turn off their managed instance that runs their e-commerce site overnight so it didn’t accrue any spend. So yeah, we’ll just turn the website off at night. It’s like the early 90s when you would leave the office and turn off the lights and it would also turn off all the servers or something. So yeah, anyway, that’s the thing. That stunk. No one was happy. So once again, if you would like better support than Microsoft is willing to offer you. My rates are reasonable and I am available for higher.

Erik Darling of Darling Data is in fact available for hire for these kinds of things. So you don’t have to be abused by Microsoft financially and mentally. So you might notice that something up here that I’m doing is setting compat level explicitly to 140, 140 because that is the 2017 compat level. The reason I’m doing that is because if we use the 150 or 160 compat level, we will get sort spills that neither of us have the patience to sit and wait for during one of these videos. So here’s what I got from these two queries. It’s really the same query twice. But this is what happens when a batch mode sort spills. That is nearly five minutes. And remember, this is a fully batch mode plan.

So all of the operators in this plan are responsible for only for the time that is spent in them. Down here in the row mode plan, things are a little bit different. Things do get weird because you might see that this ran for almost 2.2 seconds. Repartition stream says, no, I only ran for 1.3 seconds. And the sort says, I ran for 4 seconds. So that’s a little misleading. But anyway, the reason for this is all explained in great detail in this wonderful post by Mr. Paul White, or Ms. A.K.A. Pablo Blanco. And it was based on this demo. And actually, my name actually appears in the blog post, which is a magnificent thing.

And never thought that I would see the day. And if you feel like Microsoft should probably work on this scalability issue, there’s also a feedback item that is actually under review. To Microsoft’s credit, I will click on this so you can see. So this feedback item opened by y’all is truly nine months ago. Oh, man, this one’s ready to pop.

This is under review. And I actually got a thank you from the company. That’s as good as a gold watch, isn’t it? All right. Getting a thank you. Comment thank you. Anyway, let’s get back to regular row mode sort spills. So I’m going to run these two queries. And they’ve got all sorts of hints and stuff on them to recompile and clear out the procedure cache.

And this one up here is I’m using the min grant percent hint to ensure that this thing gets the minimum amount of required memory to not spill. Because I want this one to not spill. And I’ve got this query down here using the max grant percent hint, ensuring that it most definitely will spill.

It was built to spill if you’re into that kind of music. We hear it darling data or not. We like hard goth. Hard goth only. Anyway, just kidding. We like a wide variety of music.

Depends on what the mood is. When it’s a bad mood because Microsoft support is awful, we listen to the hard goth. So let’s look at the query plans for these.

Because that’s what we do, isn’t it? We’re the data darlings who stare at query plans. And let’s be moderately surprised when we look at these two query plans. And we see that the plan that didn’t spill.

Oh, that was terrible framing by me. We’re going to do some sit-ups after this one. And the plan that did spill, we see way over here, these sword operators. The sword operator way…

Oh, my hand. I look like I’m doing something awful to that sword operator. The sword operator way up top did not spill. And that took five seconds. The sword operator right here…

Ooh, that’s nice. That’s right there. Man, that’s good framing on my part. That took 3.4 seconds. But why? Why?

Why my data darlings did this… Why did the query that spilled take less time than the query that didn’t spill? Now, this is something that I… Like, I never used to, like, really catch well query tuning things before Microsoft introduced operator times into query plans.

Because you would see a spill and all you would have to go on is, Well, crap. Spills are pretty slow, right? You should try to get rid of spills. If I fix the spill, maybe it’ll be faster.

It didn’t always turn out that way. Now, the reason why my spill is faster, I mean, first and foremost, is because I am on fast local storage, right? So this is Crystal Disk Mark hitting my fast local SSDs on this computer.

Again, these are SSDs plugged directly into all the same parts and components encased in a beautiful Lenovo laptop right next to where all the other hardware and stuff is.

Because, you know, you don’t have that probably, though. Because you work for knuckleheads. And you work for knuckleheads who dragged you kicking and screaming into the cloud where storage is awful.

Generally awful. And if it’s not the storage that’s awful, then it’s the path that the data has to take getting to the storage. It has to get way over here, right? Your data is nowhere near your SQL Server.

It’s miles away, probably. Miles of network cable away. So I get the benefit of fast local storage that I don’t have to go across miles of wires to get to. You probably don’t have that because you work for knuckleheads.

I work for one knucklehead. But the one knucklehead I work for bought one nice laptop to do demos on. So that’s why this sort is fast for me. It probably wouldn’t be fast for you.

I realize some cloud instances do have, like, a local storage with, like, you know, hyper drives on them. And you could get stuff fast there, too, probably. But most people don’t have that.

So they get really screwed up by this stuff. So you will probably want to fix sort spills. I probably don’t need to fix sort spills. But I’m going to show you in a minute how you know if you need to fix sort spills.

Aside from, like, just, or, like, if you have a lot of sort spilling and, you know, doing things. So one thing that’s sort of interesting about sort spills, at least in parallel execution plans, and we’re going to hope that this query works correctly the first time because this demo is a little weird.

Sometimes it’s, like, great the second I run it. Other times I have to tinker with the memory grant percents. And it’s not fun when I have to tinker with the memory grant percents.

And what do you know? I’m probably going to have to tinker with the memory grant percents. Let’s change this one to, like, 13. Because, you know, what’s funny is it worked three seconds ago when I ran this before recording the video.

Don’t take it out on me. I’m still better than Microsoft support. There we go.

That’s what I wanted to see. So if you look at this top query up here, right? This query, when it spilled, it only… So this query, just to make sure everyone understands, this query is running at doc 8. That’s this many fingers.

And this query spilled… Well, spilled level 1 and spilled 7 threads out to disk, right? That’s this many fingers.

8 threads is this many fingers. And so one of these threads is showing that it did something, right? So if we come over here and we look at the properties and we look at this, we will see one thread with 1,435 rows on it. It looks like it did some stuff.

But this is just a weird query plan timing issue, right? This is not actually an actuality kind of what happened. It’s sort of what happened. Both the thread stuff and the operator time stuff, as we saw in the previous demo with the row mode thing where the repartition streams was not in the realm of reality of what the other operators around it were doing.

The operator timing and the thread stuff can also not be anywhere near reality. It’s sort of like me after 8 p.m. Me and reality are not shaking hands anymore.

But so this query down here, which spilled to level 1 and spilled, oh, why did you disappear? You were right there. All you had to do was not leave like my dad.

So this is level 1 and spilled all 8 threads. So this sort, even though almost 53 million rows from both of these go into this sort, and both of these sorts sort 53 million rows, this one looks like it didn’t do anything. All right, it’s just, it’s all zeros in there, all right?

Like my report cards. So, again, the reason why, like, the sort spills are generally faster is because… I have nice local storage, which you don’t have, probably.

I hate whispering. Sorry. Sorry about that. Sorry about that. So sort spills can get worse as you have more columns to spill. So just, you’ll allow me to go back in time one moment.

If we look at this sort, we have one column in the output list, that is post ID, and one column in the order by, which is post ID descending. Okay? So if we run these two queries now, and again, I have my little hints here just to make sure everything happens the way I want it to.

That should be… Is that… Those are the right two queries?

I didn’t highlight the one above it, did I? That was rather foolish of me. Rather foolish. Oh, Eric. Where does your foolishness cease? Ever.

So we’re going to run these two, and this is important because you should understand this about sorting data in SQL Server. Right? And this one is still a little bit faster.

Not as crazy faster as the other one. Right? Six point… Oh, man. Zoom it is all over the place today. 6.2 seconds versus 5.7 seconds. But now the sort operators are going to look a little bit different than they did in the previous demo.

And they’re going to look different because we have, if the tooltip ever graces us with its presence, we have way more columns in the output list now. Right? We’re still only ordering by post ID.

But what SQL Server has to do is all the columns in the output list, those also have to be put in order. Right? Like, that’s what a sort does.

It sorts all the data that you’re outputting by the column that you’re ordering by. So, you know, again, I’ve probably gone over the Excel analogy a few times where when you’re using Excel and you click that button in the top left-hand corner and everything gets highlighted. And then you click sort and you choose a column and everything in the spreadsheet flips to match the sort order of that one column.

Or Excel kind of yells at you and is just like, are you sure you just want to sort this one column and not everything around it? Because you’d look kind of stupid if you did. So, that’s what SQL Server kind of has to do in memory too.

It has to flip all the order by columns to the order of the, so it has to flip all the output columns to the order of the order by column. So, that’s why queries that select more columns and need to order those columns need more memory. Right?

So, that’s one thing to keep in mind there. And one thing that will exacerbate those issues is when you have text data, or not just like the data type text or ntext, I mean like any string data really. Anything that is not, like all the columns that we’ve been dealing with before, these are all integers or dates.

I guess they’re all integers and there’s one date time. So, these aren’t like, you know, big honking columns with like variable lengths and stuff where SQL Server has to guess how much data is in them. Right?

So, if we look at the comments table and we run this query, right, what I want to show you is that a lot of columns in here don’t, like, so the, just to make sure, make sure, sure, we understand what we’re talking about here. The, the text column in the comments table is an envarchar 700. Right?

Envarchar. Double, double byte encoded text. And so, that’s why I have data length divided by two. Also, data length tends to be a little bit faster than length when we do these things. So, that’s why, that’s the divide by two there.

So, if you look at all the, the stuff in here, like, a lot of the comments that, you know, just, listen to this top section, don’t have very long length, byte lengths, compared to the maximum byte length of the column. But, what’s really interesting, ready, like, so, I’m going to show you this and I’m going to talk a little bit about memory grant stuff, is when we run this now, we have this average column length of 302 bytes. And this is actually, this actually plays pretty well into how SQL Server does memory grants for string columns.

Right? Because what it does is it guesses that every row that is produced, that needs to be sorted, for a string column, that, that, that row data will be half full. Right?

So, for a var, and varchar 700, having 302 bytes in there is actually pretty, pretty close to half. Right? So, the average comment length in here actually works pretty well for that algorithm. It might not work well for every, like, data set.

You might have, in varchar 700 columns, where, like, legit, like, everything really is only, like, you know, half or, like, 50 bytes full or something. And you would just get really excessive memory grants for that stuff. So, if we run this query right here.

And this query does not select the text column. And we look at what it does. This, this one still sorts, spills, this one still spills a little bit.

But it’s pretty quick, right? Two seconds. Like, no one’s, no one’s going to really gripe about two seconds. But now, if we run this query, which is, which is captive, a max grand percent of one with the text column involved. Here’s, here’s what we’re going to have to, here’s what we’re going to do.

Is we’re going to come over here. And we’re going to run sp pressure detector with a sample of 12 seconds, which might, which might help you understand exactly how long that query that I just highlighted is going to run for. So, we’re going to kick that off.

And then we’re going to run this. And what we’re going to see at the end is that SQL Server spent way more time spilling when there was text data involved. Because we have way more pages to spill out.

We have bigger data size to spill out because of that text column. Right? So, this whole thing takes about 10 seconds, which is just, which, if you’re wondering why pressure detector was at 12 seconds, it’s so I could run it and have like a second or two of grace period to, to come over here and execute this one. Right?

So, if we look at what sp pressure detector tells us about the 12 seconds that this ran for, what you’re going to see way up at the top is this IO completion weight. And if you, if you notice the, the helpful description column that I’ve put into SP pressure detector, just for you, just for you, because I love you and I care about you way more than Microsoft support does. So, this is the weight type.

This is the weight type that you will see from queries when they are spilling sorts a lot. There are different weight types, which we’re going to look at in future videos that happen when hash spills, hash operators spill from both a hash aggregate and hash join perspective. But the IO completion weight is pretty decent, like you can associate that pretty decently with row mode sort hash spills.

So, if this were, if this were a batch mode query, you would see BP underscore sort as the batch mode sort thing that was happening when things were spilling and getting awful. So, if you, if you’re looking at a server as a whole, or if you’re looking at weight stats for a query and you’re wondering what IO completion means, well, if, you know, if you have a lot of slow queries that are, you know, doing a lot of sorting and they’re doing that sorting in row mode, there’s a pretty good chance that they are spilling lots and lots of stuff out to disk. And since you work for knuckleheads who, you know, put you on the cloud, you’re probably going to have to fix those because that actually can meaningfully slow a query down right there.

So, IO completion weights, if you see those associated with running queries, a lot, some spilling going on. Whether that spill is the root cause of why the query is slow, you’re going to have to determine that or hire me to do that because I’m happy to, happy to tell you. Either way.

But that’s, that’s what you would have to do there. Anyway, my, my wife has been texting me for 20 minutes. So I should probably respond or something. But before I do, before I go, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something about sort spills. If you like this video for whatever reason you like it, it doesn’t have to be the content. It could be, it doesn’t have to be what you see in SSMS.

It could just be my bright, sunshiny presence here on the screen. I like the thumbs ups on the videos. And I like, you know, the you go girl comments in the videos.

You can even say you go girl. I won’t, I won’t be offended. So there’s that. If you like this sort of SQL Server content in general, please subscribe to my channel and you can join drum roll. Let’s hit this refresh button.

Make sure we’re totally up to date. You can join nearly 3,779 other data darlings who get notified every time I publish one of these videos that mean so, so very much to me. So once again, from the bottom of my heart, thank you for watching.

Thank you.

Going Further

Join Me In Seattle To Learn Why Everything You Know About Isolation Levels Is Wrong

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

Everything You Know About Isolation Levels Is Wrong

The PASS Data Summit session lineup has been announced! And, you know, since me and Kendra are double-teaming two days of precons Nov 4-8 in Seattle, I’ve got a regular session too!

Everything You Know About Isolation Levels Is Wrong

You’ve been told that NOLOCK hints are bad, so you look at all the queries your developers write and hang your head in shame.

A NOLOCK hint here, a NOLOCK there, a NOLOCK hint seemingly everywhere. Like termites, eating at the foundation of your well-being.

But in the real world, how are you supposed to remove those all those yucky hints without blocking and deadlocking causing huge problems?

I’m Erik Darling, a world class NOLOCK hint removal expert, and in this demo-heavy session, I’ll change your mind about every isolation level. You’re going to learn why:

Read Committed is nearly as weak as Read Uncommitted
Optimistic isolation levels aren’t incorrect-result factories
Repeatable Read isn’t what it sounds like
Serializable isn’t the enemy of concurrency
You don’t need to worry about tempdb’s version store
No isolation level is perfect for every workload

At the end, you’ll have the confidence and knowledge to start turning on optimistic isolation levels and stop hanging NOLOCK hints all over your queries like Christmas tree ornaments.

Session Prerequisites: Basic understanding of locking and blocking problems, some familiarity with isolation levels.

Get A Deal On Ticket Prices

If you want to get a deal on registration — and you should hurry up and do that because birds of earliness prices expire on July 9th — head over here.

When you’re registering, use the discount code DARLINGE24 for $150 off the regular price for the three regular session days, Wednesday – Friday.

While you’re there, don’t forget to sign up for me and Kendra’s precon days.

Because I’m teaching on my birthday, and if you don’t come we are NOT FRIENDS ANYMORE!

Thanks for reading, and see you in Seattle!

Going Further

A Little About String Splitting In SQL Server

Posted on June 28, 2024January 22, 2026 by Erik Darling

A Little About String Splitting In SQL Server

To get the PASS Summit discount, head over here and use the discount code DARLINGE24 to get $150 regular price on the 3-day convention.

Thanks for watching!

Video Summary

In this video, I dive into the appropriate use of string split in SQL Server queries, highlighting common pitfalls and best practices. Erik Darling from Darling Data shares insights on how using string split directly within a query can lead to poor performance due to inaccurate cardinality estimations. He demonstrates this with a practical example, comparing it to his previous video on table variables, showing that both methods result in similarly suboptimal performance. To avoid these issues, he emphasizes the importance of dumping the results into a temporary table before using them in your query, which allows SQL Server to make better estimates and optimize the execution plan effectively. By following this advice, you can write more efficient queries and even take advantage of his special discount code DARLINGY24 for Pass Summit, saving 150 bucks while enjoying some well-deserved drinks with Erik on his birthday!

Full Transcript

Erik Darling here with Darling Data. Gosh darn it. And in today’s video, we’re going to talk about two things. The main point of the video is the appropriate use of string split in SQL Server queries. The first thing, probably the most interesting thing, is I have a discount code for you for past Data Community Summit 2020, 2024. That is, of course, coming up this November in Seattle. Remember, one of those days in there is my birthday where you are contractually obligated to buy me a drink. Everyone at the conference is contractually obligated to buy me one drink on my birthday. And I am contractually obligated to drink that drink on my birthday. I’m going to die. But if you are on the fence or if your employer is cheaping out on you about coming to pass Data Community Summit this November 4th, through 8th, through 8th, in Seattle, Washington, I have a discount code for you. So if you use, and this will be in the show notes for the Darling Data Radio podcast for Data Darlings, if you use the discount code, and I hate this, Darlingy24, you can get $150 off the three-day conference ticket. That’s this thing. So if you’re bad at math like me, and you use SSMS as a calculator, the regular price is $1,895. With the discount, it’s $1,745. Now granted, that probably isn’t coming out of your pocket. Your boss or your company is probably paying for that. So if you want to save your boss or your company $150, they can… I don’t know what a company does with $150, to be honest with you. It’s always blown my mind that $150 would make a difference.

in a company budget. But anyway, if you want to get that discount, and when you sign up to go to Pass Data Summit, you can plug Darlingy24 in there and receive your $150 off. So that’s cool, I think. Anyway, let’s talk about the appropriate use of splitting strings in SQL Server queries. Now I’ve created a couple opportune indexes here on the post table and on the user’s comments table, sorry. And what I’m going to do is I’m going to show you two queries that hit these tables. Now this demo might look very familiar to you. If you remember my smash… I’m going to actually… I’m going to start running this because…

it runs for a little bit. So if you remember my New York Times best-selling YouTube video on table variables, splitting strings and using table variables have two very big things in common. SQL Server has no idea what’s in them. SQL Server has no idea what’s in your table variable. It might know how many rows are in there, maybe, under the right circumstances, but it has no idea what data makes up those rows. And you would have seen that in my video called, for the last time it doesn’t matter how many rows you put in a table variable.

And the same thing goes for a string. So this demo might look very familiar to anyone who is… who watched that video. But here’s what I’m doing. Wait, let me… I’m sorry, I’ve messed things up a little bit. I did not frame things appropriately here. I want to make sure… ZoomIt is very laggy on me today, I’m not sure why. ZoomIt is once again rebelling against me. So right up here, I am declaring some IDs, or I’m declaring an ID variable, not a table variable.

Right? Just in the… For the naysayers out there, it’s only a varchar8000, it’s not a varcharMax, not a varcharMaximum data type. So there’s nothing to freak out about there, just a varchar8000. Still a pretty long string, right? It’s a pretty long letter. Someone wrote 8000 bytes to me, I probably wouldn’t read half of it.

But then what I’m doing is… exactly like what I did in the table variable demo, to show you how… Sebel Server cannot do good cardinality estimation with table variables. It can also not do good cardinality estimation with split strings.

So I am doing exactly the same thing, where I am inserting the ID for Mr. John Skeet. At least the last time I checked, he was still a mister. I don’t know if that may have changed. It’s difficult to keep up with John Skeet. And then I’m also sticking in the top 99 nobodies.

Sorry if you’re in this group. The top 99 nobodies in the Stack Overflow database. These are users with a reputation of one who have created their accounts most recently. And we do not have high expectations of these new recruits, these cadets, because they have not had enough time to make an impact yet.

John Skeet has been around from the beginning, making big impacts, tyrannosaurus foot impacts on things. And so, you know, we don’t expect them to have much data. And so what I see clients do a lot is something like this, where they will extract values from a list of things.

And then they will either do like, you know, with an in or exists or a join. I’ve chosen a join in this case because it was the easiest way to convert the old demo to this one. Pure laziness on my part. Nothing funny going on, I promise.

But this is what I do, where I extract the value from the string split. And then I join that to the post table. And I join the post table to the comments table. And then down here, I show you how wrong of an idea this is.

This is definitely not a good thing to do. This is the bad way of doing things. This is the inappropriate use of string split in a SQL Server query.

It’s not how you want to do things, because you will end up with very bad performance. If I move my armpit a little bit, oh, that’s my hand too. You might see two minutes and 13 seconds under there.

And that two minutes and 13 seconds was largely eaten up by just this query here by itself. Right? Because this is the crappy way of doing things. If you do things in this way, my rates are reasonable.

You can hire me to fix these things. I will fix them all day long, happily. I’ll whistle. I’ll draw you doodles of things. I’ll bring flowers.

Maybe we’ll go on a picnic. I don’t know. We could do all sorts of stuff. But really what you want to be doing is something more like this, where you can split your strings into a temp table with the appropriate data type already assigned to it. One thing that it doesn’t…

So it can certainly make things messier, but it’s not like the biggest issue with the string split. And I’ll show you in a minute. But by default, the value column that comes out of string split is like some big envarkar column.

But SQL Server is able to like do a convert implicit on what comes out of the string split function if you’re using it like I am where you’re joining it off to something. And I’ll show you that in a minute in the query plan. But then if we put that into a temp table with the correct data type, SQL Server can do this a lot better.

Because two reasons. One, you know, again the correct data type, minor point there. But when you put that data into a temp table, SQL Server is then free to figure out what makes up that data.

You get a statistical histogram on the temp table, so SQL Server can do better cardinality estimation. This is almost the exact same thing that happened with the table variable. SQL Server doesn’t know what makes up the data in the table variable.

SQL Server also doesn’t know what values, what comma separated values make up the string that you’re splitting. It does not pause every time it extracts a value and say, oh, it’s this value, cardinality estimate. Or it doesn’t like extract, it doesn’t fully extract the values and say, oh, this is what I’ve got.

Let’s do cardinality estimation based on these values. It just says, uh, crappy guess. Got a crappy guess for you?

Want a crappy guess? Got a crappy guess. Got lots of crappy guesses. Handing them out, making it rain crappy guesses. So, if you’re gonna split on that string, what you want to do is not this, where you immediately join to it in your query. You want to do that and dump it into a temp table.

Right? And I think the proof here is in the query plans. Now, we return exactly the same result for both of these. That’s a big number and all, but if we look at what goes on here, right?

We have our initial string split, right? Or, sorry, this is our initial string assignment, right, up here. And this is quick, about 45 milliseconds in total.

And then down here, this is our first attempt at the count query. And this ends up performing just about as poorly as the table variable query for just about the same reasons. Now, I could have made this worse, but I chose to not make it worse.

I chose to make it a little bit easier because if I made it worse, it might have looked a little unrealistic. But, I mean, if you look what happens here. Oops, come on, zoom it.

This query takes 2 minutes and 12 seconds in total. And SQL Server guesses 50, but we get 100, right? So we have a 200% misguess here.

But then SQL Server’s estimates down here are also awful. We are off by 5, wow, that lined up really nicely. I didn’t do that on purpose.

If I tried to do that a million times, I would not be able to do that. That is a very nice… Look at this, look at the symmetry on that. God bless. Wow. Anyway, SQL Server makes a very bad guess here, right?

We get almost, we’re not even close to reality. We get roughly 5,804% wrong. But granted, since we have a good index, it happens pretty quick. The trouble is that, I mean, A, the guesses are off.

And then SQL Server has chosen this particular plan shape, where we just go from nested loop to nested loop. And you’ll notice that we spend quite a bit of time in here, where this estimate is off by some number that can only be conveyed with an ellipsis at the end. That is 81704…

I don’t know where that number ends. I do know, looking at this, that if we pay attention to these numbers right here, and again, I’m not good at math as a high school dropout. A public education received student.

But, I can tell you that 1-3-0-4-0-0-9-0-6-7, that is a 10 digit number, is a lot bigger than 1-5-9-6-0, a 5 digit number. How much bigger? You figure it out.

81704 something percent bigger. A lot bigger, right? Orders of magnitude. If anyone knows how to order a magnitude, please let me know. I’m willing to pay extra.

So, this thing, this whole thing takes 2 minutes and 12 seconds. Now, if we look at the rest of what happened in here, when we go, and we look at our ingenious method of, dumping data into a temp table from the string that we are splitting, right?

We dump this in here, and we look at the query plan down here. It’s going to look a lot like the query plan that we got when we used a temp table in the, for the last time, it doesn’t matter how many rows you put in a table variable video, where SQL Server makes slightly better guesses.

So, in here, SQL Server actually makes the right guess. It knows 100 rows are going to come out of here. It even goes to the trouble of sorting those 100 rows, so that we match the order of, if we had a clustered index on the IDs table, we wouldn’t need to do that, but that’s okay.

For this, I’ll forgive the zero second, the zero anythings that it takes to do that, right? And then we, granted, this guess is not fantastic, but you know, the default cardinality estimator is not fantastic, so we don’t get an impossibly good guess here, but we don’t necessarily, you know, the nested loops join thing is fine here, okay?

The big deal here is that SQL Server sticks a stream aggregate between one nested loop and the other, right? So we have this combination of query plan operators in this plan that we don’t have in this plan, right? Remember I said from this one, it just goes from nested loop to nested loop.

For this one, SQL Server says, you know what? I’ve thought about it, and I think we need to trim this down. And so SQL Server has to do far less work in here.

This goes from taking, let’s go look at that again. This goes, that took, ah, get out of the way tooltip. No one needs you.

We’re all hitting F4 and getting properties when we need to see that stuff. So this goes from taking one minute and 28 seconds to taking one millisecond. Now granted, this cardinality estimate is still off by a little bit, but this aggregate saves our buttocks in a very serious way.

And so this query working off the temp table only ends up taking 10 milliseconds. It’s a big difference, all right? So when you’re, I beg of you, if you are writing queries in SQL Server, and you are passing comma separated or anything separated lists, even if you’re passing XML or JSON or anything like that, and you need to relate that blob of string text to something in a query, in a joiner where clause, or a subquery, or an exists, or really anything relational, anything with relational meaning, please, I beg of you, do not do the string splitting in the query.

Take the XML or the JSON or the comma separate, anything separated list of things you’re passing, dump them into a temp table.

You can use any string splitting facility you want for that. You can use the built-in one that Microsoft gave you in 2016. You can use the one that you copied and pasted that’s a multi-statement table valued function with a while loop in it. You can take that one that you copied and pasted from the internet in 2002 and use that, and it will not profoundly affect your query because you’re dumping it into a temp table.

And then you’re going to use that temp table in your query instead of doing the string split in the query, and you’re going to look so smart in front of everybody. You’re going to look so smart that your boss will be like, Hey, you know what?

You get to go to pass summit this year, and you get to use Erik Darling’s discount code DARLINGY24 to do it, because we’re going to save 150 bucks, and I’m going to put 150 bucks cold hard cash in your hands to buy Erik Darling drinks for his birthday. That’s exactly how that’s going to go.

So, for the sake of all of us, so that you get to come to pass and celebrate my birthday with me, and you get 150 bucks cold hard cash from your boss to buy me drinks, you’re going to not put string split where it would matter in your queries.

You’re going to dump your split string results into a temp table, and then you’re going to use that temp table in your query. All right? Cool.

So, with that out of the way, thank you for watching. I hope you enjoyed yourselves. I hope you learned something about string splitting, the appropriate uses for it in SQL Server queries.

If you like this video, you know what I like? Thumbs ups. I like nice comments.

I like motivational comments. Those are cool, too. If you like this sort of SQL Server content, you are free to subscribe to this channel and join, let’s see, let’s get the most current tally on this, nearly 3,775 other data darlings out there by subscribing to the channel and getting a delightful notification, very helpful notification every time I publish one of these videos.

And you know what that gets you? 150 bucks off pass. And the pleasure of my company.

And, I don’t know, cool way to kill 15 minutes? I don’t know. Maybe you’re like, you’re in the bathroom, you got nothing else to do. You finish playing Wordle.

You finish playing Words with Friends. And you’ve gone through your RSS feed. And you’re just like, what am I going to do for 20 minutes? Earbuds in.

Watch my video. Soothing. It’s meditative, practically. Reduce blood pressure. Give you a good laugh. I don’t know.

Whatever. It’s a cure for what ails you, basically. It’s like drinking a Guinness in the morning. Right? The smart thing to do. Anyway. That’s enough for me.

I’m going to go back to work. Maybe go to the gym. Give this Adidas t-shirt a workout. Right? Get sweaty a little bit.

And I don’t know. I’m going to think about what to record next. Because I’m always thinking for you. Anyway. Thank you for watching.

Going Further

Some Notes On Query Tuning With Batch Mode In SQL Server

Posted on June 27, 2024January 22, 2026 by Erik Darling

Some Notes On Query Tuning With Batch Mode In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into some interesting aspects of query tuning in SQL Server, particularly focusing on batch mode execution and parallelism. Starting off with a humorous anecdote about re-recording due to a weekend brain fog, I share the excitement of being voted “the classiest SQL Server consulting shop ever” by BeerGut Magazine—a moment that adds a light-hearted touch to what can sometimes be complex technical discussions. The video then explores how batch mode execution works and its limitations, especially in achieving full rowstore parallelism. Through detailed examples, I demonstrate techniques like using temporary objects and query hints to optimize performance, highlighting the nuances of SQL Server’s heuristics and the importance of understanding these mechanisms for effective query tuning.

Full Transcript

Erik Darling here with Darling Data, re-recording the video that I uploaded Friday where it sounded like my throat was full of weird bugs. So we’re going to try this again and hopefully I remember to say all the right stuff because it’s been a weekend and after a full weekend everything that was in my brain the week before disappears. So we’re celebrating a very very special day here at Darling Data. We were voted by BeerGut Magazine to be the classiest SQL Server consulting shop ever. We’ve got some drivers apparently Intel is telling me about. Thanks Intel. Get right on that. And yeah, we’re going to talk today about, I don’t know, some batch mode stuff, some parallelism stuff, some parallelism stuff. You know, some general query tuning stuff. Nothing, there’s nothing overly specific about this video. It’s kind of a funny thing about it. It’s just, it’s just the stuff that I find interesting and you’re just going to have to deal with that today. You might find value in, you know, a few different places here, but there is no overriding theme or point to this video aside from the fact that you can see here.

I find it interesting. So let’s look at a query. Let’s do that. All right. We’re going to make sure we’re doing all the right stuff there. We have query plans turned on and now let’s run this. And what we’re going to do is in about eight seconds, we are going to see the most offensive query plan ever. Highly offensive. I might have to blur it out. That’s how bad it is. Because Tipper Gore is going to be all over this channel when you see this thing. Look at this. Behold. Behold the terror, the tragedy that is this query plan. Look at this. Look at this thing.

So we have two, it’s so awful. We have a parallel merge join, which of course should not exist. Right? That’s just ugly. And for some reason, SQL Server decided that it wanted to sort a 53 million row table to make that parallel merge join happen. And no, just no SQL Server. You’re bad. You’re awful. Why? Why? I’m downloading Postgres as we speak. Postgres would never do this to me. Actually, I’m lying. It probably would.

But any database engine can forsake you like this. None of them are, you know, none of them are beyond this level of offense. But holy cow. It’s a wild one, isn’t it? It’s a real wild one. Now, whenever I’m tuning store procedures or queries and I see stuff like this, one of the first things that I always try to do is just see, like, what will happen if I use batch mode. Because oftentimes, batch mode will help us avoid calamitous query plans like that. And, you know, for queries that do a lot of CPU work, have a lot of CPU effort, batch mode can be very useful.

Because we work on batches of rows. We don’t work on a single row at a time. Sorry, something is itching my forehead and I don’t know what it is. It’s a bit distracting. Anyway, so what I usually, you know, first try to do, and, you know, if I’m using a SQL Server version that has in-memory tempDB turned on, where I can’t use a temp table with a columnstore index because I’ll get an error, which you don’t have to worry about in managed instance because it’s, like, apparently 15-year-old software and that option isn’t available in it.

Don’t use managed instance. But this is one of the first things that I’ll try here because this will, you know, give me at least some hope of maybe getting a batch mode plan that’ll be helpful. Right? And so what I’ll usually do is something like this, where I’ll, you know, create a temp table.

There will be a clustered columnstore index on it. And then I’ll do sort of a funky, tricky, little lefty join-y thing like that just to, you know, let the optimizer get a little width of a columnstore index so that it’ll be a little bit more lenient about using some batch mode operators.

Now, one thing that I’ve been sort of lazy about showing in demos when I do this is I usually give the column in whatever, you know, either temp table, columnstore helper object, or if I can’t use a temporary object because of in-memory tempDB, and I have to create, like, a dbo.t or dbo.more descriptive name, like columnstore helper table, I’ll always give this column kind of a weird name because if we were just, if we were being real lazy, and let’s say we were doing select star here, or we were doing, like, we had a CTE, we were doing, like, select into, we would get potentially duplicate column name errors.

Or if, like, we were working on just a real shamble of a query where no one was aliasing stuff, we could get, like, weird, ambiguous column name errors. If, like, this column was just, like, called ID and we were selecting just, like, ID up here, right?

Or we were joining to just ID down here without an alias. So it just, it kind of helps you to avoid weird stuff if you give that column a weird name. So there’s that.

Again, another weird little point along the way. Something for you to marinate on later. So let’s see what we get now because we’ve got this all set up. At least I think I created this temp table.

We’re going to find out. If this throws an error, it’s fine. If it doesn’t, then it would have thrown an error when I ran this query. So either way, you were going to see some, you were probably bound to see some red text if I didn’t do that, right? If I didn’t preemptively do that.

And the problem we run into here is that this trick doesn’t work here. SQL Server still chooses this godawful plan where, again, we have a parallel merge join, which is just grotesque beyond compare.

And SQL Server has once again decided to sort 53 million rows to get us, to allow for this, right? And that is, this is just no good. All of this, bad.

If I were a better artist, I would draw like a thumbs down thing here. But it’s terribly difficult to draw with Zoomit. And, you know, I think this is bordering on obscene. So I’m going to stop that.

But anyway, so this is one of those kind of funny things that happens is we’ve actually costed ourselves out of a better execution plan. So let’s run these two back to back, right? We’re going to have our eight-second monstrosity.

And then we’re going to have a slightly faster plan below it. And the reason we’re going to get a slightly better plan below it is because we have told SQL Server, please, no more any kind of join other than hash join.

And when you force SQL Server to use the hash join plan, it is also far more likely to engage batch mode because batch mode and hash joins, well, they go together like Forrest and Jenny, right? They make quirky little babies.

And we’re going to, okay, so now let’s look at the query plans. All right. So we have our same monstrosity up here. Il monstro, we hate you.

You are awful. If I saw you in real life, I would tell whoever was running SQL Server to get a refund. And down here, we have a slightly better version of the query plan where we have these two operators, which I’ll show you in a minute.

But these two operators, we got our hash join and we have a sort here. But the important thing here is that we didn’t get a parallel merge join. And the reason we were getting parallel merge joins before is because costing is stupid and broken.

And if we look at the estimated subtree cost of this query at $3,945 query bucks, and we look at the estimated cost of this query at $4,512.88 query bucks, we can at least understand why SQL Server chose this awful monstrosity of a plan.

It thought that the cost would be much lower. But it wasn’t. Well, I mean, it was.

I mean, the estimated cost was much lower. But it was still wrong. So, like, one thing that I’ve said in a billion videos at this point is, you know, you can have queries with a very high estimated cost that are fast, and you can have queries with a very low estimated cost that are slow.

So, cost really has nothing to do with how fast or slow a query is. Right? Cost is just a bunch of estimates that the optimizer uses to assemble a query plan and hope it’s the best one.

The problem is that all those estimated costs are a lot like lottery numbers. If you knew what the actual numbers were going to be ahead of time, the results would have been much better. Like, I think that if SQL Server understood what it would take to run this query, the cost would not have been so prohibitive that it chooses this awful monstrosity up here.

So, that’s that. Now, the thing that I want to show you in this bottom query plan, actually, is that we have a sort that runs in batch mode.

And, like I said, we have the hash join that runs in batch mode. But that’s it. Right? This clustered index scan on the post table, I believe. Yeah, post.

And this clustered index scan on the go away SQL prompt on the votes table, these both run in row mode, which isn’t how it has to be. Now, if you’re…

Okay, so here’s, like, the list of, like, things that have to align perfectly for you to get batch mode on rowstore. You have to be on SQL Server 2019 and up.

You have to be in compat level 2019 or better. And you have to be on Enterprise Edition or an equivalent SKU, which could be, you know, developer edition, which I wouldn’t blame you if you wanted to use that.

Because that’s how Microsoft screws you into buying Enterprise Edition in production. When, like, you’re developing stuff on developer edition and you’re like, wow, all this stuff works great and is fast.

And look at this nice thing that SQL Server does. And then you, like, release it on standard edition and everything kind of falls apart. And you’re like, wait a minute, what happened? That’s what happened.

So, you know, I sort of sympathize with people who are like, oh, well, we’re just going to use developer edition. You know? Who am I to argue?

Screw job like that? Get your revenge. So, anyway. So, like, it doesn’t have to be that bad. So, what you can do on newer versions of SQL Server is, let’s say you, like, that turned out pretty all right, right?

Where you have 3.6 seconds, like, down from 8 point, I mean, at worst, this thing was like 9 point something seconds. So, we got it down to 3.6 seconds just by getting a couple things in batch mode and using a hash join instead of a merge join and not sorting 53 million rows and spilling and, God almighty, what’s wrong with you?

But the thing that’s interesting to me is that batch mode tricks do not get you the full batch mode on rowstore experience that you could get, right?

So, if we, what you can do is you can use these compatibility level query hints to get you higher compat levels than what your database is set to, right? So, this is, like, a good way to sort of override some things and be like, hey, I got a better idea.

So, let’s run this query now. And, unfortunately, we still need the hash join hint there because if we don’t put that there, SQL Server will still cost itself into that crap hound of a merge join plan.

So, batch mode on rowstore has problems because it is all based on heuristics. And those heuristics might kick in, they might not. And even with me doing my batch mode trick there in the higher compat level, I still don’t get batch mode where I should.

I still get costed into that awful merge join plan. So, now if I look at this execution plan, this one’s a full second faster. And where it gets faster is on this clustered index scan on the votes table.

For some reason on the post table, SQL Server, oh, come on, give me the tooltip. I don’t need a missing index in. So, for some reason, SQL Server on the post table is like, no, we’re going to scan this in row mode still. That’s whatever.

And down here, come on, go away. Zoom it, catch up. Do try to keep up, zoom it. This actually does get scanned in batch mode. So, this ends up faster. But there’s something interesting going on in this query plan.

And then, so if I go and look at the properties here, this is something that we have looked at before, you and I, all of us lovely folkses together.

And this is something kind of interesting, right? Another, again, something interesting along the way. Hmm. Neat stuff.

So, we have eight threads that cooperatively scanned the post table, right? All these numbers, I promise you, will add up to this number. But only one thread, oops, I hit the wrong button there. Only one thread produced any rows.

Produced 47,575 rows. So, only thread two. This lucky devil right here did any real work. All right? Well, it produced any real work. All right?

So, you know, this thing goes into the hash join. And then, I think down here, it’s sorted out pretty well. I kind of forget, though. Oh, so only, yeah. So, down here, two threads end up doing some work. So, this is, again, some sort of a bit of lopsided parallelism.

Unfortunately, batch mode stuff does not fix this sort of parallel skew issues that can come up. Now, one thing that you can do to get around that is you can use a temporary object, right? Because sometimes, if you can’t, you can’t always rewrite a query to get it to do exactly what you want and fix every single problem.

So, sometimes using a temporary object is the right choice. So, let’s dump our post table stuff into a temp table. And notice, this is a whole heck of a lot faster now, right?

Remember, there was like 1.7-something seconds. Now, that’s only 500 milliseconds. So, we save almost a second and a half just by doing this. And what’s nice about this now is that rather than just ending up with all our rows on one single thread, we actually spread things out quite a bit.

So, we don’t have that same parallel skew problem just dumping that into a temp table. So, we’re doing well here, right? I think we’re doing better, right?

We’ve made this a little bit faster. But now, watch this, right? This is, you’re going to get a kick out of this. I know you’re going to get a kick out of this because you watch my videos. And if I get a kick out of something, you’ll probably get a kick out of it too.

Now, watch what happens here. Now, we get a batchy, modey plan, right? I think, yeah, batch mode, batch mode. Okay, so that turned out pretty good.

You know, our left join to the little temp table over there did us some good. And now, we’re working up this temp table. But watch what happens down here now, right? This one’s okay, right?

This one’s kind of okay. This number sticks out a little bit further than the rest. But now, look at, watch, ready for this? Ready for the filter? Actually, let me select this so this stays.

You ready for this filter operator? Bam! We just shifted the parallel skew problem over here. All right, that’s interesting.

Okay. So this thing, all in all, this takes 1.8 seconds. 1.889 seconds in full right there. All right.

All right. So now, batch mode, you know, for all the great good that it does to queries and SQL Server, sometimes it is, sometimes it doesn’t do as much as it should when you’re only using the batch mode tricks. Right?

Right? And what, the reason, I mean, I don’t, is there a reason why? I suppose if there’s a reason why, it’s because, you know, we are using the older compat level where batch mode on rowstore isn’t available. Right?

So if we were in compat level 160 or better, like, we would get, like, a better batch mode experience, but only if batch mode on rowstore, like, chooses us. Like, just using a batch mode trick doesn’t necessarily guarantee you batch mode on rowstore. Now, let’s race the batch mode version of this query versus the row mode version of this query.

Right? Because, like, the only two operators in here that get batch mode are these two. You know, this, all this, all the rest of this stuff still happens in row mode.

And, of course, the parallel exchange operators, they don’t, they don’t have any batch mode support. So their row mode is hell anyway. Now, look at down here, though.

That’s 1.9 seconds now. This one is just one second. Our fully row mode hash join plan is only one second. Pretty wild, isn’t it?

Now, it’s not because one of them gets a bitmap and the other one doesn’t. This filter operator is an optimized bitmap. There we are. Optimus bitmap-ius. And so we have a bitmap down here.

Remember, in these query plans, bitmaps, they get made up here, but then they get used down here. Right? So they go this way and get used to filter out rows here, usually. Sometimes they’ll get stuck at the partition stream.

Sometimes they’ll get stuck somewhere else. But most of the time, when you have a nice, effective bitmap, they’ll get pushed down from here to there. Thereabouts. You know, you can figure it out when you look at your query plan.

But this is a lot faster. And we don’t have the sort of same weird skew issue. At least I don’t remember having the same weird skew issue on this one. This one spreads all out.

So this one, the parallelism works better in the row mode version of this plan. That might not always be the case, but it’s certainly the case here. Now, if we run this query and we ask for this to happen in compat level 160, we should get a fully, a more batch mode-y version of this plan, where now we get actual batch mode on rowstore. So now we’re actually able to read once again from the votes table in batch mode.

And this gets us down to about under a second. So if we were to recap this a little bit, the query with just batch mode is about 1.8 to 2 seconds. The rowstore version of that query is 1 second.

But the fully batch mode-ed on row stored version of that query is about 800 milliseconds. So that turns out a lot better. Now, one of the reasons why Enterprise Edition is so important for batch mode workloads is because Microsoft, once again, I wouldn’t blame you if you use Developer Edition, hobbles the hell out of you in Standard Edition, where batch mode stuff is limited to a DOP of 2 in a parallel plan.

So a lot of times when I’m tuning stuff on Standard Edition, if you have a pretty big aggregation query that’s running at DOP 8, and you’re like, oh, we can try batch mode, you know, you’ll be pretty lucky if you end up with it running for like an equivalent amount of time because you lose like six DOPs if you’re running at DOP 8. So there’s a pretty big chance of a downgrade.

If you’re only running at like DOP 4 and you get downgraded at DOP 2, maybe less. But if you’re running at DOP 8 or higher, the DOP downgrade on Standard Edition with batch mode can be pretty painful. That’s why when like tuning Standard Edition stuff, it has to do big aggregations.

I’m far more likely to lean towards like indexed views and stuff like that because you don’t have those DOP limitations on indexed views. Granted, you have an indexed view, so you have a different set of problems. But, you know, an indexed view that has good indexes to help it, you know, get maintained is, you know, in the words of my wine distributor in New Zealand, it is no more overhead than another nonclustered index.

So, you know, hopefully that works out. Now, there are all sorts of things. I mean, say there are all sorts of things. There are like literally two things you can do to trick SQL Server into using batch mode.

One of them is what we did up above where we had a temp table with a clustered columnstore index on it. And we did a left join on 1 equals 0 to that temp table. And SQL Server smelled a columnstore index.

And it was like, ah, maybe batch mode. Another thing you can do if you’re not free to rewrite queries in that way is to create an index like this, non-clustered columnstore index that’s filtered.

And if you filter it to something that cannot possibly exist, then this index will not actually contain any data, but it will exist on the table as a columnstore object and give SQL Server some, you know, some impetus to use batchy, modey stuff.

You know, and if you’re, you know, tuning sort of regular things, you know, supporting indexes, you know, they’re good things. They’re there for a reason.

You know, they can help you with your joins and they can help you with your filtering and other stuff like that that are useful. And sometimes creating indexes might be, like, you know, the final answer, but the batch mode stuff is usually pretty useful as well.

So anyway, just a few notes on, you know, using batch mode, testing different stuff out when you’re trying to tune kind of bigger, crazier queries and query plans. Granted, this one wasn’t big and crazy, but that parallel merge join plan, man, that just set me off.

And I had to do the rest of this stuff because I feel like if I don’t talk about how awful parallel merge joins are, no one will. They’re truly despicable creatures.

I wish that they would be removed from query optimization, just to be honest with you there. So anyway, a little bit of a hodgepodge there, but again, stuff that interests me. Hopefully it interests you.

If it doesn’t, you don’t need to watch this. You can go do something else with 20 minutes of your life. It’s okay. Either way, my feelings won’t be hurt. So anyway, I hope you enjoyed yourselves.

I hope you did find at least some parts of this interesting. I hope you learned something, maybe a few things, because this was, again, a potpourri of knowledge. Yeah, what was I going to say?

Anyway, if you like this video, I like thumbs ups are good, as well as, you know, comments that don’t make me cry. At least, I mean, tears of joy are fine, but like tears of sadness. We don’t want sorrow, Eric, on these videos.

If you like this sort of SQL Server content, you can hit the subscribe button. It’s apparently a bell-shaped object. And you can join nearly 3,762 other data darlings.

Never going to be able to say that with a straight face. Who have subscribed to this channel and apparently find me interesting enough to keep getting notified when I’ve talked about SQL Server. So anyway, thank you for watching.

I do apologize for the audio issue on Friday’s video. There was no fixing it. Like, once it’s done, it’s done. So I just had to re-record the whole thing. I had lots of funny jokes about Justin Timberlake getting arrested in a tuxedo.

And you should have been there. It was great. Anyway, thank you for watching.