Performance Tuning Batch Mode HTDELETE and HTBUILD Waits In SQL Server
Thanks for watching!
Here’s the demo query, in case you’re interested in following along.
SELECT
u.Id,
u.DisplayName,
u.Reputation,
TopQuestionScore =
MAX(CASE WHEN p.PostTypeId = 1 THEN p.Score ELSE 0 END),
TopAnswerScore =
MAX(CASE WHEN p.PostTypeId = 2 THEN p.Score ELSE 0 END),
TopCommentScore =
MAX(c.Score)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id = p.OwnerUserId
JOIN dbo.Comments AS c
ON u.Id = c.UserId
WHERE EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE v.PostId = p.Id
AND v.VoteTypeId IN (1, 2, 3)
)
AND u.Reputation > 10000
AND p.Score > 0
AND c.Score > 0
GROUP BY
u.Id,
u.DisplayName,
u.Reputation
ORDER BY
TopAnswerScore DESC
OPTION
(
RECOMPILE,
USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'),
USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')
);
Video Summary
In this video, I delve into batch mode performance issues in SQL Server, focusing on weight types that can indicate potential problems. Erik Darling from Darling Data kicks off by discussing the nuances of batch mode execution plans and how they differ from row mode, particularly when dealing with columnstore indexes. He highlights the importance of understanding memory grants and cardinality estimations, which are crucial for optimizing query performance. I then walk through a specific query plan, pointing out where things go awry due to misestimations and spills, and introduce HT wait types as key indicators of these issues. The video explores two potential solutions: one involving the use of temporary tables to contain misestimations, and another from Joe Obish that leverages derived tables for more efficient row handling. Both approaches aim to mitigate performance bottlenecks in batch mode queries, offering practical insights into optimizing complex SQL Server operations.
Full Transcript
Erik Darling here with Darling Data, the cup of just coffee. You know what, I’m going to refrain from, try to refrain from drinking this while I’m recording because I don’t want anyone to suffer hearing me eventually swallow a coffee while I’m on microphone. An awful noise. I’m going to get on one of those conference calls where, like, someone doesn’t mute themselves and he doesn’t hear anything. hear them, like, constantly slugging away from a 64-ounce growler of water and it’s just gulping the whole time. Drives you bonkers. Drives you bonkers. Worse, worse than the, the low, low battery fire alarm, dogs and kids in the background. You know, it’s just someone completely unaware of how gross they are. Anyway, today’s video, uh, we’re going to talk about, uh, uh, a weight, well, a group of weight types that can be indicative of batch mode performance issues. Uh, and I’m going to start by showing you around the query, some of the indexes that I have, uh, the query plan. Then I’m going to show you the weights and I’m going to show you some ways to potentially fix those weights. Now, the weights that I’m talking about today, I’ll start with the letters HT.
There are four or five of them that all, uh, belong to the, uh, batch mode operations in a SQL Server query plan. Of course, batch mode most common when there are columnstore indexes involved. And batch more, batch more is usually a pretty great thing, especially for queries that process a lot of data can be far faster than row mode. Um, not always in standard edition because standard edition is hopeful, hope hopelessly hobbled by Microsoft. Uh, and you only see a lot of data. You only get a dot of two with, uh, a maximum dot of two with any batch mode. Uh, well, I mean, indexing query plans, query operators. Uh, so you can, you know, uh, sometimes you’re better off doing other things in standard edition, uh, that, that still allow you to have a higher dot. Uh, at least for me in standard edition, I’m a pretty big proponent of, uh, of, of, of indexed views. Uh, because indexed views are really good at doing, uh, pre-aggregations of large amount of data, large amounts of data, uh, but they, that they have no dop limitation the way batch mode.
So, uh, uh, a topic for another day, but, you know, just figured I, I give you as much information as I can in these things so that, uh, you don’t walk away feeling, you know, feeling like I robbed you of your time. So let’s talk a little bit about this query plan in front of us. Uh, the, the parts that are slow, why it’s slow.
So, and, uh, then we’re going to look at the weight stats behind this one. Now, this query, uh, at least, you know, for the most part is pretty quick up until we get to these hash operations. And I guess I didn’t need the sort in there because I just said the hash operations and then I circled a sort operator too.
So in batch mode execution plans, uh, where they differ from row mode execution plans is that, uh, rather than parent operators, uh, also, uh, sort of showing the time that their child operators executed for. So, like, if you have an index scan that takes a second and then the next thing you have is, like, uh, like an aggregate that takes two seconds in row mode. Those, like, the aggregate took one second and the scan took one second, but the, but the aggregate shows you two seconds because it’s the one second it took plus the child operator below it.
In batch mode, it’s different. Because in batch mode, every operator is just responsible for its own time. So really, this plan is pretty quick until we get to a hash match inner join and the hash match aggregate.
Now, these operators spill. We can tell that they spilled to disk because they have these helpful little exclamation points on them that says, oh, dear, something terrible has happened. Please pay attention to me.
And you can kind of get a sense of why this happened. Because, uh, when SQL Server came up with this query plan, it came up with another, another set of things that it said, that it said it needed. Aside from the operators here, it came up with a memory grant.
And it asked for a memory grant based on the cardinality estimation that it did when it was first figuring out, like, where do operators go? So the memory grant that it asked for was way too small for the amount of data that ended up flowing through some of these. Now, you can kind of start to see where things fall apart cardinality estimation-wise pretty early on in the plan.
So let’s frame this above here, and you can see at this operator, we got 383% more rows than we expected. At this operator, we got 426% more rows than we expected. At this operator, we got 4,400% more rows than we expected.
At this operator, we got 1,500% more rows than we expected. At this operator, we got 573% more rows than we expected. And at this operator, we got so many more rows than expected that the number is cut off.
But if you look at the scale of these two numbers right here, you can probably get a sense of, like, you know, wow. Orders of magnitude, whatever those are. There are a lot of extra digits in this number that are not in this number.
So they’ve got way more rows than anticipated. And so we just didn’t have enough memory to process all that stuff without spilling to disk. This one doesn’t do too bad.
But, you know, it still spilled, right? It still took a minute and 43 seconds just on its own of spilling, right? That’s what this number is right here.
So this took a minute and 43 seconds, and this took a full minute. So these operators did not do well. Now, we failed these operators pretty miserably. So while this thing is executing and taking, where is it, two minutes and eight seconds to finish, what it is primarily waiting on, aside from parallelism waits, which also suffer because of this.
Now, so, again, we can, not again, this is the first time I’m saying it. We can tell this is a parallel plan, but we can tell by looking at little operator icons here. These are my little racing stripes, right?
My go fast buttons. Parallelism is also hurt by things like spilled for a couple reasons. One is thread synchronization, and the other is that, you know, like when we were talking about memory grants, what happens is SQL Server, when it comes up with a memory grant, it comes up with a memory grant when the plan is a serial plan.
Parallel plan exploration happens later, assuming that your query passes the cost threshold for parallelism, and nothing is keeping it from going parallel. So what SQL Server does is it takes the memory grant for the serial plan and divides things evenly amongst the dot threads that get assigned to the parallel plan.
So what you have is a bunch of parallel threads, you know, that need to, like, do some stuff in memory, spill to disk, read back from disk, then go and process stuff. So you can see parallels and waits get really, really weird in any plan that spills because you might have one thread that spills, you might have four threads that spill, depending on what your dop is, you might have eight threads that spill, which is the case here.
So all of these threads have to wait to sort of do their parallel synchronization stuff while they’re doing the spilling and reading back from disk, which is all sort of just a generally bad time for those parallel threads.
So you would see parallel waits get really, really high, potentially, for queries like this where parallel threads are all spilling because the spilling hurts the parallel thread synchronization and whatnot. But the waits that really drive this query crazy are all these HT waits.
HT build, delete, memo, repartition, re-init. Now, being honest here, you know, I showed all the HT waits, but these bottom three hardly get impacted.
The real ones that hurt in this plan are HT build and HT delete. Now, if we look at, okay, let me frame this a little bit better. There we go.
So if we just focus on these top two waits, I’ve done some math on a DMV that is available in SQL Server 2016 plus called DMExecSessionWaitStats, which breaks, which has for any like open session doing stuff that hasn’t closed out, whatever, it holds on to wait stats at the session level.
So you can really monitor what’s happening for a particular session weight-wise while the query is running and after it’s done. So this particular query, it didn’t have a lot of waits on those, right? 672 on HT build, 1008 on HT delete.
But look how many seconds and minutes we racked up in the two minutes that query ran for because those parallel threads were all like emitting these particular waits.
And these parallel threads were all dealing with spills and other nonsense. Now, you don’t have to have big spills in a batch mode query plan for these waits to show up. They show up primarily, well, actually, I think they show up specifically for hash operations.
So hash joins and hash aggregates is really where these waits start to show up from. And you don’t have to have spills for this to happen. You can get these just from like terrible misestimations in your query where, you know, more rows show up.
Even if you don’t like, you know, run out of memory and spill the disk, you can still hit these waits pretty hard in queries where there’s just bad estimates. So we, during the two minutes that query ran, I think this is the most particularly interesting column in there.
There’s one other really interesting one, but the wait time in minutes, six minutes of HT build and five minutes of HT delete while that query was executing for two minutes.
If we look at the max wait time in seconds for some of those, 29 seconds for a max wait time, right? That’s seconds.
29 seconds we waited on this wait, like once. Like that was the max wait time that we had for it. And ditto the HT build below it where 37 seconds was the longest recorded wait on that wait, on the specific wait type while that query was running for two minutes.
So pretty wild stuff in there, right? So again, coming back to, again, these, these waits, not, not your friend when they start piling up in this way.
And coming back to the query plan, you know, we have a lot of stuff where misestimates are happening. Now, there were two ways that, or there are two ways that I’ve come across to sort of solve for this.
One of them was, I mean, mine, this was, this was mine. And the other one was from Mr. Joe Obish, who I showed the query, because he’s great with, with batch mode stuff.
And so I was like, going on with these waits, he came up with a slightly different rewrite that is a little bit slower than mine, but doesn’t use a temp table. So we’ll forgive Joe, we’ll forgive Joe by a few seconds on that.
So what I did was I took the initial join between the users table and post table with the exist check on the votes table. I stuck that into a table called pre-check, and then I went out and I joined off to the comments table afterwards. Now, the reason why this, this rewrite made sense to me is because when I was looking at this query, what kind of happened is like whenever, every time, like no matter like how I did this, or rather with any demo query that you write using the Stack Overflow database, as soon as you join from users, like two posts or two comments or something like that, there’s like this real, like amplification because users is one-to-many with posts, users is one-to-many with comments.
And when you join out, you get two one-to-many relationships. And then the one-to-many, like the two one-to-many relationship joins just kind of make everything go wild. And they make cardinality estimation probably really difficult, especially join cardinality estimation.
Now, I tried both the legacy cardinality estimator and the default cardinality estimator. Neither one provided a whole lot of help for this particular set of joins. So, you know, no shining example of one being vastly superior to the other there.
So, in my head, what I thought made sense to do would be to sort of self-contain a little bit of the misestimation. So, using a temp table, we still see some, we still see the sort of bad estimate row explosion when we come here at 369% and here at 816% and here at 1,544% and here at 6,166%.
But we stabilize that result set in the temp table here. Now, SQL Server knows there’s 13,000 rows in this temp table. And since we use the temp table instead of a table variable, SQL Server is able to generate some statistics on this.
This doesn’t fix everything, though. This doesn’t fix all of the cardinality estimation down here because when we go and look, SQL Server is like, yep, 13,000 rows here, got that.
But the cardinality estimate down here still stinks and the cardinality estimate here still stinks, but it’s self-contained, right? It’s not like affecting other parts of the query plan because it’s off by less than it was in the original query plan where it’s off by like some giant number because there was just order of magnitude, several of those more rows coming out of one of the joins than SQL Server was expecting.
So we can contain things a little bit with a technique like this. Another thing we can do is, oh, what’s in this window? Oh, that’s a different thing.
We don’t have to worry about those last two windows. I was like, I wonder what’s going on there. This is what I get for not saving files and naming things. So that was my attempt at a rewrite. Joe Olbich’s attempt at a rewrite is this one where he sort of, rather than using a temp table, he uses two derived selects in this way.
So in the first one, he joins from users to posts with the existence check here and takes care of all that. And then he does an inner join down here between users and comments and does all the right grouping and stuff in there and then joins the two derived tables back to each other.
And Joe’s plan, for what it’s worth, and we can still see where the misestimations hit, but again, they’re a little bit more self-contained than they are when it’s just, you know, when all the joins are just sort of done in one step of things.
We get some rows eliminated because we do a join from users to posts, and then in another chunk of the query, we do a join from users to comments, and so we get some row trimming down in those portions.
So Joe’s whole thing takes about 6.4 seconds, which is pretty sweet, right? It’s much better than the two minutes and eight seconds, nine seconds that the other one took. Pretty brutal.
But you can kind of see what Joe had going on in here. Let’s make this a little bit bigger so we can frame things up a little bit more nicely. But you can see where Joe does his join between users and comments. And this is, again, very instructive of how the optimizer works, where even though we wrote this part of the query in the, like, the inner join, the derived inner join after the first part that joins from users to posts, SQL Server did this part first.
So the optimizer was like, I have a feeling about this. We still get the bad estimates. And this isn’t Joe’s fault. And this isn’t anyone’s fault but SQL Servers. Holy cow.
All this weird stuff starts happening. All these estimates are really terrible. But they’re a little bit more self-contained, right? And we get some row elimination from doing each join separately in sort of derived queries because we’re able to eliminate some rows that would have just kind of been flying through the query plan when the original query where everything was just written in one straight shot.
So there’s a lot of bad estimates still going on in here. But this plan actually recovers pretty nicely from them. These aren’t even real numbers anymore, I don’t think.
It’s just insane stuff. So Joe’s query does pretty well there, I think, for not using a temp table. My query that uses the temp table, oh, that’s 6.4 seconds.
My query that uses the temp table is, oh, just a little under 2 seconds, about 1.3 seconds there and about 500 milliseconds there. So just a little 1.8 seconds, 1.9 seconds if you really want to do some extra math.
I want to do. We just want to sit here with our plain cup of coffee. Gloat a little bit.
So, yeah. When you’re working with batch mode queries, and again, I love batch mode for a lot of things. This query in row mode is an even bigger disaster.
But when you’re working with batch mode queries, if you see lots of weights within your batch mode queries on these HT weights, specifically HT build and HT delete, you can run really bad performance issues sometimes.
And one way of fixing them is to sort of help SQL Server make better estimates, eliminate more rows, use a temp table, rewrite the query to sort of isolate different parts of it a little bit better.
And you can see really big performance improvements when you do that sort of thing. So, be careful out there. If you’re tuning an individual query, always make sure that you’re looking at weight stats in the query plan.
Those are available if you right-click on the root operator and you go down here and, you know, typical Microsoft SSMS 1, 10, 2, 3, 4, 5, 6, 7, 8, 9. The 10 is right where it belongs, obviously, right?
That’s a smart sorting there. That bubble sort? That bumble sort. Hard to figure out sometimes. But SQL Server will show you the weight stats for your query.
There’s HT build. There’s HT delete. Should be a CX port down here if we zoom in. But here’s our top two. And these, of course, in milliseconds.
So, you have to do your own math out there. If you’re looking at a server that processes a lot of batch mode stuff, you’re looking at the server-level weight stats, then you should really keep an eye out for the HT weights, especially if there’s a long average milliseconds per weight or long max weights recorded on those.
Because they can be indications that you have some hash operations and batch mode plans causing some big performance problems. So, keep an eye out for that stuff.
I think that’s probably good for today. Or for now, anyway. It’s about 20 minutes in, which is longer than I expected. But, you know, perhaps it’s a nice gift for someone out there. Thank you for watching.
Hope you learned something. I hope you enjoyed yourselves. If you liked this video, please give it the old thumbs up. If you don’t, I don’t know, go collect rocks, I guess. There.
You produced something. If you like this sort of SQL Server performance tuning content, subscribe to my channel. I publish this stuff fairly often. And I’m also rebooting my…
I mean, I hesitate to call it a podcast because it’s really just sort of me talking with Joe Obish about SQL Server stuff. But you can call it whatever you like. But I’m reviving that.
That’s the Bit Obscene Show. Radio Show. Let’s call it a radio show. I don’t like podcasts anymore. It’s a Bit Obscene Radio. So, anyway, keep an eye out for those. Keep an eye out for more videos.
And I will see you in another video at another time. Not today, though, because I’m losing sunlight. 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.
One thought on “Performance Tuning Batch Mode HTDELETE and HTBUILD Waits In SQL Server”
Comments are closed.