In this video, I dive into the world of T-SQL by exploring `UNION` and `UNION ALL`, two powerful set operators that often confuse SQL Server developers. I walk you through practical examples using tables filled with datums (or numbers), demonstrating how `UNION` returns unique values from both result sets while `UNION ALL` preserves all rows, including duplicates. We also delve into the query plans generated by these commands, showing how SQL Server processes and deduplicates data to optimize performance. Whether you’re new to T-SQL or looking to deepen your understanding of set operators, this video offers valuable insights that will enhance your skills. If you want to explore more, consider purchasing my presale course for just $250—significantly cheaper than the full price after summer!
Full Transcript
All right, it’s time to learn T-SQL with Erik. That’s me. This is the teaser content for my learn T-SQL course. Again, this is all in the presale price of 250 bucks. It’ll go up to 500 bucks when the advanced material is written. So that’ll be after the summer. So if you buy it now, you’ll spend less money than if you buy it later, which is a very, very good deal. Right? Just like, just because. Anyway, in this video, we’re going to talk about union and union all because everyone sort of gets this weird and wrong and there’s nothing worse than being weird or wrong. Nevermind being weird and wrong altogether. So let’s, let’s play with some tables here, shall we? Let’s, let’s create a couple of tables with some datum in them, right? Datums, you know, whatever, whatever you want to call them. Ah, that’s a good sign. Connection is, connection is not recovered, possibly recovered. What happened? I don’t know. I probably restarted my VM. I don’t know. Don’t, don’t, don’t mind me.
Anyway, let’s look, let’s actually turn on query plans here and let’s look at what datums are in these tables. And we’ll see that in the first table, we have the numbers one through five, but there are duplicates, right? There’s two ones and two twos and two threes and two fours and two fives and a rogue zoom it being weird on me, right? But we have doubles in here. And then in this table, we have nearly the same data, except we have added two sixes to the end. Oh dear me. What are we going to do? What are we going to do with these extra, these spare slack sixes running around?
Well, the fun thing about union is that union will return unique values from both tables, right? But, but only, it will be fully deduplicated down to just, just unique rows. So if we run this query and we look at the results, we just get the numbers one through six, right? The numbers one through five were in both tables, but we deduplicated that down to just one, two, three, four, five. And then in the second table, we had two sixes, which got narrowed down to just six. So there doesn’t have to be a match between the two tables to union or union all, right?
Like that, like matching the rows doesn’t matter. It’s all just about like returning unique rows from both result sets. Now, if we change, we actually just look at the query plan for this real quick. We’ll see that we scanned each one of those tables, right? They’re just tiny little temp tables. So there’s not really much worth putting an index on.
And then we concatenate both result sets together. And because we have 10 rows here and 12 rows here, then we end up with 22 rows here. But then we have this distinct sort kick in and that brings us down to six rows, right? So this is where the deduplication of everything happens from both inputs.
We can change those queries a little bit, right? And this is logically the same query, right? If we say select distinct union and then another select distinct, union would already do the work of making both result sets distinct. But we’re going to see a slightly different query plan here.
If we run this and we look at the results, we’ll see that we got the same thing back, right? We got the numbers 1, 2, 3, 4, 5, 6, just like last time. But the way SQL Server went and did this is a little bit different.
Notice here that we have two distinct sorts where SQL Server actually before we went and did this merge join union, right? And this is where SQL Server broke things down to just six rows. So we start with 10 rows here. We start with 12 rows here.
We make each of the result sets unique for itself, right? We get down to five here and we get down to six here. But then this just returns the six unique from between those two, right?
So remember, the first one is going to be 1, 2, 3, 4, 5 after we distinctify it. The second one is going to be 1, 2, 3, 4, 5, 6 after we distinctify it. So when it goes into the merge union, it’s 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 6.
And then the merge union takes care of the rest. How you see, how this query plan shows up is going to depend on what your data going into things looks like. If you are selecting a primary key or if you are like another unique column or you put like a literal value in there, you may not see the early sort distinct or other aggregation that would remove duplicates or like do any grouping on the first result set.
So like this really depends on what the data looks like before when like when you apply that when you say select distinct from it, it really depends on what that data looks like and what guarantees SQL Server has about that data. So you might not see this, but if you do, it might be kind of a reasonable performance benefit to think of this because rather than making the final operator do all the distinctification, right? Making like using union to make each thing unique, you might get like we’ve talked, like I’ve talked about in a million videos.
Sometimes early aggregation pays off because you break up the work of doing aggregates that SQL Server would do later over a much larger result set. So sometimes this can pay off from a performance point of view, even if logically both of these like either doing union or select distinct union select distinct is logically the same as select union select, right? We don’t need the distincts because the union implies that everything will be unique.
Sometimes the distinct or like group by or something else going before we do the union would be useful from a performance point of view. And of course, union all does not do anything to remove duplicates. If we run this query, we will get everything back, right?
I’ll fight like from both tables, four ones, four twos, four threes on down until we get down to two sixes, right? So that is everything, which of course is different from saying select distinct union all select distinct, which would just bring us down to two of each because we remove one from each result set. But then the union preserves duplicates coming out of here.
So we end up with one, one, two, two, three, three, four, four, five, five, six, six. And we can sort of validate that looking at the query plan where we go from 10 rows here and 12 rows here down to five rows here and six rows here. But then we have this concatenation operator that just brings the two results together and gives us 11 total rows, which is, of course, one, one, two, two, three, three, four, four, five, five, six.
Anyway, not a whole lot to talk about with union and union all, at least in the teaser material. So if you want to learn more, you should buy the full course, which would give you every opportunity to learn more. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video where we will. I think we’re going to continue talking about these set operators. I think the next one is we got intersect, except is not distinct from. We got some fun stuff to talk about.
So I will see you over in those videos next. 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.
In this video, I dive into some essential T-SQL techniques by discussing pivot and unpivot operations, key components of data transformation in SQL Server. Starting off, I explain the basics of pivoting data, showing how to transform vertically-oriented results into a horizontal format using aggregate functions like COUNT. I highlight the importance of using derived tables to maintain accurate results when working with pivots, emphasizing that pivots can be sensitive to implicit grouping and can produce incorrect outcomes if not properly managed. Additionally, I cover unpivot operations, demonstrating their forgiving nature compared to pivots and how they handle null values differently. By walking through practical examples, I aim to provide a solid foundation for anyone looking to manipulate data effectively in T-SQL. This content is part of my new course, “Learn T-SQL with Erik,” where you can find comprehensive beginner-level material available at a pre-sale price of $250, with the option to purchase advanced modules later this summer.
Full Transcript
Erik Darling here with Darling Data, and we’re going to do some more teaser-y content from my new course, Learn T-SQL with Erik, in which I teach you all of the terrible things that I know about T-SQL, which is unfortunately a lot. Don’t worry, there’s a workaround. So all 23 hours of the beginner content is out and available. You can start watching that now. The course is available at the pre-sale price of $250 bucks, and that goes up to $500 buckaroos when the advanced material drops after the summer. I do have some things that I must attend to and then get that all wound up for you. So anyway, let’s talk in this video about pivot and unpivot because we might as well talk about something, right? As we’re doing a video about T-SQL. Now, most of the queries that you write will return results that look something like this, where the results are vertically presented and we have a column called type and a column called total type, which I have, of course, aliased as the count of things in here. And then in this column, we have what, you know, the text value of what each type of post is named and account for them. But there may be times in your life when you need to sort of flip this data on its side. And when you do that, that is when things like pivot are very helpful. So what I’m going to do, and I’m going to explain why I have this, this query in a little derived table in a moment, but we’re going to start with this query, which is sort of the foundation of the query up here, just without any grouping or aggregations. And then we’re going to, so we’re going to alias this as PT. And this is going to be, I guess, because it’s like post types or whatever.
And then we’re going to pivot, which we’re going to alias is PV down here. This is our pivot. And what we’re going to do is say, we are going to count post type IDs for all of the post types for all of the things that we saw in that first query, right? So question, answer, wiki, tag wiki, excerpt, tag wiki, moderator, nomination, wiki. I don’t know what half these things are, to be honest with you. But those are all the things that we saw in here, right? All of these things, all this text, we put that down in our pivot. So for all of these types of things that we have in our type column, we are going to count the IDs so that we can figure out how many of these things have occurred. And when we run this query, our results look a little bit different, right? We are no longer working vertically. We are now working horizontally.
And the way that my, I forget what grade I was in, but I had some teacher explain it, where vertical, the V makes an arrow, right? So the V from vertical makes an arrow going like down, or I guess it could, if you flip it, it would be going up as well. So vertical is up and down and horizontal, right? Like this is going across. So that’s how I remember horizontal and vertical in case you need to know where I’m at.
But now we have these horizontal results where each column is labeled with the count for each of the things. So question, answer, wiki, tag, wiki, all this other stuff I don’t really care about. Now, the reason why I put this part of the query into a derived table is because pivot is very, very sensitive. It has this sort of implicit grouping thing going on. So if we just wrote the query like this, and we said, select PV dot star from post types, join to posts on yada yada, and then we pivoted this stuff, we would immediately start to see very, very different results.
And this query would immediately take a very, very long time. I’m going to cancel the execution there because these results are clearly incorrect. But what I am going to do is I’m going to scroll over this way just to show you that the pivoted columns do still come out of these results. But because we are grouping by like every column in the post table now, we are getting woefully incorrect results for all of these.
Like we see some of these are questions and we see some of these are answers, but we are not getting the grouping that we want. And you do have to be very careful of this. So like let’s let’s write the query just in a slightly different way. And what we’re going to do is have a derived table now that selects this stuff.
But now we’re going to look at the score of each one of these things. So we’re going to put score in here and we’re going to group by ID and type here. Right. And when we when we do this and we we write our pivot down this way and we say to sum the score. Right.
Because that’s going to be our aggregate that we that we pivot on. And then we say where type in all that other stuff. The results are going to look a little weird at first. Right.
Like if we if we run this, what do we like? This looks just bizarre. Like we’re just like doing this like stepladder thing down here. And we have all these nulls in the results. And the reason why we have all these nulls in the results, because we have we have selected too many columns in our derived table. If we quote out this ID column, right, we’d say don’t even bother getting that. Right.
We’re not putting we don’t want that anywhere. And then we rerun this. Now we get the sum of scores just without all the nulls in there. So you do when you’re writing pivot queries, you do have to be very, very careful about, you know, what like what you’re doing.
It’s very rare that the table that you are trying to pivot things from is limited to all the columns that you want to group by. And nestling things in a little derived table like this can make your like can make your pivot queries return correct results, which is fantastic. What we want, correct results, sometimes, most of the time anyway. Right.
I mean, we got no lockins everywhere, but hey, we didn’t return any nulls. So be very, very careful, even when you are even when you are using the derived table to only put the columns that you really need grouped by and everything in there. There is, of course, some alternate syntax that you can use if you want to do something very similar.
And we could what we could say is just select a sum and then put a case expression into. Oh, go away, you silly tip. Go and then just put a case expression.
We’ll say, hey, for questions, if post type ID equals one, then add a one. Right. So just sum up that. If it’s not a one, then just add a zero. So we don’t add more to that because we don’t need to do anything.
But this will give us the same thing in there, too. Right. So this will this is equivalent to like the count query that we ran before. So we could, of course, you know, rework this to do the thing with the score above.
But just to give you an idea that there is a different way of writing these queries. There’s not a very much of a performance difference between the two because they both just result in one sort of scan of the post table and then a grouping to do all this stuff. If we come back up to the original pivot query that we ran, we’ll see just about the same thing where it’s just one sort of scan.
Well, this, I mean, obviously has to hit the post types table, too, but it’s just one scan of the post table to do anything. So pivot versus the crosstabs thing, like, you know, there might be some performance difference depending on what you need to do with the queries. So, you know, of course, test both ways if your performance is a big concern to you.
But if you just need to write the query, sometimes, you know, once you at least remember the pivot syntax, it can be a little bit less mentally taxing than writing out all these summed case expressions. Unless you like formulate it with Excel or Dynamic SQL or something. So the next thing to talk about is unpivot.
Now unpivot is a lot more forgiving when it comes to the implicit grouping thing. We don’t have to worry about that so much. The one thing that we do care about with pivot, though, is that pivot will explicitly get rid of nulls. So, like, if we look at what this values clause is returning, there are one, there’s one null in each row, basically, right?
Null, null, null. And if we run this query with the unpivot syntax and we get, we look at the results, we’ll see that we do not retain those null values. SQL Server explicitly filters nulls out of these results, right?
It says no nulls in here. So if you want nulls, you have to write the query a little bit differently. We’re going to use our old friend cross-apply with the values clause. And we are going to put the columns that we want to unpivot on in here like this, along with an alias for them.
And then we want to, or with a name for them. And then we are going to alias the values as call and callValue the way that we have in the table, or rather in the values clause. And then when we run this, you’ll see that this one retains the nulls from the results.
You can, of course, filter those out if you add an aware clause that says where u.call is not null. That’s this thing in here. So we can, of course, get rid of nulls doing this.
But just the basic way of cross-applying values to unpivot things will keep nulls in. So anyway, that’s just about enough pivoting and unpivoting. Again, this is teaser material.
This is a shortened version of the full module of pivot and unpivot. I hope you enjoyed yourselves. I hope you learned something. And I will see you over in the next video where we’re going to talk about more T-SQL fun learning activities. 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.
Can you dumb down what SORT_IN_TEMPDB does? I’ve read the documentation many times, but I don’t get it. Index building uses tempdb even when I turn it off.
My devs use ORMs very heavily. They let them write WHERE IN @Foo and the ORM will convert it to IN (@Foo1, Foo2, …). Is it worth bullying them into using table-valued parameters instead or am I just walking a path that ends in temp tables?
In Query Store I have one query that has two planss. It’s just one Nested Loops join, but one is really fast and one is really slow. What should I look for? They seem identical.
I still don’t get RCSI vs SI. Do I need both? What’s really different?
I want to buy your training but my boss thinks I can learn everything from ChatGPT. What should I tell him?
Video Summary
In this video, I dive into some user-submitted questions on SQL Server topics ranging from index building and TempDB usage to query optimization and isolation levels. Whether you’re dealing with ORM-generated queries or trying to understand the nuances of read committed snapshot isolation (RCSI) versus snapshot isolation (SI), there’s something for everyone. I also address the ongoing debate about whether to invest in my training or rely on AI tools like ChatGPT, highlighting the importance of human validation and technical depth that AI currently struggles to provide. If you have any questions you’d like answered live, feel free to submit them through the provided Google spreadsheet. Whether you’re looking for performance tuning tips or just want to chat about databases in general, I’m here to help!
Full Transcript
Erik Darling here with Darling Data and it is brutally blistering hot outside here in New York. It is like a hundred and something degrees. So if I seem brutalized by external sources, you ain’t wrong. Anyway, we are going to do office hours right now. This is where I go. I answer five. Count them five. That’s all the fingers God gave me. User submitted questions to my little Google spreadsheet. If you want to ask a question for one of these office hours episodes, you can go to this link and you can read some stuff and you can ask a question. If you feel like supporting the absolute insane amount of incredibly high quality SQL Server content that I produce and provide, you can sign up for a membership, you can sign up for a membership. All of all of these things that we’re talking about here are also down in the video description. So if you don’t feel like transcribing from your screen or just guessing how to do something, you can, you can always look to the video description for helpful clickable links. If you need, if you need help with your SQL Server and you’re like, boy, this Erik Darling fellow sure seems like a cool cat. Maybe a cat in a hot tin.
I’m not a cat in a hot tin roof today, but I, of course, am a SQL Server consultant. That is where I make the majority of my money from. I do all this stuff and do it better than anyone else. So we got that. If you would like to buy my performance tuning content, you can do that too. You can get all 24 hours of it for about 150 US buckaroos with that discount code there. Again, full link helpfully assembled for you down in the old video description. Another thing that I have available that you can spend money on in some, some, you know, fun way is my new T-SQL course. Learn T-SQL with Eric. That’s me. All 23 hours of the beginner content is currently published. The advanced stuff is coming after the summer. When it cools down, my brain stops frying. This heat has got right out of hand, as a wise woman once said. If you are coming to pass and attending the T-SQL pre-cons that Kendra Little and I are putting on, you will, of course, get access to all of this companion content for with, with your admission to the pre-cons.
Just a reminder, this is the pre-sale price of 250 bucks that will be going up to 500 bucks when everything is done. So get it while it’s hot or get it while it’s cheap, as they say. Man, do I wish I was out of the house right now? Maybe not in any of these places. I think everywhere is hot. I am going to be on the Red Gate Roadshow Tour this summer. That’s the Pass On Tour dates. New York City, Dallas, and Utrecht throughout the summer and I guess a little bit into the fall months.
All leading up to, of course, the big old Pass Data Community Summit taking place in Seattle this November. So I highly suggest you, if you are in the areas of any of these things, that you show up and we hang out and talk about stuff you’re into. It doesn’t even have to be databases. You can just talk to me about whatever.
Quite frankly, I’d rather talk about anything other than databases. Anyway, let’s answer some questions. And let’s zoom on in here.
And here’s our first lucky person. Oh, you lucky, lucky people. Can you dumb down what Sort and TempDB does? I’ve read the documentation many times, but I don’t get it.
Index building uses TempDB even when I turn it off. So rather than try to dumb down what Sort and TempDB does, because it’s hard to imagine anything dumber than the documentation, let’s focus on why your index building might still use TempDB.
Probably the most obvious one is that you’re using online equals on. That would be the first thing that requires TempDB. The second one would be, if you ever look at the query plan for the index that you’re building, you will most likely see a Sort operator in there.
And if that Sort spills, that Sort will spill to TempDB. So the Sort and TempDB is not talking necessarily about the physical Sort and the query plan, which you can’t help but Sort spills to TempDB regardless of what you choose for a TempDB option.
There might also be some stuff in there if you have lob data involved. But aside from that, I think the online index build is probably the most reasonable explanation for why you are still seeing TempDB usage, despite, I mean, Sort and TempDB being turned off your index build options.
All right. My devs use ORMs very heavily. My rates are reasonable.
I’m just going to say that up front. I’m here. Like, this is all me. So they let them write where in at foo, and the ORM will convert it to in at foo, foo to.
Is it worth bullying them into using table-valued parameters instead, or am I just walking a path that ends in temp tables? So it really depends on what your problem is.
Is there a problem with performance of the end clauses? Is the problem that they generate varying end clauses that all generate different query plans? Are you fighting a plan cache issue here?
If you’re fighting a query performance issue, I sort of doubt. If you’re fighting a plan cache issue, then the table-valued parameter thing will be useful. But if you’re fighting query performance issues, then the table-valued parameter would probably not get you anything all that much better.
It’s just like the lack of column-level histograms on table variables, which back table-valued parameters, would probably lead to some oddities in there. I suppose the nice thing about table-valued parameters is that the table cardinality does get sniffed a bit like a parameter would, which can be good or bad depending on just how many rows are going in there.
But it could be a case, too, where your data distributions are not so rough that using a table variable would hurt you. But, you know, again, without knowing what exact issue you’re facing, it’s a little hard to tell you if it’s worth bullying them into using them.
Of course, temp tables are the solution to many, many paths in SQL Server. So it would not surprise me in either case if you ended up using temp tables instead anyway. So maybe just get them into temp tables.
I don’t know. This might be a thing there. Let’s see what we got here. Oh, I love this question. In query store, I have one query that has two plans.
It’s just one nested loops join, but one is really fast and one is really slow. What should I look for? They seem identical.
Ah, so what you should look at is the nested loops join operator. Just take your MIS and right-click on the nested loops join and hit properties. Over in the properties tab that opens up in Management Studio, you’ll probably see one to two differences in there.
You might see that one nested loop says optimized true and one might say false. That might be one thing that you would see. But the important thing that you will see, the big thing that I find in these cases is that one of them will be using some form of prefetch, either ordered or unordered, and the other will not.
The prefetching optimization happens when SQL Server estimates that 25 or more rows might go into the loop join. And if you get a case where fewer than 25 rows, so like if it’s a, like the easiest way to think about it is maybe a parameter sensitivity thing.
It could also be a table like statistics thing. Or, you know, it could be a table variable. Well, I guess not.
Well, maybe table variable depending on if you’re on 2019 and all that table variable deferred compilation stuff. But, yeah, most likely one is going to have a prefetch optimization applied to it and the other is not. So that would be what I would look for there.
Depending on what you find or depending on what is driving the cardinality estimation there, if it’s a parameter sensitivity thing, you might try optimizing for a value that always gets you 25 or more rows so that you get the prefetch optimization.
Because most likely it’s going to be that the prefetch plan is the faster one. If it is a temporary object, you know, you could always try using some slightly out of warranty stats updates commands to change the row count on your temp table or something.
But, you know, again, my rates are reasonable. All right. I still don’t get RCSI versus SI for the folks at home. That’s read committed snapshot isolation versus snapshot isolation.
Do I need both? What’s really different? Do you need both? No. But you might find a use for both depending on if you’re asking this question, you probably don’t need both. If you’re asking this question, what you probably want is to just turn on read committed snapshot isolation and start removing all those pesky no lock ins.
The real difference is in statement versus transaction level consistency. And the easiest way to think to use hand gestures to tell you what that means is let’s say that this is a begin transaction and a commit, right? The other begin transaction and your commit up here.
And let’s say that there are two select queries within this begin transaction and commit. If you are using read committed snapshot isolation, this is the biggest difference to people who query it. This is not the only difference.
It’s like snapshot can obviously do stuff with updates and there’s conflicts and stuff that can happen. But the most common thing that people who write queries are going to be concerned about is going to be select query stuff. So we have this begin transaction up here and this commit transaction down here.
And we have two select queries. Under read committed snapshot isolation, this first select query will return any versioned rows that it needs to as of the time when that select starts. The second select query will return any versioned rows that are required when that select query starts.
That’s a little bit different under snapshot isolation where, like, you know, again, same deal, like begin transaction, commit, and two selects. This select will run and return any under snapshot isolation. This select will run and start returning any required versioned rows here.
And then the second select will actually use the timing of when that first select accessed versioned rows to figure out when it should return versioned rows from. So it’s statement level consistency, which is our CSI, right, where each statement is consistent. And then transaction level consistency, which is snapshot isolation, which is consistent with when the first query within the transaction accessed versioned rows.
So that’s the that’s the big difference for most people who write queries. All right. The fifth and final question, a humdinger.
Wow. Oh, wow. I want to buy your training, but my boss thinks I can learn everything from chat GPT. What should I tell him? Well, I think the easiest way for me to describe it is that I am a person who produces training.
Right. And when when LL was, you know, there I mean, when LLMs came around and up until today, I have tried to use them at various points to, like, you know, bounce some ideas around because, you know, it’s just me sitting. I am a solo individual person.
And I don’t have anyone to, like, talk to about stuff that I want to do, really, except myself. And so, you know, I’ll talk to the robots and I’ll be like, hey, I want to I want to I want to make some new training. Like, you know, I want to bounce some ideas around.
And I can’t even get chat GPT or like any of the other LLMs to, like, do a good job of, like, outlining good training concepts. They just come up with the worst crap, like like the nonsense gobbledygook that you see people like say over and over again on like LinkedIn, like most prolifically. Like, that’s all they come up with.
They don’t come up with like anything like good or like in depth. They don’t come up with anything that like beyond like surface scratching. And like, you know, they’ll say, you know, 10 things and three of them will be wrong. But like they’re just mixed in with all the other stuff that’s like, you know, either mostly or kind of right or just like situationally right.
So like even for me, like I go through it. I’m like, oh, well, I can’t use this. I can’t use this.
This is wrong. Like skip this. Like so like as a person who creates training content and I like who knows kind of like what good is the stuff that they come up with when I’m trying to get them to like like listen to me about things is not good. So like youth like for a person who needs the training, you would get you would get quite a miseducation in a lot of ways if you were to purely use an LLM unless you have the time to not only like like pester the LLM about stuff, but then go and like validate and verify everything that it’s saying.
So often like the value of the training is that, you know, it’s like mine, like like like I like I write it. I validate the hell out of it, like with all the stuff that I do. And also I get it tech reviewed.
So like I have Paul White like tech review my training so that I make sure that stuff is extra correct. So like like it’s like I’ve already done unpackaged and like like validated and votes for everything that I can like in the stuff that I produce. A normal person doing that would spend way, way, way, way longer getting a lot of stuff wrong and having to fix it.
So I, you know, if you’re if your boss thinks that the chat GPT knows everything, I mean, rude awakening when when that happens. You know, one thing that I tell people is that, you know, if you are if you think if you’re that confident in it, then you should have a conversation with an LLM with something that you. You know, deeply that you like like understand and you you have like a like a great grasp on and see how often it’s it’s you have to you.
You go, oh, no. Oh, that’s not that’s not it either. So, like, you know, that’s pretty much what it comes down to for me.
So, no, like I’m sure that you can, you know, get some stuff out of it. But as far as like, you know, the the depth and the the validation and all the like like the technical correctness of it, that that still does require a human touch. Anyway, thank you for watching.
Hope you enjoyed yourselves. I hope you learned something and I will see you in the next video and of course the next office hours. All right. 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.
It’s Time to Get Rid of the Cost Threshold for Parallelism Setting.
Video Summary
In this video, I delve into the outdated and largely unnecessary `cost threshold for parallelism` setting in SQL Server. This setting has been around long enough to have outlived its usefulness and relevance in modern database management practices. I explain why relying on a fixed number for this setting is not only ineffective but also misleading, as it can lead to suboptimal query execution plans and resource overutilization. I argue that the focus should be on optimizing queries to run faster rather than obsessing over cost estimates, which are merely pre-execution metrics with no actual meaning in today’s computing landscape.
Full Transcript
It’s time to get rid of the cost threshold for parallelism setting. It is a setting that has long outlived its usefulness and even longer outlived its meaningfulness as far as how SQL Server should be considering the benefits of using parallelism when executing a query. There are far too many people out there in the world that think that there is some hidden perfect setting for their workload when there isn’t. They refuse to acknowledge that settings like this are just good, you know, general guardrails for things. They are not a fully bulletproof way of making sure that your workload always does the right thing. You might see people say you should start at 30 or 50 or 150 or 500 and maybe figure out what you should set it to based on the type of workload that’s running. But it really is all magic. It’s all magic number thinking and it’s really annoying to watch people chase their tails on this. It only ever meant seconds on one computer and it is not your computer. It is a unitless metric. If anyone ever called me a unitless metric, we’d probably end up in a fight. You shouldn’t be looking at unitless metrics to tell you how your modern workload should execute. You might think that there is some magic number thinking.
You might think that there is some magic number, but then find that really tragic optimizer costing issues leave important queries with a serial execution plan and options for forcing a parallel execution plan, either trace flags or use hints are all unsupported. And that is something that desperately needs to change, but that’s a topic for another day. Remember that max stop is not min dot. Max stop limits how many CPUs a query is. Can use does not instruct SQL Server. How many CPUs to use. So setting max stop will not force a parallel query. Likewise, you might, you might also, you know, set cost threshold for parallel parallelism in a way that, you know, not only prevents some very important queries from getting a parallel execution plan, but you might also set it in a way where too many queries end up getting parallel execution plan. Because they no longer qualify for exploring the parallel execution plan.
You may also set it too low, which is also in some handy quotes there and find that you’ve run out of worker threads because now everything is going parallel. And that six core server that you threw all the hardware in the world at is just completely overwhelmed by parallel queries all running at some dop or another. One of the, some of the, some of the most, some more frustrating things about it are that it puts way too much emphasis on a very, very flimsy data point. Uh, something that a lot of people, uh, something that a lot of people either refuse to acknowledge or it just never occurs to them or they have never read or listened to anything I’ve said is, And this is like maybe the most important thing in here.
Cost is an estimated pre-execution metric. When you look at your execution plans, there are no actual costs. There are estimated costs for everything.
There are estimates for a lot of things. And for some things, there is even an actual component that gets added after you execute a query. When you look at the execution plan, there will be things like actual rows and actual executions and other stuff like that.
There are no actual costs. So people start paying way, way more attention to operator and subtree cost when they’re trying to tune things. And it makes them think that a meaningful goal is reducing cost when it is not.
Reducing cost is not the goal of tuning a query. If it happens to be a byproduct of tuning a query, that’s fine. But it’s not a query tuning goal.
Your query tuning goal should be to have that query run much faster. Reducing cost is not a way to do that. You might have very high cost queries that run very quickly. And you might have very low cost queries that run very slowly.
There are a variety of reasons for that, of course. But cost is not an indicator of execution time. And in today’s world where compute is at a premium due to licensing, people will start paying attention to these cost things like they’re going to save them money.
That’s what’s going to drive costs down. You might as well be stuck in 2008 looking at logical reads or some other dinosaur metric. You might as well look at PLE or buffer cache hit ratio or, I don’t know, like context switches and disk queue length and other stuff that has just gone the way of, you know, just obsolesion.
One thing that I dislike about it that is, you know, maybe not the biggest deal in the world is that it tends to give people a very false sense of control. Sure, you can change this number whenever you want, right? It’s a thing that you, it’s a knob that you have control over.
You can change it to 49, 50, 51, 62, 75. You can go up, go up as higher, as low as you want with it. But you can’t change how the optimizer costs things internally, at least not in any supported way.
There are some, there are some DBCC commands that you can run, but, you know, most people aren’t going to start running those on their production servers just to see, see what if this and that happens. It can also be very confusing to the same people who are confused by a lot of, a lot of things in SQL Server who are just not experts or who have never spent much time with it. Or, you know, maybe they spent a lot of time with it, but, you know, they’ve been, they’ve been taking the backups and doing the index rebuilds for years.
And that’s, that’s their, that’s their single point of experience. They’ll see parallel plans with a cost that’s lower than their cost threshold for parallelism setting and think that SQL Server is broken, right? But that’s not true.
What, what, what, what no one gets is that every plan starts out as a serial execution plan. If that, if the cost, the estimated cost of that serial plan is higher than your cost threshold for parallelism setting. And SQL Server starts looking at parallel plans.
There are no natural inhibitors to a parallel execution plan, like a non-inlineable scalar UDF or an insert into a table variable or something. Then SQL Server will start looking at these candidate parallel execution plans. And if it finds a cheaper one, then it just might go with that.
After, you know, factoring in CPU reductions and whatnot. But, but you might end up like if, let’s say that your cost threshold for parallelism is 10. You might see a parallel execution plan with a cost of 8 or 9 or 1 or 5 or 0.
Because the parallel plan was cheaper than the serial plan. Well, while we’re on the topic of parallel plan costing, nested loops queries get an absolute screw job on parallel plan costing. Because the costs for anything that happens on the inner side of the nested loop are not applied any reduction.
But often nested loops queries benefit quite a bit from parallelism. Right? Serial nested loops queries at a certain point just drive the mind, just fries your brain. We’ve come to the point with SQL Server as a, let’s call it a mature software product.
Where we have enough stuff going on that falls under the intelligent query processing umbrella of features. There are many things that will happen or not happen based on other heuristics. I think probably, probably the easiest one to recognize in that category is batch mode on rowstore.
SQL Server will use various heuristics, heuristics about the type of query, the joins, the size of the tables and things like that to see, to figure out if batch mode on rowstore might be a good thing for you. And batch mode on rowstore leads to all sorts of other neat things like adaptive joins and whatnot. We’re at the point now where there is almost no sense in taking user input on what query cost should be before a query goes parallel.
There might be a good candidate setting to replace it with, but it might also at this point just be completely replaceable by some intelligent query processing feature that just uses heuristics. Again, similar to what batch mode on rowstore does to implement or explore the parallel query plan space. And there are also many feedback mechanisms where one might look, where the optimizer could look at the query plan and then the engine could execute the execution plan.
And then we could look at things after the fact and say, well, you know, we didn’t think parallelism would be good there, but we ended up with a lot of rows on a single thread. And boy, howdy. Maybe, maybe more threads would help.
We actually already have a setting called dop feedback, which is, I mean, plum useless the way it was designed. But, you know, I wasn’t the PM on that. So don’t blame me.
So there are things that would be better to do at this point, right? Like setting, like leaving this cost threshold for parallelism setting, like, you know, A, out of the installer, right? Because, like, there are so many other things in the installer now.
You can set maxed up in the installer. You configure 10 dB in the installer, right? There’s, like, stuff, like, you can even turn on, what’s it called? Like, perform volume maintenance hash, like the lock, the instant file initialization thing. You can turn that on in the installer now.
But they leave cost threshold for parallelism out of the installer. And by they, I mean Microsoft. There’s, like, nothing in the documentation that gives people guidance or anything on it. You can’t change it at all in Azure, right?
So who knows what Azure is doing, right? Azure SQL database, you can’t change it at all there. I mean, you can change it in managed instance. You can change it, like, if you have a VM. But Azure SQL database, you can’t change it there.
So perhaps there is some scientific exploration going on about a better way to gauge the relative benefits or drawbacks of parallelism for queries there. I don’t know. I don’t have any information on that.
But I just have to hope that at this point we can finally either drop it, drop cost threshold for parallelism as a setting completely, or we can finally start to give people some meaningful thing to do with it. The thing is, I just don’t think that there is a routinely meaningful thing that you could tell people that they should do with it.
They would solve their problems. Anyway, I’m done here. It’s Friday.
I’m going to go think about this and, I don’t know, stare at some red stuff in a fancy glass. 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.
In this video, I delve into the intricacies of batch mode friendliness in SQL Server queries, sharing practical examples and strategies to optimize your query plans. Starting with a common annoyance in a query plan, I demonstrate how certain operations can hinder batch mode execution, leading to suboptimal performance. By exploring alternative query structures and pre-aggregating data, we achieve more favorable execution plans that leverage batch mode effectively. The video also touches on the importance of understanding where batch mode excels and aligning your queries accordingly, providing valuable insights for anyone looking to improve their SQL Server performance tuning skills.
Full Transcript
Erik Darling here with Darling Data. And boy, I got a video for you today. We’re going to talk a little bit more about batch mode friendliness. I don’t know, these seem to do fairly well as far as peaking interest from people. So what the hell? Let’s do it some more. Before we get into that, the usual ol’ spiel schnitzel. If you want to support the content that I produce on this channel, you can sign up for a membership. It’s a good deal. It’s a good deal. Because then I’ll keep doing it and not just retreat into a cave and keep it all to myself on stone tablets. If you want to ask me questions from my Office Hours episodes, there’s a good link for doing that down in the video description. I have enhanced the link to give you more opportunities to add detail to your questions as well. If you need consulting, you know, consulting, looking at your SQL Server and you’re thinking, gosh darn, this thing is slow. I promise you, I can do all of these things and I can do them all well. And according to Beer Gut Magazine, I do them better than anyone outside of New Zealand. So you can hire me. And as always, my rates are reasonable. You can get my performance tuning training for about 150 US dollars.
That link and that discount code also fully assembled for you down in the video description. And of course, my new T-SQL trainings. The beginner content is fully published. There is about 23 hours of it. If you’re going to pass and attending Kendra Little and I’s pre-cons, you will of course get access to all this material. It is on pre-sale still at 250 bucks. But after the summer, when I have regained consciousness, the price will go up to 500. So I would really suggest that you get in on these purchases now. They will be far less negotiable than the everything bundle. And of course, this summer, I am also traveling a bit. The nice folks at Redgate have decided to pull me kicking and screaming from my home where I do these recordings. I will be in New York City, which I guess is not too far from home. August 18th and 19th. Dallas, Texas, which is moderately far from home. September 15th and 16th. And then Utrecht in the Netherlands, which is slightly more moderately far away from home than Dallas, I guess. And of course, you know what? Now that I think about it, Utrecht and Seattle are kind of about the same flight wise.
So I will also be slightly more moderately far away from home for past data community summit in November, the 17th to 21st. With that out of the way, though, let’s talk about some batch mode stuff here. So the first query that I want to show you has kind of an annoyance in it, right? We’ve got it. We’ve oh, oh, dear. I don’t know what button that was. Did did zoom it have a problem? Are we not? Are we not doing the zoom it show here? The zoom it show works now. Great. Some reason SP who ran and that just should never happen.
We’ve got a little bit of an annoyance in this query. Part of our join clause is looking for where user ID is no or we’re giving SQL service say I really want to join on this. But if the user ID column is no on the comments table, we can we can we can leave that in. The thing is, and I can only get an estimated plan for this because when I when I’ve tried to run it and get an actual execution plan, it is horrible.
So what we do in this query plan is nothing very batch mode friendly. We scan the clustered index of the post table and the comments table. And we we sort the entire post sort the post table. I mean, not the entire table. We sort the columns that come out of the post table. But, you know, we’re selecting, you know, owner user ID, post type ID and score.
And we are ordering by post type ID ascending. And then we go into a nested loops join nested loops joins no batch mode there. Uh, lazy table spoons, no batch mode there. Uh, stream aggregates no batch mode there.
Uh, these rocking with rowstore, right? We are not getting anything batchy at all in this plan. Uh, worse is if I tried to tell SQL Server, I would, I would prefer a hash join here, my friend, uh, we will get an error back. You’ll get this, all this red text telling us that the query processor could not produce a query plan and that we should resubmit the query.
Well, okay. We could do that or we could, could, could try some stuff, right? Now, what I find interesting about this particular query format is not so much the SQL Server can’t figure out like, like how to use batch mode or how to use a parallel plan or how to use a, uh, hash join plan. What I find interesting is that if we look at the post table and the owner and the, and the look at where owner user ID is null.
And we look at the comments table to see where user ID is null, which is effectively measuring the, or rather, uh, storing the same type of data, right? It’s like whoever owned the comment or whoever owned the post, there are no nulls in the post table that we have a zero there. And we have 336,000 null user IDs in the comments table.
Very interesting. So if we change the query to look like this instead, right? If we say, uh, where owner user ID is null and, uh, you see that user ID is null.
Uh, we can at least achieve a hash join plan here. However, this query will run for a very long time. It is not a good time.
Uh, we do, uh, was this the one? No, this is not the one. So, uh, there is still no sign of batch mode, uh, in this plan until we get to the very end where we have, uh, some batch mode on this final hash match aggregate. The thing is that we do all this other work in row modes and that’s, that is not exactly what we want.
We want more batch mode happening in our plan, not just one operator. That’s, it’s not terribly helpful. So, uh, what we can do, or actually before we do that, what I want to show you is without the hash join hint, I want to show you, uh, potentially one of the most deeply offensive query plans that you might ever see in your life.
Uh, and this is all row mode. Uh, I can promise you that, uh, there is a scan of both tables. Uh, then we repartition streams again, no batch mode here.
And then SQL servers like, Oh, a merge join. Yeah, great. I have no sorted input. So I’ll just sort both of these inputs. I’m just going to sort both of these to use a merge join worse, worse, worse, worse.
This is a many to many merge join. Uh, and, uh, I, and I tried running this one too. This one ran for about 30 something minutes before I was like, you know what?
Um, I think, I think I just need to go record something at this point. Cause I’m getting tired. I’m starting to get exhausted. So we have this all row mode work.
And then of course we have our one loan hash match aggregate occurring in batch mode here. Uh, it’s not that none of the operators leading up to the merge join support batch mode. We just don’t get it.
Right. And like, like, like merge join, it doesn’t support batch mode. And again, like the parallel exchanges, like the repartition streams and the gather streams over here don’t support batch mode. But of course with batch mode on rowstore, uh, we do, we do have support for reading from tables and sorting and computing scalars or computering scalars in batch mode, but we’re just not getting it here.
SQL Server does not naturally choose a very batch mode is plan here, which is not good for, not good for batch coin. I’ll be honest with you. I’m not having a good time with that one. So, uh, what we can do in order to make this query more batch mode friendly is do some pre aggregating on our own.
One of the big downfalls of even this plan with the hash join hint is if you notice, uh, SQL server does not make any attempt, uh, prior to the join to like group data together. Like we have two columns, owner, user ID and user ID, which are lousy with duplicates.
There are so many duplicates in there. Just an incredible amount of duplicates. Right.
Like just wild with them. Um, but SQL Server does not make any attempt to do a pre aggregate. Like it just like, no, I want to, I feel like fully joining all these things together. I want, I want all the rows joined together.
This is going to be great. It’s not great. It’s not a good time. Even the hash join plan runs a very, very long time. Now what we can do is we can force SQL servers hand a little bit and we can do some pre aggregating of our own.
Uh, we still need to do some outer aggregating, but it’s okay. Cause it’s a little bit of pre aggregating that goes a long way. So for example, we can, uh, select this stuff and we can do a little bit of pre aggregation on some of our columns and we can do like a group by an owner, user ID and post type ID out here, and then we can join that to another pre aggregated result here where we group by user ID and then do our join.
And then finally out here, group by post type ID. And when we do that, now why is, why are you not properly terminated? When we do that, we get a much more favorable execution plan. I’m just going to show you the estimated plan first.
Where we do get much more batch mode. We do not get full batch mode the way that we might want, but we get much more of it. And the, like another thing that we get is of course, like using our smart brains, we, we, or at least we’re, we’re using some smart brains out there.
I mean, I don’t know. Some, sometimes I have good ideas. Uh, but when we look at this plan, right, where we do the pre aggregation SQL Server is like, oh yeah, I think I can use batch mode here.
And when I do this aggregate, oh yeah, I can, I can, I can do batch mode there. And, and, and, but I know there’s still no batch mode for the repartition streams, but, uh, SQL Server is still does some fairly batchy things leading up to, uh, all of the, uh, this work.
Now, unfortunately. Unfortunately, the hash join that we do is in row mode, but that’s not the end of the world here. We still see a really, really big performance increase, uh, and, uh, the estimated execution mode of that, this final hash join, which is now going to have like much less work to do.
Cause we pre aggregated a bunch of stuff before the join. We’re going to have, we’re going to be leaning on this thing much, much less because we’re going to have far fewer things to, to go and aggregate altogether. And, and finally, when we run this query, this one actually finishes.
Like it, it actually just completes without me having to do much. And the, like you can, you can see like, you know, sure. There’s, is there stuff we could do for this thing?
Yeah. Maybe, maybe we could add in some indexes. Maybe we could tweak indexes a little bit. Maybe we could even like add columnstore indexes. I don’t know. We could, we could do maybe stuff that might be useful here, but just like doing things that are more agreeable to batch mode, like, like doing like the early group buys and joining the results of that.
And like, you know, just things along those lines, like think like things that batch mode is into doing, which is like, you know, I want to group by this stuff and aggregate this stuff and get some sums and some counts and some other, like, you know, data warehousey analytically things.
Batch mode is just like, oh yeah, no, I get it. I’m picking up what you’re putting down. I think, I think, yeah, I think, I think I’d be useful here. Yeah. I’m going to get in on this game. So when we do this, like, like a lot more stuff, like I said, happens in batch mode.
The, the, the hash join does not happen in batch mode, but that’s, that’s okay. Because like, so, you know, going back to like reading operator times in these plans, all of the batch mode operators, like, like these, like, they’re just going to show the time underneath the operator in here for like the wall clock time for what they did.
So, you know, it’s like 416 milliseconds, 186 milliseconds. And then like, when we get to the, the repartition streams, which is in row mode, then we get like, like the total time spent added up until you get to here.
And that’s the same thing with this, this side of the branch too, since this is batch mode and this is batch mode and this is batch mode. These things are all sort of individual, all individual.
But like, like one thing I do want to repeat here is that like, like looking at operator times for a parallel exchanges is not something that you should spend a lot of time with. But the point is we don’t spend 1.5 seconds in the hash join.
It’s like 1.5 seconds minus like 500 and minus like, I don’t know, like 186 plus 416. So like, you know, there’s, there’s some stuff in there that, you know, is, is okay. Right.
It’s like, we didn’t spend 1.5 seconds here or here or here. Like we just like, it’s, it’s cute. It’s cumulative, but the whole query finishes in about just 1.6 seconds. So when you are trying to write batch mode, friendly queries, again, the, the, the closer you write your query to align with things that batch mode is good at, the better the chance you have of getting batch mode operators in your query.
So just, you know, try to think like, like a batch, right? Like think where is batch mode useful and then try to align your queries to that rather than just like, you know, writing your typical query and wondering why batch mode doesn’t show up.
You might, I don’t know, maybe you’re, you, maybe you’re like already a batch mode person like by accident. And the way that you write queries is very batch mode friendly. But, you know, like if you’re coming from like, you know, sort of like, like role mode query tuning stuff, you might be really into doing like, you know, like, like apply and things like that, where, you know, you’re like into like trying to find these like navigational seek-y strategies, maybe like getting like parallel nested loops pushed in there.
But when you’re trying to get batch mode happening in your query plans, you’re, what you really should focus on is truly thinking in terms of where batch mode excels and what batch mode is good at.
And then like writing your queries to try to conform to that. Anyway, that’s good here. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where I will say something that is, I don’t know.
I don’t think it should be all that, all that weird, but maybe, maybe you’ll think it’s weird. 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.
Spotting Batch Mode Opportunities in SQL Server Query Execution Plans
Video Summary
In this video, I delve into the world of batch mode opportunities in SQL Server, exploring how to identify and leverage these opportunities for performance gains. Starting off with a discussion on my upcoming consulting services and training courses, I share that you can support this channel by signing up for a membership or asking me questions during office hours. Moving onto the technical content, I analyze execution plans from previous videos where SQL Server missed out on using batch mode, even though it was clearly beneficial. By adding a simple columnstore index to a helper table and running trace flag 7418, I demonstrate how these changes can transform row-mode operations into batch mode, significantly reducing query times. Through detailed explanations of the execution plans and performance metrics, I aim to help you spot similar opportunities in your own queries.
Full Transcript
Erik Darling here with Darling Data, and we are going to talk in this video about batch mode opportunities. It’s kind of like the clash song, except way less catchy and cool, right? Anyway, my life, what am I gonna… Started out so promising. If you would like to support this channel, you can do that. You can sign up for a membership. If you would like to ask me questions for office hours, you can do that at that link. Both of these links are down in the video description. If you would like help with your SQL Server, perhaps you need help with your batch mode opportunities. I am available for consulting. You can hire me. I’ll show up. I will wear this Adidas shirt. And I will be just as clean and kempt as I am in these videos. Not drunk, and it’ll be fun. And as always, my rates are reasonable. If you would like to buy my performance tuning training, there’s 24 hours of it, just for you. Aren’t you special? Look at you. Special little thing you are. You can get all of it for about 150 bucks for life. You go to that URL and plug in that discount code.
And this is also helpfully assembled for you down in the video description. My T-SQL course, Learn T-SQL with Eric. That’s me! Is also available. All 23 hours, just about 23 hours of the beginner content is fully published. If you are coming to pass Data Community Summit and attending Kendra Little and I’s pre-cons there, you will, of course, get access to this material as I consider it companion content to what will be going on there. The advanced stuff is being worked on currently. That’ll all start going up after the summer.
And the other thing going up after the summer is the price. It’ll go up from 250 bucks to 500 bucks. So you should buy that now while it is still 250 bucks. Speaking of the summer, gosh, how am I going to get all this done? Red Gate is taking me on a partial world tour. You know, mostly small clubs and menus.
New York City, August 18th to 19th. Dallas, Texas, September 15th to 16th. And the Hamlet of Utrecht in the Netherlands, October 1st and 2nd. And that all leads up to Past Data Community Summit where the aforementioned T-SQL pre-cons, plural, will be taking place.
But with that out of the way, let’s talk about spotting opportunities for batch mode. Now, this was the query that I ran in the last video where I said, no, that default cardinality estimator sure didn’t do so good. And if we look at the execution plan for it, we’ll come back to that.
I’ve already run it because golly and gosh, why sit through that 8.5 seconds last time? I guess, I don’t know, maybe Windows Update wasn’t doing something in the background when I ran this one. This was 300 milliseconds or so faster, so I don’t know.
We got a speed boost from something. Sure wasn’t Microsoft. But looking at this execution plan, there are things that I do not love about it. For example, all of this stuff happens in row mode, like SQL Server.
It is the year 2025. We are in database compatibility level 160. You have, I’m using developer edition, which is an enterprise edition equivalent skew of SQL Server. The batch mode on rowstore feature is there.
It should, why wouldn’t you use it here? We are scanning 53 million rows just about. Why on earth would you leave this to row mode? What is on your mind?
SQL Server. Gosh. And it does it all throughout the plan. Another way that you can tell you’re not really getting batch mode on rowstore is because we still have these repartition streams operators. Now, sometimes these can still show up in mix batch and row mode plans.
But since none of the parallel exchanges support batch mode, we know that these operators are not happening in batch mode. Neither are these compute scalars. If you squint really hard or I zoom in like a reasonable presenter should do, we will see that these occur in row mode, as does this big old whopping hash join here.
Gigantic hash join. Huge hash join. Right?
52 million rows come in from each side. And what do we do? Row mode. SQL Server. Smackity smackity smack. What is on your mind, buddy? Let’s try this again.
Let’s give SQL Server some ideas about itself. Let’s say, hey, SQL Server. What might be a good idea here? Now, this table columnstore helper is a completely empty table. I’m just going to type in a demo real quick.
dbo.columnstorehelper. Just because I want you to see the execution plan here. We return no rows from this. The execution plan shows that we have a zero row clustered columnstore object in our database.
You can do this with a temp table or whatever other kind of thing you want to slap a clustered columnstore index on. But all we’re going to do here is say left join to our columnstore helper on one equals zero. One can never equal zero.
But there is now an object with a columnstore index on it somewhere in near or around our query. And so the optimizer is going to think somewhat differently about things. So if we run this, remember that was about eight and a half seconds, right?
We’re going to just go with it there. Now, what are we down to? 2.4 seconds. Jeez, SQL Server.
I think batch mode might have been a good call here. What do you think? What do you think? How do you feel about that one, SQL Server? Should we have used batch mode? Was that a good idea?
Well, probably. So now we have, I mean, we still have a scan of the votes table on both sides because we don’t have like a where clause on the votes table that we could like, you know, like filter rows and whatnot out. But you might notice that like, you know, this takes about 800 milliseconds.
Before, this is like 1.7 seconds. This takes just about the same time, right? What’s off by 20 milliseconds here. Not that big of a deal.
But, you know, especially considering what these times were before. And all of these things are happening now in batch mode, right? We have a batch here. We have a batch here. Yeehaw.
Look at us. Good job. Even our compute scalars are happening in batch mode. And now that big giant hash join that we were doing before in row mode with 52, almost 53 million rows coming on in from each side are happening in batch mode. Even this top end sort is happening in batch mode.
Now, like I said before, the parallel exchanges do not support batch mode. Boo. Neither does the top operator.
The top operator also does not support batch mode. So the plan timing in this one looks a little funny because, you know, for batch mode operators, which is like, you know, all this stuff, the times that you see in there are just the wall clock time spent in that specific operator. So it’s like 800, like in a row, in a rowstore query, it’s cumulative going from right to left, like the child ones build it up.
I’m going to show you a way to change that in a second. So these numbers, like these numbers are all just for the individual operators.
But by the time we get over here and we get to these row mode operators, these ones add up all the times for the stuff that happened before them. So the 2.4 seconds you see here and here is not 2.4 seconds a piece. You remember this whole query finished in about 2.4 seconds.
We can validate that by going to the properties and looking at query time stats and seeing that there was about 18 seconds of CPU time and 2.4 seconds of elapsed time. So that’s one good sign that your queries could possibly do with some batch mode is when you have gigantic scans of tables, especially in parallel, and big old hash joins. But they’re happening in row mode.
It’s usually not what you want, right? Especially if you have any say over it. Batch mode really helps because the more rows get involved, you know, row mode just does exactly what it sounds like. It processes a row.
Even though we’re not like using a cursor or a loop or something like that, iteratively, like inside, like this is why query plan operators are often called iterators. Because they are iterating over rows. And SQL Server in row mode pipelines all this stuff, so it’s like one row and one row and one row.
Granted, that happens pretty quickly because, you know, the people who made SQL Server were pretty good programmers or something like that. But batch mode is much faster here because batch mode processes up to 900 rows at a time depending on the size of those rows. Sticks all those rows on a CPU register and uses something called SIMD, which is single instruction multiple data, to run CPU instructions over batches of rows at a time.
Which, which when you have many millions of rows is typically a good idea because it removes all the CPU boundness from your queries. So let’s look at another example that’s, that builds on this example. So I’m using a trace flag here, 7418.
This one came out in SQL Server 2022. And what this, what this trace flag does, which is, you know, technically undocumented and unsupported. So, you know, don’t go messing around in production with this one because who knows, right?
I can’t tell you everything that it does in effects or even if like that might cause stack dumps or assertion errors and, you know, whatever else product failures. I can’t tell you. So for, for demonstration and testing purposes only, we are going to run this query, which builds on the query that we were just running.
So what this is trying to do is add in some more information about missing IDs in the votes table. Let’s say that we wanted to summarize all of the data that we, all of the missing ranges in here. So we want to find the range, like the start range of when things go missing and the end range of when things go missing.
This is a query that will do that. We have our ID plus one. We have our min ID minus one.
And we have our not exist query here in order to find the non-matching rows with our terribly non-sargable predicate here. And then we have this final, this final predicate on our query here in order to figure out like, like, cause we don’t want to get like the last value in the table because that like, that’s not actually a missing one. That just, we just don’t need that last bit.
So I’m going to run this whole thing at once. And the two things that I want to show you are one, I mean, the query plan is the most important part. We’re dumping this into a temp table.
So it doesn’t matter much what else we’re doing with it, right? We’re not, not doing anything else terribly interesting, right? We put 5.4 million rows into a temp table, but this execution plan also happens entirely in row mode. I know it’s a little hard to see here, but you know, when, like, like if you, if you just kind of understand, the pattern of the, like the, like what, what you’re going to see in these tooltips, everything is happening in row mode.
Even once again, this gigantic hash join between two tables and all the, all the work that gets done in here. So this is, this is like very similar. Like you might, so the important thing here is like most of the operators in this query plan are not eligible for batch mode, but a small segment of them in this query plan are, in this section of the query plan are like, like granted, like any of the like data acquisition operators, like clustered index scans are absolutely eligible, but you know, things like top and stream aggregate and the, the parallel, parallel exchanges, like repartition streams and distribute streams and gather streams over here.
Aren’t nested loops joined sure. Isn’t, but I wish it was, it’d be so cool if it was, boy, I wish we had batch mode nested loops. I don’t know.
Maybe that’s just really hard to do, but this whole thing once again takes, uh, well, this one takes a little bit longer, right? This one actually, if we go over to the very end here, let’s go look at the properties and let’s go look at the query time stats.
We’ve actually been lied to a little bit. The elapsed time on this was actually almost 14 seconds, 13.7. Why that doesn’t show up here, uh, appropriately?
Well, uh, you know, like I said, the, the, the, the, um, um, wall clock time on parallel exchanges is bonkers. Bonkers. Not in a good way. Not in like, this is going to be a fun night bonkers.
Like this is like, Oh, I’m getting arrested. Cool. So like you might have a query plan where the, like a lot of the operators are not eligible for batch mode, but you might still spot like this is, this is like, like the, the last one I showed you was very simplified.
This is like that same section of the query plan, but with a bunch of stuff around it. Cause I want to teach you what to focus in on, which is like this pattern in here. Thank you tool tip for showing up on, on, on, on uninvited.
So it’s like this pattern in here, like we spend, like, if we think about like the amount of wall clock time that we spend in this plan, a lot of it is right in here. Right. Like there’s a lot going on in here.
So let’s do what we did before. Right. So I have trace flags 74 18 on. So this, this query plan is showing all of the operators is only having the wall clock time of themselves. Right.
Like even the row, even the row mode ones are only showing like the wall clock time that they consumed. So that’s like, did I say that? I think I forgot to say that about the trace flag. That’s what this trace flag does.
It makes it so when you have a query plan, all of the operators in the query plan, uh, will only show the wall clock time that they are responsible for. So it makes row mode plans act like batch mode plans, uh, in the, in that timing regard. So like all the stuff that you see in here, you know, even though it’s not happening in batch mode, just uses that timing.
So let’s, let’s put this in now. Right. So we have like just a hair under 14 seconds for this. Right.
Remember this lied to us when we looked at the query time stats wall clock time was almost 14 seconds. So let’s put this in, right? We’re going to tag in our columnstore helper friend here and we’re going to run this and we’re going to, we’re going to see what happens. I spent all that time warning you about the trace flag without ever actually explaining what the trace flag does.
So this no longer takes, uh, this no longer takes almost 14 seconds. If you look at the query time stats for this, now we are down to six and a half seconds. And the, you know, like the, the sort of annoying thing is that like, like a lot of the, like the plan that we get, it reuses a lot of the operators from the last plan.
Right. Like we still have like this whole section is still identical and this whole section is still identical, but this section in here now is all batch mode. And we can tell it’s all batch mode because the repartition streams that used to be in here are gone.
So it’s a little annoying that we were like, Hey, SQL Server batch mode would be really cool to do here. Wouldn’t it? And SQL Server was like, I gotcha.
But then like we get a, we like stream aggregate does not support batch mode, right? Like this thing, like no batch mode, this thing. I don’t know. Like it could get batch mode, right?
Like it’s, it’s possible, but this, this thing, no, it just uses row mode. But up here, this scan of the votes table uses batch mode. Right.
This scan of the votes table uses batch mode, even though the storage is rowstore. Don’t get too, don’t get confused there. The compute scalars are both in batch mode and this hash join happens in batch mode. So we were able to at least affect part of the plan with that columnstore index being in there.
We didn’t get a fully batch, full batch mode on rowstore plan because batch mode on rowstore, of course, goes much deeper into query, like the, into like the query optimizer. Optimizer than just sort of tricking SQL Server into like, Ooh, you tripped and fell and landed on some batch mode. So like, like we got at least partial batch mode in here, which improve things, but we don’t get like the full batch mode experience.
There are of course, you know, further, there are of course ways that we could change this query to probably make it a bit more batch mode friendly. But that sounds like the subject for another video. That sounds like a great video, Eric.
So we’re going to call this one here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video where I’m, I have not decided what, what I’m going to do next. So it’ll surprise you as much as it surprises me.
All right. 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.
A Little About: Old vs New Cardinality Estimators In SQL Server
Video Summary
In this video, I delve into the reasons why I often prefer the legacy cardinality estimator over the default one in SQL Server. Using a practical example involving an identity column backfill process, I demonstrate how the legacy CE can provide more efficient execution plans compared to the new CE. By running the same query with both estimators and comparing their performance and execution plans, I highlight key differences that might influence your choice of cardinality estimator during query tuning. Whether you’re looking for a straightforward explanation or want to see real-world implications, this video offers valuable insights into when and how to leverage the legacy CE for better query optimization.
Full Transcript
Erik Darling here with Darling Data and we are going to talk in this video because I actually answered an Office Hours question recently where someone asked why I generally prefer the old cardinality estimator, old or legacy, compared to the new cardinality estimator or default cardinality estimator as Microsoft and all its blue Azure-y hubris calls it. And I’m just going to show you an example today of a query. Now, I know this is an example of one query, but it’s just a good example of kind of generally why I tend to prefer the legacy cardinality estimator and why when I am tuning queries and I am using a version of SQL Server and a database whose compatibility level dictates that we are using using the default cardinality estimator using the default cardinality estimator and using the legacy one. This is not, of course, a thorough undoing of everything that the new cardinality estimation model attempts to, you know, do differently than the legacy model, but it’s just an example of why I tend to prefer it and why I will always try it out. There are, of course, worst times in my query tuning life when I’m maybe using the legacy one and I might say, hey, let’s give the newer one a shot. Let’s give it a try. Let’s see how it goes. The worst thing that happens is that either the query finishes in the same amount of time or takes longer and we can say that didn’t work. Let’s try something else. So here we go with that. But before we do, of course, I mentioned Office Hours. If you want to ask me questions privately that I answer publicly, that link right there is how you do it. It’s down in the video description. There’s also a link where if you think that this channel is worth is as little as $4 a month, leaving your bank account and going into my bank account, you can sign up for a channel membership to support all of this wonderful material that I produce. I am also an acting SQL Server consultant active, maybe play play one on TV, play one on YouTube. If you need help with your SQL Server, you can hire me. And as always, my rates are reasonable. Hooray for reasonable rates. Anyway, my performance tuning training, if you want it for about 150 bucks for the rest of your life, that’s the link, that’s the discount code. The forming pattern here is that it is also in the video description.
My T-SQL course, Learn T-SQL with Eric is also available currently at a pre-sale price of $250. I recently finished recording all of the beginner material and am hard at work working on the advanced material now. So that will get done after the summer. The price of the video course will go up to $500 when that is complete. So I suggest you do that sooner than later. I am speaking a lot all over the place this summer. New York City, Dallas and Utrecht, August 18th to 19th, 15th to 16th, and October 1st to 2nd. Of course, all that is right before I go to Seattle for Pass Data Community Summit. Kendra Little and I will be delivering two T-SQL pre-cons at Pass Summit together over two days. So I hope to see you at both of those. But with that out of the way, let’s talk about this turkey here.
Now, let’s say that we have a table and that table and it has an identity column. Let’s pretend it’s called the votes table and let’s pretend it’s in the database called Stack Overflow 2013. Think that’s reasonable, right? The reasonable set of things that we can pretend. And we realize one day that our ID column is not as contiguous as we would like. And maybe we would like to go and backfill it. So we start designing a process to find all of the missing rows in the votes table. In this case, our job is to find the first, the lowest value that is missing from the votes table and then assign that to something and then do an insert to start backfilling rows in there.
Maybe that sounds a little silly, but I’ve seen plenty of places start needing to backfill their identity columns. And, you know, depending on various local factors, this might be a reasonable way of doing it. So if we just select the top 10 from the votes table and we look at the ID column, when compared to the row numbering column, right, this is not a column from the table.
This is just the row numbering that comes back from SQL Server Management Studio. We’ll notice about right here that things go a little amok on us, right? We are clearly missing ID 8, right?
We are like this goes 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. This goes 1, 2, 3, 4, 5, 6, 7, 9. Ah, boy, we’re missing ID 8. All right, so our goal is to write a query that will find us the earliest missing ID.
Sounds pretty easy. One way of doing that would be to write a query that looks about like this. You would say I want to select the top 1 missing ID, which in this case, because of what we’re trying to accomplish, we would need to add 1 to the ID column here.
And we’re going to say where not exists, select doesn’t matter from the votes table, where v2.id minus 1 equals v.id. And we will order our results by v.id to make sure that they stay perfectly deterministic.
Since ID is already the clustered primary key of the votes table, it is guaranteed to be unique. And since it is indexed, it is presented to us in order. Now, you might look at this and scream a lot about sargability and whatnot, and I hear you.
I hear you. There’s a lot to be said for sargability. But we’re going to run this query in two ways. Right now, my database is in compatibility level 160.
1.6.0 for SQL Server 2022. So I’m going to run this query once using compatibility level 160 and all its attendant properties. And then we’re going to run the exact same query the exact same way down here, except we’re going to add in this use hint to force legacy cardinality estimation.
All right? So that’s the only difference between these two things is one is using the default cardinality estimator, and the other one is using the legacy cardinality estimator.
You might notice that it’s been a little while since things started running. You would be a very observant person if you picked up on that. These both return ID8, right?
They both return the correct result, but the execution plans are quite different. So this top plan is using the default cardinality estimator. You’ll notice that it took 8.6 seconds right here.
And if we scroll over here a little bit, yeah, my head’s not in the way. We’re off to a great start, aren’t we? We spent 1.7 seconds fully scanning the votes table. We spent 2.2 seconds fully scanning the votes table here.
We’re going to ignore the timing on the repartition streams operators for now because the wall clock timing on parallel exchanges is a complete disaster. So we’re going to ignore that for the time being.
And we’re going to look at this. So this is just about this whole section in here is where 8.5 seconds winds up, right? It’s not like something weird happened over here.
Like we have this top end sort, but this top end sort wasn’t like spilling a bajillion, 52 million rows to disk. And we were like, ah, God, we can’t take it. It’s tempdb.
We broke tempdb. It’s not that. If we look down here at this query where we use the default legacy cardinality estimator, oh, dear old me, this query chose a completely different execution plan, right?
One, it’s single threaded, right? I mean, first off, you might want to know it’s right here. It takes one millisecond.
This is a single threaded execution plan. There is no parallelism at all in any of this whole entire thing. But there are some funny looking numbers.
Like, for example, 900 of 1, 4, 5, 4, 6, 0, 0, 0. So that’s eight digit number. That’s 14 million rows, 14 and a half million rows.
It only took 900 rows for us to find it. So the big difference here is if we look at the properties of the clustered index scan here, there will be this row, estimated rows without row goal.
So SQL Server estimated that it would have to read this many rows to get stuff out of there. But, you know, the number of rows that it took was 900, right? So the actual number, we only needed 900.
SQL Server was like, it might take a while, but we only actually ever needed 900. But the important thing here is that this exists here, right? So we have estimated rows without row goal here.
We have estimated rows without row goal for the second time we touch the votes table. But if we click on these up here, that estimated rows without row goal thing disappears, right? It’s not in here, right?
Even though we have a top in there, SQL Server used a top end sort up here. So this one just used a regular top. So some slight visual differences in the execution plans. But this is sort of in general why, again, when I’m tuning queries and I am using the default cardinality estimator and I get a rather suspicious looking plan, I say to myself, Eric, we should check in on that legacy cardinality estimate.
We should see how Legacy Cardinality Estimator is doing today. Let’s see. Maybe we can bring it some snacks or, you know, just go give it a call. Have a little chat with it.
See how it’s hanging in there. Because a lot of the times, even if you don’t see like a performance difference this drastic from like eight and a half seconds to one millisecond, you can at least, you know, get some feedback from it and see if there are any differences.
And, you know, sometimes you do see something this dramatic, just like I did. Again, you know, you can go on and on about sargability and subtracting one from something that you’re comparing here. But, you know, Legacy Cardinality Estimator just does a better job here.
So you might find this in your queries as well as you are going through and tuning things. And like I said earlier, you may also find the opposite is true sometimes. You may find that the Legacy Cardinality Estimator does a rather rotten job of things occasionally.
And you might find that testing out the new Cardinality Estimator will do a better job. If you want to test out the new one, what you can do is say force default Cardinality Estimation. And you can use that use hint to test your queries out using the default Cardinality Estimator.
But anyway, that’s enough for now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you over in the next video where I’m going to talk about… So I did a couple of videos sort of recently about writing batch mode friendly queries. And I realized one thing that might be useful for people would be a small bit of education on how to recognize query patterns where batch mode may be useful.
So we will do that. And one of these queries might even make an appearance in there. You might even see this exact same starting point.
So that’ll be fun for us anyway, won’t it? All right. Thank you for watching. All right.
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.
In retrospect, what was the best thing about SQL Server 2022? For me, it’s Query Store hints.
why does index cleanup replace unique constraints with unique indexes?
why table value parameters get a different estimate than table variavles?
why do you not care about logical reads?
I missed you at SQLBits this year. Will you be there next year?
Video Summary
In this video, I dive into answering five user-submitted questions during an office hours session, providing insights and solutions to common SQL Server challenges. We cover topics ranging from the best features of SQL Server 2022 to why unique constraints might be replaced with unique indexes in certain scenarios, as well as the differences between table value parameters and table variables when it comes to query estimates. Additionally, I explain my reasoning for not focusing on logical reads when identifying slow queries, emphasizing that duration and CPU usage are more telling indicators of performance issues. The session also includes a bit of personal reflection on upcoming SQL Server events and community summits, including Pass Data Community Summit in Seattle, where Kendra Little and I will be delivering T-SQL pre-cons. Whether you’re a seasoned DBA or just starting out, there’s something for everyone in this episode!
Full Transcript
Erik Darling here with Darling Data. Nice to see you too. Fancy meeting you here, all that good stuff. It is time for an office hours in which I answer five user submitted questions. I don’t know how many users actually submit these. It could all be one person. Or I don’t know. It could be five different people. Anyway, I hope that I answer your question this week so you don’t feel left out. But before we do, the usual the actual old song and dance, if you would like to support this channel, memberships are available. I have an unlimited supply of those. If you enjoy this content and you would just want to support my efforts to keep caring enough about doing it, you can sign up for a membership. Otherwise, you know, all the other stuff. If you want to ask me questions that I answer on these episodes, I have a slightly different URL up here now. This one, goes actually to my website rather than directly to the Google form because there is some additional information on the website about if you need to ask questions about code or execution plans and you need to share them. So I’ve changed the link there a bit and I suppose I’ll fix it in the YouTube videos as well. Or at least the ones that I, I don’t know. We’ll figure it out. Anyway, I’m available for consulting as well. I got an unlimited supply of that. Never seemed to run out of consulting, health checks, performance analysis, hands on tuning, dealing with performance emergencies, and of course, training your developers so that you don’t have any more performance emergencies. Good Lord, that’s quite a bit of service. And as always, my rates are reasonable. My performance tuning content, all of it 24 hours is available for 75% off, which means about 150 US dollars. You can you can of course go to that link and plug in that discount code to get the everything bundle over there. And if you want to pick up my new T-SQL course, which has all 23 ish or so hours of beginner content currently published, you can do that now for the pre-sale price of 250 bucks. That’ll be going up to 500 bucks after the summer once the advanced material lands.
And speaking of summer, boy, is it hot out. New York City, Dallas and Utrecht will all be graced with my presence over the summerish months with the Pass on Tour events. Redgate has decided that they’re going to smuggle me to various places to talk about SQL Server stuff. So that’ll be fun. Especially fun for you, I hope. And of course, Pass Data Community Summit will be in Seattle, November 17th to 21st, where Kendra Little and I are delivering not one, but two T-SQL pre-cons. So we’ll have a great lot of fun there. But with that out of the way, let’s do this whole office hours shindig. Let’s have some fun here. All right. Our first question. Let me, where is Zoomit? There you are. Where’s my little pink dot buddy? There we go. All right. In retrospect, what was the best thing about SQL Server 2022? For me, it’s query store hints.
I wish I had the same love and affection for query store hints and plan forcing. I suppose they’re great when they work, but it’s less fun when they suddenly stop working and you’re like, wait, what happened to the thing that I just told it to do? It worked for a while. Why is it not working now? And then you have to go do it again and kick plans out. It’s not fun. As far as SQL Server 2022 features, let’s see. I don’t know. That was 2019. No, well, that’s going to be in 2025. Gosh, you got me. SQL Server 2022. I suppose there were some decent linguistic improvements to window functions.
But like features, I don’t know. Let’s just let’s just throw it out there for for again. It’s cool when it works, but probably the parameter sensitive plan optimization is a nice, as they say, down payment on, you know, fixing quite a quite a pernicious issue in databases generally. So that’s that’s that’s about it there. 2022. Kind of a kind of a bummer. Kind of 2014 ish, kind of 2017 ish in that it’s it’s not very interesting generally 2025. I don’t know. All right. Here’s a good one. Why do index cleanup replace unique constraints with unique indexes?
Well, my friend, you’re you’re you’re referring to my store procedure SP underscore index cleanup. And the reason why it replaces unique constraints with unique indexes, which is only sometimes is if you have a unique constraint on, let’s say, column a to get, you know, real, real, real worldy there. And you have, let’s say, an either a unique or non unique nonclustered index on column a, maybe with other key columns or actually, no, not with other key columns. I lied. Other key columns would mess it all up on column a with like other included columns.
Then, then, then SP index cleanup will either make either either drop, give you a script to get rid of the unique constraint because unique constraints are backed by an index anyway. So if you already have a unique nonclustered index on that column with some includes, then like you don’t really need the unique constraint still. But if you have a non unique nonclustered index and what it’ll do is give you a script to make the non unique nonclustered index unique and also get rid of the unique constraint because it’s sort of a duplicative facility at that point.
So, um, like, you know, when, like, there is like, I guess a question that sometimes comes up, it’s like, oh, should I use unique constraints or unique indexes? And, um, you know, I, I do prefer the unique index because you have a bit more flexibility with the unique index than you do with just unique constraints as far as like included columns and like some other options go. So, so that’s, that’s, that’s about that there.
All right. Next up. Let’s see here. Um, got it. The same person, right? This one, uh, why table value parameters get a different estimate than table variables? Well, um, I’m going to guess you mean table variables.
Uh, so table value parameters are of course backed by, uh, table variables or rather presented to store procedures with table variables. Uh, but since they are presented to store procedures as parameters, uh, table valued parameters tend to get parameter sniffed the way that, uh, other parameters do. But, you know, it’s, it’s really only for like the table level cardinality.
So like you might find that you execute a store procedure with a table valued parameter. And when you pass it in with, let’s say like 10,000 rows, then you get a 10,000 row table cardinality, table level cardinality estimate from it. Um, and then it’ll keep that until you, you know, so recompilation occurs for whatever reason.
Uh, but then if you were to pass it in instead for first compilation with a table valued parameter that has a thousand rows in it, it would just use that thousand rows over and over again. So table valued parameters are a little bit different in that they tend to get sniffed like parameters rather than, um, you know, being treated like table variables, which, uh, the, the cardinality estimates that you get, get from those do depend a bit on, uh, version edition and, uh, database compatibility level among some other things. All right.
Hey, let’s look at this well, well, well-formed, well-structured question. Why do you not care about logical reads? Well, just because logical reads don’t tell me how long if a query was fast or slow. They don’t indicate that.
Uh, I want to find queries with performance problems. So I go looking for queries that, you know, uh, have a high duration and or a high CPU. Uh, so that’s, that’s it.
Logical reads don’t, don’t tell you if a query was slow or not. I want to find slow queries. So I find queries that use a lot of some mixture of wall clock or CPU time. I don’t know.
That, that, that seems, that seems fairly straightforward to me. Uh, anyway, let’s go on. Uh, oh, oh, how sweet are you? Hey, look at you. Someone, some lucky person.
Hey, wait a minute. Come on, zoom it. Uh, some lucky person out there got to go to SQLBits. I missed you at SQLBits this year. Will you be there next year? Another well-structured question. Good job out there.
Uh, I don’t know if I’ll be there next year. Uh, SQLBits has changed the way they do their pre-cons. And, uh, they, they are now curated. Uh, they curate the speakers.
I was not curated for this past SQLBits. So if you would like to see me curated, uh, for SQLBits, then you are, of course, you know, welcome to express that opinion to the SQLBits organizers.
I don’t know how much good it will do. I don’t know. I don’t know, uh, what their curation process is. But, um, I don’t know. Maybe, maybe there’s a cure for it.
Anyway, uh, that, that, that gets us through five questions here. Uh, they’re short ones this time around, I guess. It’s easy for me then. Uh, anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I will see you… …soon. Somewhere. Somehow. 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.
In this video, I delve into an interesting query optimization topic that has been relevant for years but is now less pertinent due to the advent of batch mode in SQL Server. I explore why certain complex query syntaxes were necessary before batch mode existed and how they can be replaced with simpler, more efficient methods today. Specifically, I demonstrate the carry-over sort method—a technique often used when window functions weren’t available—and contrast it with modern approaches using window functions. By running sample queries in SQL Server Management Studio, I illustrate why the old method is not only slower but also less accurate for certain scenarios. The video aims to provide insights into query optimization and help viewers understand how leveraging batch mode can significantly improve performance without resorting to outdated techniques.
Full Transcript
Over the years, there’s been a lot of very interesting and intricate query syntax ginned up to deal with row mode performance issues that don’t really exist anymore in a world where batch mode exists. One of them is called the carry over sort. I don’t know if you’ve maybe ever run into a problem like this or whether you’ve ever stumbled upon this problem. I admit that the name is a little outlandish, but it comes up a lot when you’re dealing with data analysis type work. And, you know, prior to there being batch mode, there was one specific way that you typically wanted to write queries if you wanted to sort of emulate what what a windowing function would give you for finding the max value for a row, not per column in a row, but just per row, that batch mode largely solves. So, I’m going to talk about that today. Once we get over to SQL Server Management Studio, I’m going to show you probably why the query you think that you want to write is not right. Just how slow, how the process of the process of doing this using row mode is and how batch mode improves that. But of course, we’re going to contrast that with the carry over sort method, the way that it has been presented. This is not my syntax. It’s something that I’ve run across and I just wanted to make sure that I was comparing it accurately to modern versions of SQL Server. Of course, you may still need the carry over sort method of doing this if you are using standard edition of SQL Server. The reason for that is because if you are using standard edition of SQL Server, at least as of the recording of this video, Microsoft still does not think that you have paid them enough to be your friend and they have deliberately hobbled anything involved in the way that you have paid them enough to be your friend.
there’s nothing to build. Yo, of course, otherwiseulanning you have final version of SQL flagship and will be rewrite your friend or of Spain. you have had made all the power졌uj assodo.com Be it would be an exact same way that you have posted positive feedback button to the world And that is now patreon.com buradazt neuron will понять how to run substitute feedback of how to run with optimal name in optimal name know if you want to contribute to this channel you can sign up for a membership link down in the video description for that it’s just a show of appreciation for all this cool content that i write and record for free you can also like comment subscribe and ask me questions for my office hours episodes if you need sql server can help consulting help boy howdy i do all this stuff and as always my rates are reasonable if you want to get all my performance tuning training i have 24 hours of it for about 150 us dollars uh you can in that in that lasts you for life you get it forever and always uh just go to that url plug in that discount code which is also a fully assembled link down yonder and you can you can you can start your learning today uh my new t-sql course uh the beginner content is all done and recorded it’s about 23 hours over 69 modules uh if you are going to attend kendra little and i’s uh t-sql past pre-cons you will get access to this material and uh right now the course is on pre-sale for 250 bucks it will go up to 500 bucks when the course is fully recorded after the summer i am hard at work on all the advanced material now so that isn’t that isn’t that spectacular for you how hard i work uh if you would like to see me live and in person i am going on tour with the red gate road show uh the pass on tour dates new york august 18th and 19th dallas september 15th and 16th and utrecht not just an art supply store it’s a hamlet in the netherlands october 1st and 2nd and then of course past data community summit in seattle november 17th to 21st so with that out of the way uh let’s let’s pratty or party whatever it is so i have uh pre-run a couple things here uh the first the first two things that i have pre-run are uh the the the the version of this query that a lot of people uh will write uh or maybe tried to write at some point in the in the far distant past and we’re unhappy with the performance of uh which is basically to uh select some stuff within a CTE and most importantly in that CTE generate a row number and the goal of the row number is for each post type id because that’s what we’re partitioning by we want to order by uh the the creation date uh converted to just a date it’s a date time we’re just converting it to a date then ordered by owner i user owner user id descending and then ordered by id descending with id descending acting as a bit of a tiebreaker because id is unique and these other things are not guaranteed to be unique either individually or in concert the second thing that i’ve i’ve run is a query that this is hinted to use uh optimizer compatibility level 140 because i do not want batch mode on rowstore to kick in and be enabled for this query the second thing i’ve included with the first uh first running of things is the query that a lot of people think could replace this but this is not the right query to write for this because what this is doing is it’s getting you the max value for each column grouped by post type id that is not correct correct because the the the real sort of algorithm if you want to call it that is that we are ordered first by creation date then ordered by owner user id then ordered by id this is getting the max for each of those individually the first thing that i want to show you is the results because showing you the results shows you where these two methods uh no longer agree as far as uh the included data goes so uh post type id one the owner user id is different between these two uh it’s also different it’s also a different owner user id for post type id two and post type id well three of course right there um the ids are different for these two as well uh so really the the max method just does not give you the correct results there’s just too much different in here focusing over on the execution plan uh we can ignore this one because this one does not give us the correct results so let’s just get that like way out of the way we’re not thinking about this one at all this is the row mode version of the query and you’ll see if you follow along the operator times here this thing runs for just about well a little over 14 seconds i like to say 15 seconds that feels good to me so let’s just say this thing ran for like 15 seconds right nice nice fizz buzzy number there uh and and like really you know there’s just not a whole lot to say about this generally uh in row mode queries like this are quite painful even if you have a reasonable index for sql server to use to make the the window function go faster um you you often it’s often just a terribly inefficient way of writing and running the query so what what i want to show you next is uh the carry over sort method so what the carry over sort method aims to do is get the max you see the max starts here and the max ends way down here but what the max is doing is basically assembling a string based on the getting the max of all three of the columns that we care about the normal carry over sort thing uh does not include as much complexity uh in uh for the second column as mine does the problem that i was running into is that there are negative owner user ids in the post table and sure i could have filtered them out but that’s that’s cheating a little bit uh we want to maintain uh all user ids we want to make sure that we even include the negative ones because what when the max is when the max is a negative number then we we need to consider that don’t we we can’t just not return a result for a row because we didn’t feel like dealing with some abnormal potential abnormalities in the data so i have a case expression here and the case expression just says when owner user id is less than zero then i add some x’s to the left uh which um is different from what we’re doing when owner user id is greater than zero or greater than or equal to zero uh because with that we are right padding the number so we’re like adding like zeros to the right of it the reason that i did the x’s is because if i zero padded it things would have gotten messed up down in the select query in the select query uh we are basically asking for a substring and converting the substring to the correct data type so if i were when you do that with the id column and you have a zero padded number and you convert a zero pad right zero padded number to an integer it’s no problem right because like we just you just remove the zeros from the front of it and you give give the rest of the number the problem with the negative number is that you have you essentially have a string and if i added zeros in then i wouldn’t know if there were naturally occurring zeros in the number uh so i used x’s and i replaced the x’s to avoid confusion there but the carryover sort method just does this we still group by post type id we’re just we’re using the max function across like we’re we have three different columns in the max function uh post type id sorry creation date owner user id and id and like we’re assembling like the max across those three right so max encapsulates all three of those columns and if i run this query and we got a little bit of highlighting to do here don’t we uh this will return correct results at least if we at least compared to the first query that we ran with the window function and uh so like all the results here match what we get from the window function version but we get it much faster what’s cool here is that none of this query even though we’re in compat level 160 uses batch mode the scan of the post table happens in row mode you can see that just sort of over right next to my big head there uh this compute scalar also happens in row mode the hash match aggregate which is totally eligible for batch mode still goes in row mode uh so basically and like and of course parallel exchanges like gather streams don’t support row mode so this whole thing finishes in about two seconds without using any batch mode whatsoever so back before batch mode was really a cool useful thing this was a good method to get the max value per row like i said earlier this can still be very useful if you’re on standard edition because microsoft you didn’t if you’re on standard edition microsoft doesn’t think that you paid them enough to have your queries perform well so you don’t get like even if you’ve got batch mode to like batch mode to happen here you would be limited to a dop of two for your batch mode queries so you would not be like like i have max dop set to eight for this this thing will have used like a dop of eight for uh the query right you can see degree of parallelism right there eight so this thing would have used uh eight cores up for for this whole thing and spreading the workload of 17 million rows out across eight across eight cores nice and efficient is pretty efficient for row mode uh and for batch mode but like 17 million rows across two threads even in batch mode you’re likely going to see some performance fall off there like whatever the like i don’t know maybe maybe you’ll get real lucky and the trade-off won’t be too terrible but um that the standard edition limitations there are really quite a pain the next thing that we’re going to that i want to show you is the uh the the window function version of this again but without that compat level uh 140 restriction on it down here to prevent batch mode on rowstore for kicking in so if we run this query this will take about two seconds now right we get back the correct results this finishes in 1.8 seconds just like the carryover sort method that i showed you above and we don’t have to write that crazy max syntax where we convert like dates and numbers to strings and pad things and all that other stuff with the the synthesis developer edition which is the enterprise edition equivalent skew i’m not using uh 2025 standard developer edition because why would i hurt myself that way um that we we are we are just using regular uh edition here regular developer edition here so this runs at a degree of parallelism of eight and this runs nice and quickly and efficiently so if you’re writing uh window fun so like you know like really the the idea of this window function right it’s like we just want to get like the top row for each post type id so i’m filtering to where row number equals one for all these if it’s if your goal is to find like the max value of something for a row based on whatever criteria i just kind of picked three columns at random from the post table that seemed to like make sense i guess i could have thrown score in there if we felt like it um but uh if you’re doing that like i still would have had to deal with potentially negative numbers because scores can be negative in the post table so that like that maybe that wouldn’t have saved me too much time or trouble with the carryover sort syntax but uh if your goal is to like sort of find data like this and you’re filtering to where row number equals one batch mode can make these queries crazy fast like you don’t even have to add indexes like just let batch mode on rowstore kick in read from your table and batch mode process all the data in batch mode like it’s a way better uh way of running like big data crunchy queries like this if but if you don’t if you’re not in a situation where batch mode on rowstore can kick in for you like if you’re not on sql server 2019 plus and you’re not on enterprise edition and your database is an incompatibility level 150 or better and there’s no batch mode on rowstore uh like naturally occurring and you know like depending on if you can change stuff like you know you can you can mess around with like you know columnstore indexes uh you can mess around with uh putting like a like just like having an empty table in your database with a clustered column store index or creating a temp table with a clustered columnstore index and like left joining to that thing uh you can do all sorts of stuff to get like partial batch mode on rowstore but it it does not go as deep into your query plans as batch mode on rowstore like the intelligent query processing optimizer feature does just kind of weird i think but it’s just something that you learn to live with when you are tuning queries across a variety of strange environments anyway uh i this is just something that caught my interest and i felt like talking about um uh i i hope you enjoyed yourselves i hope you’ve learned something i hope maybe there was some good educational point in this video that you are able to take away from it even if it’s not uh memorizing the the crazy syntax in here uh i feel like perhaps there were a good a few good educational moments aside from that but anyway thank you for watching and i will see you over in the next video adios 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.
Learn T-SQL With Erik: Window Function Tricks for SQL Server 2022+
Video Summary
In this video, I delve into the latest enhancements to T-SQL in SQL Server 2025 and reflect on what Microsoft has or hasn’t done to improve it. While there are some minor additions like regex support and aware clauses, much of my focus is on the improvements from SQL Server 2022 that make working with window functions more efficient. I explore new features such as `ignore nulls` and `respect nulls` in `lag` and `lead` functions, which significantly simplify finding last non-null values. Additionally, I discuss shared window clauses, demonstrating how they can be used to reduce redundancy and improve query readability by allowing common window specifications to be defined once and reused across multiple window functions. Despite these useful additions, the overall sentiment remains that Microsoft has largely neglected T-SQL enhancements in recent releases, leaving SQL Server users with a sense of being left behind compared to other database systems.
Full Transcript
All right, so we are going to continue with a little bit of teaser material from the Learn T-SQL with Erik course. Again, that is still on presale price until after the summer when the advanced material drops. There’s a link to purchase it down below. It’s 250 bucks right now. It’ll last you for the rest of your life. And of course, if you are attending past data community summit and you’re coming to Kendra Little and I’s T-SQL pre-cons, you will get access to this material for free because it is completely free. It’s a companion material to what we will be teaching. So, you know, SQL Server 2025. There are release notes all over the place for it. And there is not a single worthwhile enhancement to T-SQL to talk about. Sure, we got regex. Okay. You have any idea how many people that’s going to screw up? Regex and aware clause. I mean, cool. Like, as a consultant, like, yeah. But as far as, like, things I’m excited about, nothing. I think, you know, one way, one way you can sort of judge how much Microsoft cared about a specific SQL Server release is by how much T-SQL has, been sort of alleviated of the many things that it has been missing for many years that are in the SQL standard. And this one is rather laughable. You know, I guess Microsoft is busy trying to get Fabric to catch up with Databricks.
So they have ceased trying to get SQL Server to catch up with, like, every other database on the planet. So, cool. Anyway, the only T-SQL enhancements that I have thought were kind of neat were back in SQL Server 2022 when Windows Functions got a couple neat new things. Windows Functions got nothing in SQL Server 2025. We have once again been left in the dustbin. We are on the shelf. We are not having a good time.
So, like, if you ever spend time, like, just like, I don’t know, like, if you read T-SQL blogs for fun, you read SQL Server blogs for fun, you may have found a particular brand of problem across posts over the years called the last non-null value. This, of course, this, of course, this, of course, did get easier with window functions. Before window functions, it was, forget it, like, queries would never finish. But even with window functions, it takes, like, multi-step queries in order to get the last non-null value for something.
So, if we run a query like this and we say, like, you know, we get the last commenter is lag user ID one over order by creation date, you’ll notice that there’s a lot of, you know, nulls in here. So, if we wanted to find the last non-null value, we would have to, like, we would have to essentially, like, like, run this query and then run another query to sort of, like, to get those other values. It gets very complicated very quickly.
What SQL Server 2022 added is a couple things that you can stick into, like, the lag lead window functions to either ignore nulls or respect nulls. Now, you can think of it what you will, that SQL Server Management Studio 21’s parser has a bunch of red squiggles in this query because it does not recognize the syntax from SQL Server 2022. All right.
So, we have SQL Server Management Studio 2021, which became GA, like, I don’t know, a couple months ago at this point. And we have SQL Server 2022, which came out, like, three years ago at this point. And the parser is still like, I don’t know what that means.
So, you know, we got dark mode. Okay. But I promise you that this query will run successfully.
What I’ve added to this query are the lines ignore nulls for this one and respect nulls for this one. So, ignore nulls makes finding the last non-null thing a lot easier because this will give you the last non-null value in the column. Right.
So, this ignore nulls just gives us the value that we want over and over again. Granted, this isn’t a very interesting data set, but the respect nulls, we get all of this stuff back. Right.
Now, forever, we have had the ability to pass in a third input to, like, lag and lead and stuff. I’m just going to spread this syntax out a little bit so it’s a little bit more obvious what I’m doing in here and why there are some rows that have a very strange big number in them.
And that is because I am adding a third optional input to the lag and lead functions, which give you a default value for anything that would have been produced a null because of the function. So, the results in here, you’ll notice that both of these lines have the integer maximum for them.
That just, that’s because we filled in a blank with that optional third parameter. Now, there’s other neat stuff that came out in SQL Server 2022 for window functions as well. Like, you can now have shared window clauses.
So, like, if you were writing window functions with, like, similar, like, window specifications in them, you would have to, like, write that over and over again and your queries could get very, very big with window function specifications. But now what you can do is you can say something like this, right?
Notice we’re just saying over x here, right? And typically, over x would be like, huh, what is x? Well, x is what we have defined down here.
This window x, right? We have, it’s almost, it almost looks like a CTE for your window function. Isn’t that scary? Right? Window x as partitioned by owner user ID, order by rows between unbounded proceeding and current row.
So, both of these window functions, sum and average, can share a common window clause. Now, would that Microsoft were so kind as to give us more neat enhancements to T-SQL like this in SQL Server 2025, you and I could be talking about much newer cool stuff.
Here we are, though. But what’s even neater, I think, about the common window clause is that you can actually stack them so that they inherit window clause specifications from higher up ones.
So, it almost looks like stacked CTE when you read them. So, here, notice that we’re not using x anymore. We’re using T-S and A-V, right?
So, this is the window specification for sum, and this is the window specification for average. And if we look down here, this is where I’m doing the magic work for this one. We have window x as partitioned by owner user ID, right?
And then we’re saying, comma, T-S as order by score between rows unbounded proceeding and current row. And then, just for, you know, a little bit of texture in the demo, A-V is ordering by score descending between unbounded rows proceeding and unbounded rows following.
So, they’re both going to partition by owner user ID, but then they’re both going to do something slightly different with the order by. So, score for this one is ascending.
Score for this one is descending. This one is going from the beginning of the results to the current row. And this one is going for the entire result set. So, unbounded proceeding and unbounded following. So, it’s like the entire thing is what we’re getting the average as.
And now, we can allow our window functions to not only share a window clause, but to inherit and share window clauses. So, we can get back even, we can, I don’t know, make much more interesting queries without, I don’t know, I guess that’s actually still kind of a lot of typing now that I think about it.
But, it saves you some space up here in the select list. It makes that cleaner and tidier. I’ll give it that much. So, that’s just a couple cool things from 2022. Hey, we got Regex.
Ding. Like and subscribe. All right. Cool. Thanks for watching. I hope you enjoyed yourselves. I hope you learned something. And I’ll see you over in the next video where we’re going to talk about some stuff that batch mode makes a whole lot faster.
So, we’re going to take a little break from being depressed about Microsoft’s abandonment of SQL Server generally. And we’ll talk about some stuff from back when they cared.
That’ll be a good time. 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.