Tag: Query Plan

Stop Worrying About Duplicate Statistics In SQL Server

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

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.

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

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

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.

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

---

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

---

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.

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!

Register for Data Saturday, on September 7th here!

Going Further

---

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

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

---

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

Erik Is Not Here Today

---

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

Thanks for sizzling!

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

---

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

---

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.

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

---

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.