Category: SQL Server

Row Goals: Part 3

---

Video Summary

In this video, I delve into some interesting query optimization techniques using Roll Goals in SQL Server, particularly focusing on how sub-queries can impact execution plans and performance. We explore scenarios where sub-queries lead to inefficient nested loops joins or top operations above scans, which can significantly slow down queries despite having good indexes available. By understanding these patterns, you’ll learn strategies to improve query performance, such as using COUNT() instead of EXISTS in certain conditions and leveraging appropriate join algorithms like hash joins when necessary. The video also covers how to identify and address these anti-patterns through practical examples and real-world optimizations, helping you avoid common pitfalls that can plague your SQL Server queries.

Full Transcript

All right, so welcome back to Roll Goals. We’re in part three now. All right. If the three surprises you, there’s a playlist with all the others. I don’t know how you would stumble upon the third part without having stumbled upon the others, but welcome to the internet. Welcome to YouTube, I guess. Anyway, or wherever you found this. It could be Twitter, LinkedIn, other websites. If you got here from my OnlyFans, sorry. This is going to be much, much more difficult. It’s going to be much more boring. I wish I had OnlyFans money. Anyway, one place that Roll Goals can be very useful for reshaping query execution plans is with sub-queries. Now, I want to say this query isn’t like terrible, terrible, but it is a good example. All right. So let’s look at the query. Let’s look at the execution plan for this query. And what we’re doing is we are selecting some stuff from the user’s table. where reputation is greater than or equal to 1000. And what we’re doing in here is saying, I want to find each user’s max last activity date. And then I want to figure out if that max last activity date is less than or equal to, I guess, Halloween of 2009. Right? And then we’re going to order by some stuff out here, I guess, for whatever reason. Seemed like a good idea at the time, right? How do most queries end up with an order by? Seemed like a good idea.

Seemed like a good idea at the time. So we’re going to run this. And we’ll get the execution plan back eventually. And we get our rows and aren’t we so happy to have gotten our rows, but we are not necessarily happy with the execution plan. So we have the user’s table down here. And we might find it quite odd that SQL Server did not start with the user’s table, maybe find just the, you know, whatever rows it cared about there, and then go find rows in the post table that matched to that because, you know, we like the, on the, on the, on the users table, we have the clustered primary key on ID. And we currently do have an index on the post table on owner user ID, I haven’t, I didn’t show the indexes that I created for these demos. But you’ll see, you’ll, you’ll understand what they are eventually, right, you’ll get it. So it’s a little bit strange that SQL Server chose to take this part of the execution plan, the select max, last activity date, where p dot owner user ID equals u dot ID. And it chose to, like formulate this aggregate first, and then like, you know, like scan the post table, aggregate the data, and then filter out the rows that we don’t care about, right? The filter here is going to be Halloween of 2009. That’s our, that’s our, that’s our filter predicate in the query plan.

So it chose to get all this stuff, like, you know, scan 17 million rows, aggregate them down to 1.4 million rows, I think, 1, 4, 3, 5, 0, 7, 2, yeah, 1.4 million rows, and then filter out the rows we don’t care about down to 7,320, right? So it’s a little weird that SQL Server chose to do this. Because it had alternatives.

Now, what, what’s one thing that you and I know about this, about a query that does this, right? Like even, even just like, if we focus on this part of the query, right? If we selected the max last activity date from posts, it could only ever return one single row.

If we selected the max last activity date for, for posts, for posts, even grouped by owner user ID, owner user ID would only ever produce one row. You can’t have multiple maxes. I mean, you, like, let’s, like, let’s say both max last activity dates were like today, right?

Like you had like two or three or four or five of them. It would still be one max, right? Like they wouldn’t have multiple maxes. Like we have one max value.

So this actually can only ever return one row per owner user ID, right? We have that pretty much guaranteed. So if we rewrite our query a little bit and we stick a top one in here, then all of a sudden SQL Server understands that too. Why it doesn’t understand that to begin with?

You know what? Maybe it does and the optimizer just said, oh, I don’t like the cost of that. I don’t, I don’t like the, I don’t like how much that costs. It’s too expensive. We’ve got a max last activity date at home, Erik Darling. But if we do this, remember the last query that we ran, that took about 3.7 seconds.

Okay. 3.7. That’s the, that’s the mark to beat. If we put a top one in our little sub query here and we rerun this, well, that was noticeably faster. This is 714 milliseconds, right?

And notice now here we have a top and then we have a stream aggregate and then we have another top. And it’s all rather funny looking, isn’t it? But this ends up being a faster execution plan because, you know, even, even though we, we find, we find more rows in the users table initially than we, than we got when we joined to the post table the first time.

It took three, like extra three seconds the way that we did it last time. Now we’re getting one aggregate per user ID that comes out. And this ends up being a lot faster than getting all the aggregate user IDs at once.

This is a trick that you can use in sub queries in the select list, sub queries in a where clause that have an aggregate like this. You can use this almost anywhere and you can get a completely different execution plan shape that might be more efficient. It’s especially more efficient if you have an index that you can seek into down here.

I have an index that leads with owner user ID. So when this query runs, we can seek to each owner user ID that we care about, right? And that’s a, that’s a very efficient thing to do.

Even though we do the last bit of filtering over here for Halloween of 2009, this query still finishes a full three seconds faster. If you want to see kind of a cute little trick, we can add in a sort of an empty group by, right? We have a group by with these empty parentheses here.

And what this will do, this will actually remove the stream aggregate. It’s kind of a funny little side effect, right? If we run this and we get the, look at the execution plan. I mean, it saves a little bit of time, right?

The last one was 700 milliseconds. This one’s about 550 milliseconds. But now we just have two tops hanging out next to each other, right? What are they looking for? I don’t know.

We’ll get to the bottom of it someday. Anyway, row goals tend to work well when data is relatively easy to find, either because it naturally occurs often or you have a reasonable set of indexes to search with. Sometimes, even if you have a reasonable set of indexes to search with, it’ll be slow, though.

Row goals can be slow. And this is a pattern that I end up troubleshooting quite a bit. So, almost everyone I work with has some store procedure that either has, like, maybe a loop that does stuff, maybe, like, some batch things going on.

Or they’ll have, like, a conditional, like, update or delete, usually. So, what they’ll do is they’ll start a query with something like this. And they’ll say, if this condition exists, go do this thing.

Like, so, in this example right here that we’re looking at, we’re joining post of votes. And we’re looking for where vote type ID equals one and post type ID equals one. The reason why we’re looking for this is because a post type ID of one, which is a question, should never have a vote type ID of one, which is when a user accepts an answer.

Vote type ID of one should only happen for a post type ID of two, because only an answer can be marked as the answer. A question can’t be marked as the answer. So, we’re going to run this query.

And what we’re going to see is that even with good indexes available, we are unable to find this data. Right? We don’t find any matches.

But SQL Server thinks that it’s going to find matches, because SQL Server trusts you. One of the sort of conditions of the optimizer is that if you ask for rows via a query, SQL Server assumes that those rows exist. SQL Server is like, well, you seem like a nice person.

Why would you lie to me? Why would you send me on a wild goose chase looking for data that doesn’t exist? Right? And so, we look at all this stuff, and we see that SQL Server estimated three rows, but we ended up with 3.7 million rows. And so, we went into a nested, so SQL Server set a row goal and said, I think I can find this condition with three rows.

But it didn’t, did it? Nah. Eh, did not.

We went into a nested loops join, because row goals work, like nested loops join, like small navigational queries, like with nested loops and stuff, work very well with row goals. Right? Because you just, oh, I’m going to find this.

I thought, I’ll find it. Yeah, you get it. I’ll get it on the next row. I promise. Right? So, we have this nested loops join here, because SQL Server was like, oh, well, I can find this really easily. Right?

I’m just going to seek to the three rows that I care about here, and I’m sure I’ll find something down here. But we don’t. Right? No rows come out of the post table that meet that. So, if you have a store procedure or something, or some other, you know, code that runs, that looks for data conditions that shouldn’t exist, and seeks to correct them, right? Or like delete them, or update them so that they’re the right thing, or just like flag them so you’re like, wait a minute, this is wrong.

We need to figure out what happened. Then you can run into this problem quite easily. So, if we look at the properties of the index seek, we will see something that we saw before, the estimate rows without row goal. Right?

So, SQL Server was like, if I don’t set this row goal, I’m going to have to go through 3.7 million rows. That’s no good. I think I can do this in three rows. Right?

Which is like, I guess, 2.8 something. Right? It’s like, I can do this and it gets rounded up to three. Okay. But guess what? None of that happens. And then at the end of all this, so it looks a little funny that we get a lot of rows here, and then zero rows here, and then zero rows here. But then we end up with one row here, but that is coming from this constant scan.

So, SQL Server is like, ah, well, I got to figure out if this exists or not. And I have to return something to say, did this exist or not exist? So, we get that.

One way that I fix this quite frequently is I make SQL Server count. Oh, oh, that’s tooltip hell. Ah, all right.

So, we can actually run this same if exist query, and we can just say, if this exists and it has a count greater than zero, now all of a sudden, SQL Server has to do something differently. SQL Server has to count everything and then filter out on that count. All right.

So, we went from, how long was that one? 6.464 seconds. All right. Now, let’s run this one where we make SQL Server count things. All right.

We make SQL Server do some extra work. That was noticeably faster, right? So, this is 1.2 seconds. And we get a much more sort of appropriate execution plan for this. So, this filter is where the halving on the count comes in. But, we notice that we get all 3.7 million rows here, and the whole thing just takes about 1.2 seconds total.

We get a nice parallel plan. It’s still nested loops, which, you know, you can think what you want about that choice. Maybe this would have been faster with a hash join.

I don’t know. There’s a lot of scanning around for stuff when you do that. But, you know, SQL Server is like, I’m going to get 3.7 million rows here, and then I’m going to probably get 3.7 million rows here, but I got none. All right.

So, maybe the nested loops join, not the best choice, but still a lot faster. All right. Not that crazy single serial nested loop row goal plan. All right. Avoid that.

Another big anti-pattern that I see quite a bit is a top above a scan. All right. So, top above scan, almost always a bad sign. When people talk about query plans, they often say, there’s no such thing as a bad plan.

There’s no such thing as a bad operator. Like, they’ll make all sorts of, you know, like, I know a lot about stuff, things. But there are certainly query plan patterns that I dislike seeing quite a bit.

And when I see these query plan patterns, I almost always say, hmm, I think you’re having a bad day because of this. So, this is an example of that. So, what I’m going to show you first is the estimated plan.

All right. And if you were, say, looking at this plan in query store or the plan cache or maybe some monitoring tool that correlates plans and stuff, you might say to yourself, look, how could this possibly take a long time? How could this, look at those tiny little lines.

What could, what could go wrong here? Are you insane? This was slow. This had to get blocked. The server must have been, like, shutting down when this query ran. No way this query took however long, right?

So, it looks harmless, but it is not harmless. All right. If we run this and we get the actual execution plan, we might be shocked. We might be very shocked.

We might be very dismayed. We might take an extra long look at our phone. We might start doom scrolling some social media or another. We might, I don’t know, we might call about our car’s extended warranty.

We might find all sorts of things to do in the time that these queries take to run. We might find all sorts of things to do in the time that it takes these things to finish, right? We can get a lot of things.

Now, one thing that I joke about quite a bit in my stuff is part of query tuning is a rows to seconds ratio. All right. How many rows per second should you be able to get back?

Well, we get back one row, but it takes us almost 24 seconds, right? That’s not a good time. And I mean, a lot of this query does still have rather thin lines to it, right? These are all tiny, thin lines.

That’s still a tiny, thin line. This one, you can barely, it’s barely a smidge. But look what happens, right? This is all 24 seconds. I mean, all 24 seconds is really like here. But we have this top above a scan.

And this top is just asking for one row over and over and over again until it finds something, right? So SQL Server said, I can do this in one row. Just one row.

I can knock this out. No problem. Clearly, that didn’t work out. It took 58 rows to knock this out. Now, what the top is doing is every time it gets a row, right? Because we didn’t find what we wanted, right?

We didn’t find that on the first row. It took on the 58th row we finally found this. So every time this ran, we did a scan of the votes table. How long that scan takes depends on where in the data what we’re looking for is, right?

So this one, for some of them, it probably would scan the whole table. For other ones, it would only scan part of the table. If we look at the properties over here, like, you know, we’ll see the actual number of rows read is, let’s see, 244689052.

That’s a nine-digit number of rows. Nine is a very hard one to do. I guess I’ll just put a thumb down. So nine-digit number of rows.

So the votes table itself is 52 million rows. But, you know, it’s, but 52 million times 58 is not that number. I don’t think it is.

Maybe it is. I don’t know. But I don’t think this, this, the votes table gets read in full for every one of those. Maybe, maybe, maybe not. Because sometimes, because we’re looking for not exists. So if we find that a row exists, we can exit out early, right? Because it’s a semi-join.

It either is there or it is not there. So sometimes it might find everything. Sometimes it might not. But, so we talked about this. So, of course, this would be fairly trivial to fix on our own.

We could just add an index on post ID. And then we would have a top above a seek. It would be much easier to do, let’s say, 57 seeks into an index on post ID. And figure out if a row is there or not.

Mostly not. And then it would, what do you call it? On the 58th row, we would get, we would figure out, we would find what we wanted. So, this pattern is fairly easy.

Like I said, fairly easy to spot in an, even in an estimated execution plan. Either from query store, the plan cache, your favorite monitoring tool. You may need to get an actual execution plan to know that there’s a real problem with it.

But, getting one just like that was a fun and exciting time. We had 24 seconds to just relax, stretch, yawn. I don’t know, scratch ourselves if we wanted to do that.

Anyway, enough about, enough about those kind of row goals. Let’s talk about some good kind of row goals. There are two different types of row goals, to my mind. There are top level row goals, which is like when you say select top, whatever.

And then there are sort of interquery row goals, right? Where you might select a top within a query like this. So, if we, let’s, let’s actually quote this out first, right?

So, we’re going to take this and we’re going to run this query. And we’re going to look at the execution plan. And we will see that we just have one top out here. And that SQL Server scans the users table.

And then it does a nested loops join. And then it hits the post table. And then it does another nested loops join. And it hits the comments table. This query is not disastrously slow. It’s about 1.3 seconds.

But look how the query plan shape changes when we put a top in here. We’re going to put that top there. And we’re going to run this.

And it takes, I mean, it’s still about 1.3 seconds. Like I said, this isn’t an example of fixing something with a row goal. This is just an example of top level versus inter-query row goals.

But now we have one top over here. And we have one top over here. And the shape of the query plan has changed a bit. So, now we start, we hit the users table out here.

But within this branch, we join posts to comments, right? So, things have changed in the shape of the query a little bit. So, in the next video, and I think the final video in this talk, we’re going to look at how we can use this sort of knowledge about how we can sort of get things to happen together in a query with row goals to fix some bad performance issues.

All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you over in the next video.

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.

Row Goals: Part 2

---

Video Summary

In this video, I delve into the intricacies of execution plans in SQL Server, particularly focusing on how row goals affect query performance. I explore various scenarios where row goals are introduced and how they influence the query plan, such as when using `TOP`, `EXISTS`, and `IN` clauses. Through detailed examples, I demonstrate how these elements can lead to unexpected behaviors and highlight the importance of understanding their impact on query optimization. Additionally, I discuss the nuances between setting a row goal versus a row limit, illustrating practical cases where large numbers or specific conditions can significantly alter the execution plan, offering insights that can help optimize SQL queries more effectively.

Full Transcript

All right, Erik Darling here with Darling Data. And we are going to, this is part two of the Rogals playlist. Two of what? I don’t know yet. Hopefully four. I think I’m aiming for four. And if you have stumbled upon this and you’re hearing that it’s the second one and you are taken by surprise, well, there’s a playlist of these that you can find the other videos in. But right now, we’re going to do this. So, most people in the world, at least SQL Server people in the world, right? Because we have very nice graphic execution plans to look at. Not everyone is so lucky. Some people have text. And some people just have like weird tree looking things. But we’re lucky and we read execution plans kind of like starting over. Well, that’s, I guess this is mirror image from the right to the left, which is fine because that’s the way data flows. But when a query plan executes, it’s really, this is something that Paul White has talked about in a few blog posts and in a few videos where, you know, you have stuff that, sorry, my mirror image, stuff that opens up and starts executing over here and then sort of asks for rows down as it comes along. So, that’s part of why things like Rogals and operators like Top kind of work in the way that they do is because they are just continuously asking for rows until the, top specification is met or you run out of rows to produce. So, I have this table called high rep users. There’s only about 600 rows in it. But I want to show you sort of three different execution plans. They’re all two of them look pretty close, but it’s interesting to note this stuff. So, one is just selecting the top 10 rows with no particular order. Now, what we’re going to observe here is that rows will get returned in clustered index order.

It’s not a guaranteed order. This is only an observed behavior for this query is the way it’s running. So, keep that in mind. Then we have another query that’s asking for the top 10, but with the rows ordered by ID descending. Okay. Now, there’s another, the third query we’re going to run is the top 10, but with, but this is going to be ordered first by reputation descending. We don’t have any indexes on the reputation column that are going to help have, they’re going to put this in order. So, we’re going to have to physically sort the data. This will all make more sense when we look at the execution plans. So, let’s run these three. And what we’re going to see is, of course, three execution plans. If SSMS will be so kind as to let me get a little more screen real estate here.

So, the first query just starts asking for rows and these, these rows could be returned in any order, right? Cause we didn’t specify an order by. So, if we use maybe a different index than the clustered index, then we might get rows in a different order for this, right? But for this one, notice that the 10 rows, just SQL Server reads 10 rows from here, right? And we get those back in whatever order the thread happened to find them. And it’s a serial plan. So, it’s most likely just going to be clustered index order. Again, observed behavior, not guaranteed. The second query ordered by the top 10 with ID descending is we still don’t have to sort this data because we can still read this in clustered index order just backwards. And we just go and get 10 rows from here, right? So, this is just where we get all 10 rows from.

The last query is a little bit different. So, where this one is different is that the other two just had top, right? Just a top operator. This one has a top end sort. And the reason why this query is different is because we actually have to read all 613 rows from the table into the sort operator. And then we just get 10 rows from the top end sort as we get the data from here. We sort it and then we just get the top 10 rows in the order that we asked for it in, which is reputation descending and then ID descending.

So, while row goals do trickle down through the plan, sometimes you’ll end up with row goals that don’t quite make it down as far as other times, right? It all sort of depends on how your queries are and what your indexes look like. Now, because exists are something that may introduce a row goal, one thing that I see a lot of people do is act absolutely ridiculous when they write exists subqueries.

So, when we run this query, right, we’re going to say select star from high rep users where we have some where one of those users has a badge, which of course is going to be all of them because it’s impossible to get a high reputation without getting a badge, I think. Oh, wait, no, I lied. It’s only 609 rows. So, four people have a high reputation and no badge. All right. Maybe I’m wrong about this stuff sometimes. But if we look at this execution plan, it is just, it looks just like this, right?

We scan the clustered index of high rep users and then we have a left semi-join. In this case, the left semi-join does introduce a row goal. All right. I’m holding back a sneeze because I’m not wasting this five minutes.

And if we look at the properties of the index seek on the badges table, we’ll have this line right here, which tells us that a row goal was introduced, right? Estimate, estimate, estimate, not estimated, estimate rolls without row goal. Okay. Not estimated. Okay. Just estimate. Okay. Whatever.

But this shows us that a row goal was introduced, which happens sometimes with exists because a semi-join only needs to find one row. So, what I want to show you next is something that I see people do quite a bit with exists subqueries, which is a lot of useless typing, right? They will throw a distinct, a top one, they’ll group by, they’ll order by, they’ll do all this stuff.

Now, if we were to, if we run this query on its own, I just want to show you the execution plan for this real quick. If we run this without that outer correlation, we will get sort of a predictable execution plan, right? We have a top because we had a top. We have, well, I mean, we did a distinct and group by like fools, but, you know, okay.

We’re not going to distinct and group by, but, you know, stupid. But, you know, you at least have an aggregate here that shows we did some aggregating of things. If we run the full query, right, if we take out, if we, rather, we put the correlation back in and we run this, what we’ll see in the query plan is that SQL Server completely ignored all of the stuff that we did around the correlation, right?

The distinct, the top one, the group by, the order by. The execution plan doesn’t change one bit. We just go back to having a left semi-join.

There’s no aggregate, there’s no top, no nothing. Why? Because SQL Server is already sort of putting a top in there with the row goal. So, just a couple notes on some other T-SQL things. There are, there is syntax that is logically equivalent to exists, which is, which is like the in clause, okay?

We can say select from high rep users where ID is in, select user ID from badges. And if we do this, we will get the same semi-join query plan, right? The exact same thing that we got before.

I mean, obviously it’s not like, like exact exact because it’s a different query. Like other things will be different, like hashes and handles and things, but it’s still the exact same query plan shape and operators. Another way of writing a query with like this is to say where ID equals any.

Okay. That’s a funny one. Right? Bet you didn’t know you could do that. But any equals, uh, is sort of like doing in or a left semi-join, right? Right.

You could say equals any, and you could get this plan back, right? Which is the same thing with the left semi-join. You could also write that as high rep users equals some, which is equivalent to any, right? So any and some, uh, equivalent in this case.

And this will give you the same left semi-join plan. So there are all sorts of funny ways to get this syntax. We don’t have in, in T-SQL a way to say like, uh, like, uh, inner or outer semi-join or something like that. There’s no way to, um, give like, uh, request a semi-join, but there are ways that you can sort of, uh, influence the optimizer to give you the semi-join behavior.

There’s also another funny way, uh, to write queries, but this is not the, uh, this is not the equivalent. This will just get a very strange, well, not strange execution plan, but we won’t get any rows back because not all, all rows match. Don’t, not all rows match across all of these.

So this is it. Um, not in is of course not equivalent to not exists. Um, I’ve written and recorded quite a bit about that. So I’m not going to go into detail on that here.

Good Lord. Just know that I’m dying and my dying wishes for you to get to the end of this video. Uh, so I’m not going to talk about not in here, but, um, uh, we’re going to, uh, just point you to a handy little link that you can go to. All right.

Anyway, um, top 100% is absolutely useless in views. Um, if you see people do this, uh, yell at them. Um, if we were on this query and just the, just like from like without, not from the view, but you’ll see that we, we like SQL, there’s no top and there’s no top end sort. We do sort the data because we asked for data in a particular order that we do not have an index to support, but there is no top SQL Server ignores the top 100%.

So if we create a view that says select top 100% stuff, and then we select from that view, uh, you’ll note the execution plan has no top, no sort, no nothing. Right. So this obviously the optimizer does throws us away and it, but it does this to be helpful, right?

There’s no top operator. There’s no sort. So the optimizer does this to clue people in that they are potentially being dumb or potentially they are trying to rely on behavior that is not guaranteed. So we should all give the optimizer a round of applause for correcting our terrible, uh, I don’t know.

What is, what would you call that? Malfeasance? Right. Maybe correcting our terrible query writing.

I don’t know. Something like that. Anyway, there are other ways that row goals can sneak into your queries. Um, one interesting way, at least I think it’s interesting, but I think lots of things about this stuff are interesting that maybe other people don’t. Is, uh, when you have, when you’re, uh, filtering on row number.

So if we say where the, the N up here, N equals row number, right? Uh, if we say where N is between one and 10, SQL Server will, uh, do something that we’ve actually kind of saw before in one of the other queries. Where, uh, we have a top, right?

And this top starts asking for 10 rows, but before it can, before it can ask for 10 rows, those rows have to get sorted. Right. So we get all 613 rows from high rep users, but we don’t, we don’t reduce that to the, the row goal of 10 until we’ve sorted them. Right.

And then the, of course, the two operators in here are the row mode representation of generating the row number, the sequence project in the segment. Uh, but if we were to change this to say 20, we would see that row, we would see the, the, the, uh, 20 in here instead of a 10 in here. Right.

We would see this creep up and say, oh, now we need 20 rows from the sort. So row goals can sneak in, in all sorts of ways that you may not expect. There are other things that will also set row goals, but this, there’s a difference, right? Because we talked about it in the first video between setting a row goal and setting a row limit.

Right. So, uh, a query like this, where we say, uh, select whatever from table offset zero rows, fetch next one row only. This will set a row goal of one, but also only return one row.

Right. We’ll get Chris Jester Young here. We can also set a row goal with, with a fast end hint. Of course, you can put whatever number you want up to, I think the big int max in here, but that would, I don’t know.

I don’t know at what point that would become counterproductive. But when we say option fast one, we’re still setting a row goal. We’re just not setting a row limit.

Okay. So fast, like the fast hints will return all the rows, but with a query plan where the row goal is set to, uh, whatever number you put in there. Now, uh, the top operator, of course, this goes for offset fetch and fast two, uh, accepts, accepts up to the big int maximum.

There are a couple of neat shortcuts you can take. If you don’t remember the numbers involved, uh, using the power function. So, uh, two dot, the dot is very important.

If you forget the dot, you will get an error. But, uh, this one is two to the power of 31 minus one. And the big int max is two to the power of 63 minus one. But this returns those, those maximum numbers.

Sometimes if I’m, uh, feeling very ambitious, I will set up a view that has, uh, various SQL Server data types in their full ranges. I’m not like, like the, not like 2 billion rows full range, but like the min and the max for them. Because it’s, it’s a very handy reference for me when I’m trying to figure out what I, what I want and what I want to do.

Um, I don’t know if you ever do things like that. But, uh, getting back to rule goals a little bit. Uh, we can put big numbers into tops.

And those big numbers can, uh, again, sort of influence the optimizer in various ways. If I run this query where I’m asking for the top nine quintillion rows from high rep users where the display name equals John Skeet, we get this query plan where we seek into an index on high rep users that happens to be on the display name column. We do a key lookup for John Skeet.

And we still, but we still have a top, right? When we said top 100%, SQL Server said, well, I know better than that. But when we say top very big number, SQL Server does not know better than that. And SQL Server has to figure out a way to get that number of rows back to us.

So fun stuff there. What’s, what’s, what’s, what’s interesting too is that the optimizer and like sort of what, what might start cluing you in to what, how SQL Server kind of fences things around stuff like top. Is if we run a query like this, where we’ve sort of nested the top away under here.

And we have the display name filter out here that when we run this, we’ll get a different execution plan back. We still have a top, right? And we still have like SQL Server still gets the top nine quintillion rows from the 613 row table, but it has to do this first.

And then it can filter stuff out. This is like, this is more like logical correctness stuff though, right? Because asking for the top nine quintillion users and then figuring out if the top, anyone in the top nine quintillion users has a display name, John Skeet, or just got, is different from asking for the top nine quintillion John Skeets, right?

There’s two logically different things. One thing that I see quite a bit and often bothers me is people using non-deterministic ordering and not really caring that what they’re doing is potentially going to introduce incorrect result bugs. Okay.

So if we run this, that’s like the top five from score. And again, like remember what we were talking about in some of the previous demos in this video where I said like we’re most likely going to return this data in clustered index order. We had a serial plan.

You know, SQL Server was just asking for the top 10 rows. So we could observe that as long as we use the clustered index and as long as we had a serial plan, SQL Server would return data in the clustered index order, right? Again, observe, not guaranteed.

If we run this query, which is selecting the top five rows from the comments table ordered by score. And we look at the IDs that get returned. Like notice a score here is all zeros, right?

So there are duplicates in here. We get these five rows back nine, 11, whatever, 855, 19, blah, blah, blah. If we run this a few times, we’re going to see different rows showing up in here because we have a parallel execution plan. And when top, when there’s a parallel execution plan with top like this, SQL Server is just sending out the dot threads and whatever threads come back first or the top five rows that we get back.

Right? Right? So obviously, depending on timing and reads and all the other stuff, we might get back five different rows every single time.

If I run this query again and we look at the results, we can see that even the order of IDs have changed and which IDs we get back. This all changes every single time. The only way to make sure that ordering is deterministic is to have some sort of unique column in there to break ties within the column that has the duplicates.

There we go. All right. We’re smart people.

We can do this stuff. So if we run this query a few times, we will see that we get back this data in the same order each and every time. Right? So this is deterministic ordering. The one we did before is not deterministic ordering.

All right. So we have reached nearly the 20-minute mark. This is a good place to pause because I just finished a demo. And now we have another demo that we can do right after this. This is fantastic.

Right? And we can actually start looking at reshaping execution plans with row goals a little bit now that we’ve established some bare minimum fundamentals. All right. So thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video, which will hopefully be three of four. But we’re just going to have to see how things go. 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.

Row Goals: Part 1

---

Video Summary

In this video, I dive into the fascinating world of row goals in SQL Server, a topic that might not get as much attention these days but is crucial for understanding query optimization. I share insights from my presentation at the PASS On Tour events and co-present with Kendra Little on two days of SQL Server performance tuning pre-cons, promising to be the best T-SQL pre-cons ever. Alongside explaining row goals in detail—both those introduced by us and those set by the optimizer—I also discuss the difference between row goals and row limits, emphasizing why understanding these concepts is vital for effective query optimization. The video includes helpful links for consulting, training, and becoming a channel member, as well as my usual humorous advice on how to contact me correctly. Enjoy exploring this essential aspect of SQL Server with me!

Full Transcript

Erik Darling here with Darling Data. And today’s video we’re going to get started going over the material that I presented at the Pass On Tour events, plural, about Rogols. Why? Because they’re fun. And it was kind of amusing and no one really talks about them anymore. So I decided I’ll give it a shot. I don’t know. You tell me how I did. If you look down in the video description, there are all sorts of helpful links. You can hire me for consulting. You can buy my training. You can become a paying member of the channel. You can ask me questions for free on my Office Hours episodes. And of course, if you enjoy this content, you can like, subscribe, and tell a friend. In the honored tradition of liking, subscribing, and telling a friend, I’m going to tell you about Sport Drink. Not because they paid me to and not because they asked me to, just because I like it. I’ve got the orange, you glad it’s not chemicals flavor. It’s quite nice. There we go. This November, I don’t even, I don’t know when this video is even going to come out. That’s how much fun life is. But this November, this might be after November. I don’t know. Who can tell? Time is a strange thing, right? Time is a flat circle, as OI’s man once said. I’m co-presenting with Kendra Little. Two days of SQL Server performance tuning pre-cons. Sorry, my back just did something strange. You get old and laughing hurts. Two days of performance tuning pre-cons with Kendra Little. They will be the best two days of T-SQL pre-cons that have ever existed. And you should come, or else. Right? Sorry. I got a text from Joe Sack. I can’t show you what it is, though.

Shh. Secret. Anyway. All right. Anyway, let’s go talk about row goals. So, row goals. Very interesting thing. Now, when we talk about query optimization, there are two ways that query optimization can be talked about. Right? There’s the query optimizer that goes and does stuff. Right? Makes a bunch of cost-based decisions. Thinks about your query. Says, hmm. I think this is a good way to go get that. And then there’s query optimization, which is kind of like what you and me do. Well, I don’t know what you do. I know what I do. And when we optimize queries, we think, hmm. I don’t like the way that worked the first time. I need to go fix it.

But row goals are one thing that sometimes the optimizer will do that will change your query and your query plan. And row goals are also something that we can do to change our queries and our query plans and hopefully get better ones. So, normally, this is where I would introduce myself to the audience because I’m a very professional presenter. I would tell them about all the great ways that they can hire me. I will tell them that I am a consultant with reasonable rates. And if they ever want to hire me or follow me or any of that stuff, these are all the ways that they can do that.

I usually also make a joke about making careful note of the fact that when you want to contact me, my name is Eric with a K. If you go look for Eric with a C, you might find the love of your life. You might, I don’t know, you might get married, you might have kids, you might have the greatest future that you could dream of, but it will not be with me. So, Eric with a K. Also, be very careful because whoever you meet, if their name is Eric with a C, they will likely be a sociopath because their parents spelled their name wrong.

So, be careful. Anyway, moving right along. I’m very funny in person. I told a very funny joke at the Pass on Tour event in the Netherlands. I told the Europeans that I reformatted all the material for a European audience by taking out the jokes.

They all laughed. The first time they’ve laughed. Crazy. Anyway, row goals come in two forms. There are ones that you may introduce commonly with top offset fetch or fast enhance.

And there are ones that the optimizer may introduce commonly with not exists, exists in, not in, stuff like that. You can think of a row goal as a bit of a short circuit for the optimizer. I don’t mean short circuit the way that it’s glitching.

I mean, sometimes you might think it is, but it’s not. But it’s sort of like the optimizer can say, hey, well, wait a minute. Normally, I would have to go get like 3 million rows and 10 million rows.

And I get to figure out a plan to get that many rows. But the optimizer can then be like, hey, well, wait a minute. That nice person at the end said, I only need to get the first 10 rows.

I don’t need to think of a way to get 3 million times 10 million. I just need to think of a way to get 10 from 3 million times 10 million. So we can kind of think about strategies a little bit differently about like there’s a quick, like probably maybe a faster, different way or different approach to getting 10 rows.

And there is some getting like 30 million rows, right? So that’s the first thing. You can also think of a row goal is sort of like a promise.

It’s like when you sell, right, that only some number of rows will be produced or that you’ll run out of rows to produce, right? So you’ll either say, well, I want the top thousand rows from this expression. And SQL Server will be like, well, you look like a trustworthy person.

If you asked for a thousand rows, I’m going to go find a thousand rows. I’m not going to stop until I find a thousand rows or I can’t find any more rows. So you can kind of use them to fence things off.

If you use them in a table expression that be derived or common. And you can often use that to force the optimizer towards a plan shape that you prefer. Of course, it does take some work to figure out what plan shape you prefer.

But once you find that plan shape, you’re golden. Now, one thing that it’s sort of important to establish up front is that there is a difference between a row goal. Say, I need you to hit this number of rows, right?

These rookie numbers, you hit this row goal and a row limit, right? So the terms may feel interchangeable. But think about this.

Just about every other SQL dialect that you will come across uses the word limit instead of the word top. T-SQL is the only one that uses the word top as a row limiting sort of device. Everyone else says like limit whatever, right?

It goes to the end of the query, right? It’s not like select limit something. It’s like select your stuff limit 10 or 100 or something. So there’s a difference between like saying I only want to get 100 rows from this and something like the optimizer saying, well, I only need to find one matching row. An example of that would be like a semi-join, right?

Because semi-joins don’t need to get all the rows. We’ll talk about that more later though. But some examples. This is a row limit, right? You’re saying I want the top 100 columns from some table ordered by some column, right?

Top without order by is usually frowned upon, but we’ll talk more about that later. So this is limiting the rows. This is limiting the rows and the results to 100 rows or maybe, I don’t know, if the table only has 99 rows in it, you’re kind of naturally limited anyway.

But we don’t often run into those tables. This is an example of a row goal. So when you say option fast one, you are not limiting the number of rows in the output, but you are telling the optimizer to devise a query plan as if it only needed to find one row.

This is set at the very root of the query plan. This and that specific wording, that pedantic wording will come in handy later. This is an example of, again, potential row goaling by the optimizer.

So when you say select something from table where exists something else, well, exists and not exists and in and not in, we often see them expressed in the query plan as a semi-join. The reason it’s called a semi-join is because it’s not a full join. And I don’t mean full join in the sense of like full outer join.

I mean that when you have tables that have one-to-many or many-to-many relationships and you join them together, the many’s need to be respected. When you have a semi-join, you don’t need the many. You just need to know if one thing is there or if one thing is not there.

Right? Either that row exists or that row doesn’t exist and you don’t need to find all the other matching rows if you’ve already acknowledged that a row is there or not. Right?

So that is an example of a row goal that the optimizer may set. Query optimization by the optimizer. Right? The query optimizer’s query optimization is largely driven by how many rows are expected to come from tables, survive, where and join clauses, get past group by and having clauses. And setting a row goal is a way to influence the optimization choices by the optimizer without having to use query or table hints and all that.

Because the number of estimated rows that SQL Server gins up during query plan exploration and all that stuff or cardinality estimation and all that stuff has a pretty huge impact on how things get costed. And of course, as we discussed during the lost in cost videos, the lost in cost videos from last week, if you have not watched that playlist, you can if you want. It is not required material for this.

Completely separate material. Except for some of the things I’m going to say here. Query costs are estimates and nothing but estimates. You’ll hear a lot of very smart and knowledgeable people prove that they’re in the cool kids club by calling them query bucks. Right?

But the important thing to understand, again, if you watch lost in cost, this will sound terribly derivative, but cost does not equal time. Cost does not equal, does not measure speed or efficiency or anything else useful. They are, costs are all unitless metrics.

They are not durable performance tuning metrics. All right? Even in an actual execution plan, costs are all estimates. There are no actual equivalents for costs derived after queries execute like other metrics.

Make sure query plans are turned on here because wouldn’t it be nice if there were a setting or a button you could press to just keep query plans turned on? Like maybe if in presenter mode, there were an option for like enable actual execution plans and just have them on for all your SSMS tabs. Sure would be great.

If only someone opened an Azure feedback issue on SSMS about that. That’d be cool. Anyway, if you, if we look at this query plan, we pause for a moment with a, I mean, you know, technically this is on topic because there’s a top 10 in here. So, you know, we do, we do set a row goal and a row limit here, but if we run this query and we look at this index scan, we will see all sorts of things that have the word actual next to them.

And we will see one thing that should have the word actual next to it, but does not. And then we will see some things that have the word estimate next to them. These things that have estimated next to them do not have an actual equivalent, right?

There is no counterpart to these where the actual costs are shown anywhere because they don’t exist. They are, they are not a thing. But these ones down here, well, you’ll, you will see actual stuff for those.

Now, what it’s, what is important to understand is that costs are only how we got the plan that we’re looking at, right? All the costing stuff is how SQL Server figured out what plan it wanted to execute for our query, right? The optimizer did all its costing and compared choices and you got the cheapest combination of choices or the optimizer timed out and say, you get this combination of choices because I’m sick of thinking about it.

Not, not an uncommon thing. I’ve seen your queries. So you tell SQL Server which tables you want via the from and join clause.

You tell it which rows you want via things like the where and on clause. I guess even having would, would make sense in there too. I gotta, I gotta fix that in post, I guess.

You might tell, you tell SQL Server which columns you want via the select, you tell it which columns you want to summarize in the group by and you tell it which columns you want in what order you want your results via the order by clause. So when I go to conferences and then I talk to people and then I go home, I will, I usually tell the conference organizers, hey, I’d like to talk to some people who wanted to talk to me. Maybe, maybe you could give me this information, right?

Because my rates are reasonable, I just say, hey, just give me anyone whose consulting budget is over zero, right? If you have zero or negative consulting budget, we’re probably not a good fit. But if you have more than zero consulting budget, you could maybe work something out.

Could do a little shuffle for you. And of course, I would want to get this information. I don’t want to get all the information because too many, too many is too crazy.

But I just want like the top hundred people and I want them by consulting budget descending so that I make sure I maximize my input. But queries are just descriptions of what we want to see. And the optimizer goes and figures out how to best do it.

It’s impossible to escape people teaching you about SQL or databases without telling you that SQL is a declarative language. And other programming, whereas other programming languages are largely imperative or procedural programming languages. Meaning that you tell the computer exactly how, when, where, and why you want things done.

And it goes in and follows your instructions. Whereas queries are a completely different world. Queries, we have to describe all these things to the optimizer.

And the optimizer has to do a good job of going and getting it for us. There are some corollaries in databases that sort of match that a bit. Indexes contain data.

Statistics describe data. Databases contain data. And we describe what data we want to see from that using our queries. Costs are really just a bunch of internal algorithms that SQL Server uses to shape and choose execution plans for us. And the hope, the grand hope, the big hope that we have every day is that those costing mechanisms are correct enough.

And that we have provided our optimizer with good enough information about the data contained in our databases in order for it to come up with a good plan to answer our question. Costing will consider, and of course, the important one is first. Costing considers rows, right?

Cardinality estimation. How many rows are going to come out of this table? How many rows are going to qualify after this where and join clause? What’s the data distribution that we have from our statistics?

Costing will also consider the type of I.O. Because, you know, different types of I.O. cost different things to the optimizer. It’s random I.O. versus sequential I.O.

It will consider how much CPU effort it thinks will go into something. Of course, parallelism may reduce the cost of CPU effort because you’ll have multiple CPUs working on something. And, of course, memory requirements will also figure into things.

Costing is a highly generalized computation. Again, it’s based on one very old, very specific piece of hardware. And the point of that hardware was, well, I mean, the point of that hardware was for someone to do their job, obviously.

But the point of the costing algorithms was to come up with a good execution plan on any piece of hardware. So if you have two cores and 128 gigs of memory, or if you have 128 cores and two gigs of memory, the optimizer will come up with the same plan for either one because it doesn’t think about much of that.

If you ever want to see the old piece of hardware that costing was done on, it is here. It is this thing, right? It is this cantankerous contraption.

There might be like a where in the world is Carmen Sandiego CD stuck in there. There’s probably like an Encarta thing in here. Like it’s an ancient, ancient thing. Your servers at home, I mean, not your servers at home, but your servers like wherever they live.

And I would say at work, but they’re probably in some lousy cloud somewhere. But they do not have a lot computationally in common with this old hunk of junk, right? This old plastic thing here.

Thank you for showing that. That was very helpful. So now you know all the questions that I’ve answered. Lucky you. Anyway, all plan decisions are based on these costs.

Some costs are fixed per unit like CPU and IO. And there are other costs are based on things like statistics and metadata, things like table size, histograms, either system created or attached to indexes. Or I guess user created statistics would be another one.

And of course, uniqueness. Describing uniqueness to the optimizer can be a very helpful thing. Some things can really help the optimizer make better enough plan choices. So useful indexes, targetable predicates, unique constraints, enforced foreign keys, value constraints, up-to-date statistics, limited query complexity, a goal we should all strive for.

We yearn for non-complex queries, don’t we? And avoiding things that don’t have terribly good costing support. You know, XML, JSON, string splitting, built-in functions.

That means like left, right, substring, replace, up, down, reverse, that stuff. User-defined functions, local variables, table variables. And of course, the list could go on.

But we do have to go home someday. Okay. Row goals. Oh, come on back here. There you go.

Zoom it. Row goals are just another thing that affect costing and the query optimization process generally in SQL Server. So we’re going to stop here because we’re about at the 20-minute mark. And the next kind of section gets into the live demos and stuff.

So we have talked for long enough, right? We have gone through enough green text. Our eyes are now burning green and we need to go look at some execution plans. So I’m going to get this one uploaded and then I’m going to carry on from right here.

And I’m going to highlight that so I don’t forget because I’m probably going to go do something and then come back. All right. Thank you for watching.

Hope you enjoyed yourselves. Hope you learned something. And I will see you in the next video, which will be Row goals part two. Electric Goaloo.

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.

SQL Server Performance Office Hours Episode 37

---

* Does the order of INNER JOINs in a view impact how the queryoptimizer chooses how to build the execution-plan?

* Is there any performance gains when inserting into temp-tables to use the WITH (TABLOCK) hint? Thanks!

* Hey Erik! Is it data or data? I’ve heard some people saying data but I say data like you, so I think I’m right.

* 1. In our high-volume OLTP fintech environment on SQL 2017 EE, we have poor DB design leading to increasing deadlocks and blocking (readers blocking writers). As a short-term fix, we’ve set low deadlock priority on readers and high on critical writers, to avoid even more NOLOCK. We want to enable RCSI but management worries about safety, especially inaccurate results from in-flight version store data that might roll back. Previous DBA enabled snapshot isolation on a few DBs, but devs likely aren’t using it correctly or understand isolation levels. We have ample tempdb space (rarely used, as devs prefer table variables). How can I prove RCSI is safe? Dev env is limited—only some DBs match prod, with slower HDDs/CPU.

* 2. About 80% of our tables (millions to hundreds of millions of rows) have outdated indexes (5+ years old), despite daily dev work. Queries mostly from EF Core; servers reboot weekly (working to stop), losing index stats—but workload is consistent week-to-week. Biggest issue: slow upserts/deletes on tables with 50+ child tables (no cascading). I propose starting with narrow compressed indexes on FK relationships to avoid table scans. Tried sp_indexcleanup, but low uptime limits results beyond compression. Thoughts on this approach and overall indexing strategy?

To ask your questions, head over here.

Video Summary

In this video, I dive into some pressing SQL Server questions from viewers, covering topics like join order in views and performance considerations for parallel inserts. I also tackle the age-old debate over “data” pronunciation and delve into issues of deadlocks, NOLOCK usage, and snapshot isolation. Additionally, we explore strategies for optimizing upserts and deletes on large tables with many child relationships, discussing indexing foreign keys to avoid table scans. Whether you’re a seasoned SQL Server pro or just starting out, there’s plenty here to help improve your database management skills. If you found this content valuable, please like the video, subscribe to my channel, and share it with colleagues who might benefit from these insights.

Full Transcript

Erik Darling here with Darling Data, and you know as well as I do, it’s Monday, you know as well as I do that on Monday we do office hours where I answer five of your most pressing, I don’t know, impressive, depressive questions, I don’t know, whatever you have going on. And I don’t know, everyone’s happy. Everyone’s happy and everyone lived forever holding hands, right? Anyway, if you look down at this page, the video description, there are all sorts of useful links for you to click on and many of them will result in you giving me money. You can hire me for consulting, you can buy my training, you can become a supporting, and again the supporting with money member of the channel. You can ask me office hours questions, which of course free, you know, I don’t know, maybe I should charge like a dollar a question or something. Some of you ask a lot of, some of you ask a lot of questions and type in ways where I know it’s the same person, I’m like, ah, you owe me a buck. And of course, if you enjoy this content, please do like, subscribe and tell a friend.

I have some exciting conference announcements coming up, but of course I cannot announce them yet as they have not been announced publicly by the presenters. So you will just have to hold on to the very seat of your pants for those. But the one that I can talk about is past data community summit coming up in Seattle, November 17th to 21st, where Kendra Little and I have two days of the best T-SQL pre-cons you will ever see in your life. And with that out of the way, let’s go answer some questions. We will go to the magical Excel file.

And we’ll, we will do our best to answer these. And the first one is, does the order of inner joins in a view impact how the query optimizer chooses how to build the execution plan? So theoretically not, right? Because the optimizer is a cost-based situation and the cost, the costing model will look at your joins and it will, you know, assuming that you get past like, you know, into the right search phase of things. It will start reordering your joins to figure out what is the best way to do it. Where that kind of falls apart sometimes is that SQL Server will set a budget for the number of steps that it will take to optimize your query, which includes join reordering.

And you sometimes you’ll see what’s called an optimizer timeout. That timeout is not in time. It is in steps that it is budgeted based on the cost of your, the sort of like heuristic cost of your query based on complexity. And so SQL Server might not have time to reorder all of your joints. If you know the best way for your joints to be ordered, well, I mean, you’re in a, it’s in a view, so you can’t add a force order to that. But if you know the best way that you want your joints to be ordered, I don’t know, you could go ahead and write them in there.

And then if the optimizer does timeout, maybe it’ll just listen to you, but probably not. So in general, no, but there are situations where, you know, if you write them in some kind of order, the optimizer might timeout before it has time to reorder everything. But you’re still probably not going to see joins specifically written in the order you write them in, unless you have a force order hint on the query. I didn’t highlight that as I was, as I was reading it. I do apologize.

I’ve changed the width of my, my zoom it thing for various reasons. And, um, it’s a, it’s a bit, it’s a bit intrusive at times. Is there any performance gains when inserting into tent tables to use the width tab lock hint? Thanks. Yes. Um, depending on local factors, you might find that using a tab lock hint will result in a fully parallel insert in which the insert operator is within the parallel zone of the query plan.

If you don’t have a parallel query plan, uh, then that won’t happen anyway. There are also things that you can do that will mess that up, like having an identity column, having a clustered index, having a primary key, having any nonclustered indexes, um, referencing the table that you’re inserting into and like a not exists in the query. Uh, there are lots of things that will screw that up. Um, if you need something that behaves like an, an identity column and it’s important to you to have a fully parallel insert, just use row number instead.

No, no, no, no inhibitions on that one. So, uh, in general, yes, but also, um, the degree of parallelism can have a big impact on how fast things are. There are some queries where a higher parallelism will higher degree of parallelism rather will result in faster inserts and sometimes in slower inserts. Of course, the sweet spot is generally somewhere around eight, but you’re free to experiment with the max stop hint to see where that best aligns for you.

All right. This looks like a very important question here. Hey Eric, is it data or data? I’ve heard some people saying data, but I say data like you. So I think I’m right. Yeah. Sounds good to me. Good job. I don’t know any other way to pronounce it. So there’s, there’s some alternative pronunciation of data out there. Well, uh, it’s just, this used to be a proper country as they say. All right. In our high volume OLTP FinTech invite you work for FinTech and you’re asking me questions for free.

Oh, my God. The poor, starving consultant. Golly. I’m going to have to take a walk after this one. This is a paragraph.

My Lord. Why don’t you just hire a professional? Uh, we have poor DB design. Oh, gee. Is there a consultant around who could help with that? Leading to increasing deadlocks and blocking. Gosh, do you know anyone?

Is there a short term fix? Oh my Lord. We’ve set deadlock priority high on readers. Sorry. We can say I’m beside myself. We’ve set low deadlock priority on readers and high on critical writers. Why readers are always going to lose. They don’t take up, take up any transaction log.

Like it’s the reader is always going to be the victim. My goodness. To avoid even more knowledge. Oh, gee. Is anyone out there in the world good at talking people out of NOLOC? I don’t know.

The mind boggles. We’re truly puzzled. Is there a doctor in the house? Previous DBA enabled snapshot isolation on a few DBs, but devs likely aren’t… No kidding, they’re not. It’s getting real hard not to curse here.

Are likely aren’t using it correctly or understand isolation levels. We have ample temp DB space. Rarely used. Devs prefer table…

You! You are also wrong. Of course table variables use temp DB. What do you think they are? Magic? I have a million videos where I prove this point a zillion times. My goodness. Okay.

How can I prove RCSI is safe? Well, you’ve got snapshot isolation enabled for a few DBs. As you noted. Why not start having some queries there? Use snapshot isolation. Hmm. That’s a good one.

Only some… Oh, wait. Hold on. Is there… Is there more to this? I feel like there’s maybe more to this and I’m missing it.

Is there more? I feel like there’s something else going on here. No. All right. How do I prove RCSI? No, that’s the end of it. Yeah. Just… You could start by having some queries ask for snapshot isolation and use that.

That would be a good way. You know, that’s probably it there. Anyway. Okay. Yeah. Let’s…

Oh, you are… You… You… You numbered your questions. You… You… Oh, Lord. If I cry on camera…

This’ll be why. About 80% of our tables. Millions to hundreds of millions of rows. Gosh. Does anyone have experience dealing with data of that size?

I don’t know. Of outdated indexes. Well, how does an index get outdated? Does it have an expiration date? Does it go bad?

Despite daily dev work? Well, your developers, if they’re the ones from question number one, you should probably just drag them into an alley and shoot them. Oh, queries mostly from entity framework core. God bless.

Servers Ruby reboot weekly. Mother. Okay. Biggest issue. Slow upserts deletes on tables with 50 plus child tables. No cascading.

I propose starting with narrow compressed indexes on FK relationships to avoid table scans. Tried SP index cleanup, but low uptime limits results beyond compression. Yeah.

Um, indexing foreign keys is one of the, um, the least crazy things that you can do. At least in a database. Some databases are nice. And when you create a foreign key, they create an index for you. Indexing foreign key columns is not terribly controversial.

The thing to be explicit about here is that if you’ve already got an index that leads with the foreign key column or columns, there are some multi key foreign keys. We must include those.

Um, then you, you don’t need another index on that has that leads with that column or those columns. Um, so you don’t need specific index. You don’t like if you name an index FK, it doesn’t give like it magic powers.

The thing to be aware of is that the optimizer is still free to choose the execution plan for your foreign keys. Right? Like, like, like, uh, foreign key validation.

Like you could have all the indexes in the world. SQL Server could still choose a merger hash join with an index scan on the inner side of the join for the foreign key relationship. Sometimes you actually have to add a loop join hint.

Um, I’ve got a blog post about, um, sort of like, about this, but also about how sometimes triggers are, uh, are a more useful approach for this. Because you can control the execution plan of triggers in a somewhat easier way. But since you’ve already got all those foreign keys, you might, may as well, and just, may as well, as long as you don’t already have indexes that perfectly express that relationship, then, then you might want to try indexing them, sure, at your high volume OLTP fintech.

All right. Well, I’ve, I’ve got a bottle of four year old Will It Rye that is screaming my name right now. So we’re gonna go do that.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in the next video. Have a good one.

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.

Lost In Costs: Part 3

---

Video Summary

In this video, I delve into the complexities of query plans and execution costs, addressing common misconceptions about cost percentages and highlighting their limitations in accurately representing where time is spent. I explain how operator times can vary significantly between row mode and batch mode execution plans, providing practical examples to illustrate these differences. Additionally, I explore the concept of trivial plans and how SQL Server handles them, showcasing a demo that demonstrates the impact of simple parameterization on query optimization and plan caching. By breaking down these intricate aspects of SQL Server’s cost-based optimizer, I aim to provide viewers with a deeper understanding of what goes into query execution planning and help them make more informed decisions when tuning their queries.

Full Transcript

All right, Erik Darling here with Darling Data, back once again to where this is going to be the third and final video, regardless of length, in the Lost in Cost series, because I have to start something new, right? And so this is going to be the last one here. We’re going to finish it. And the next thing we’re going to do is stuff from my Row Goals presentation. You might see that hanging out right here. That’s going to be the next one that I go through. That should also be three or four videos. But anyway, what was I going to say? Very important. So the Row Goals presentation is a little embarrassing because I actually lifted a lot of the content for that, at least in the beginning, from this one. So there’s going to be sort of a double dose of Darling Data Delights when we start that one. It’s going to feel a little bit repetitive, but I promise you will be quite entertaining. If you didn’t tire of those jokes the first time, you won’t do it. And then you’ll be tired of them the second time. Anyway, when I’m talking to people about query plans, they will persistently look at query costs and percentages and things, and I will have to keep whacking them on the head and telling them to stop. And the reason for that is because costs will shift. Certain things will look free in one context, but very expensive in another context. So here’s an example. And if we run this query, and we look at the execution plan, we’ll see that SQL Server says 96% of the cost is here, 0% of the cost is here, 1% of the cost is here, and 3% of the cost is here, at least they add up to 100 this time, at least I think they do. But it’s interesting because you know, we spend 192 milliseconds here, that 96% of the time, no, this query runs for 522 milliseconds, so it can’t be 96% of that. This thing that costs 0% adds a significant number of milliseconds, so it can’t be 96% of that.

We go from 192 to 334. This thing that costs 1%, we go from 334 to 453. This thing that costs 3%, we go from 453 to 522. So these percentages don’t precisely line up with what takes time in a query plan. And then if we look at this query, which is slightly different. So in this one, I should probably point this out, is that we are partitioning by and ordering by the clustered primary key. So it’s already in order for us, we don’t need to sort that data. When we change our windowing function to use now the account ID column, which has no supporting index on it currently, oh, I should highlight the query, shouldn’t I? And we run this, well, all of a sudden, I mean, it takes a little bit longer, because we had more work to do, but that’s okay.

All right. So we scan the clustered indexes, but now this is free, right? This is 0% of the cost, but 636 milliseconds of the query execution time. This sort is 100% of the cost. And you know, I give it, it’s a good chunk of the execution time. But then this query runs, well, at the sort operator, we were at 2.1 seconds minus that. So like 1.5 seconds. And then we go to 2.182 here, but the query ends up being almost 2.4 seconds. And all these other things, thanks, Zoom, all these other things cost zero, right? They have zero costs, but time is still spent in them.

So cost percentages do not equate to exactly where the effort is spent. And the cost percentages are based on the operator costs. So you can’t follow those and get a good sense of where time was spent in a query plan. Right now, it’s worth talking about how to read operator times in different execution plans, because they are slightly different between all row mode plans and all batch mode plans, or at least mostly batch mode plans. So if we run this query, it’s going to do some stuff. And we are going to get some operator times, and we’re going to examine those.

So in a row mode plan, operator times are cumulative reading from right to left. I know it’s funny because this is my left hand. I know that because my watch is on it. But this one, I point this way because I’m a mirror image. This is left in the room, which is very strange because I’m pointing right to my body. Makes everything very strange.

But getting back to the point, since this is the first operator at the end of the query plan, we call it the caboose. The time spent here is just itself. Now, since this plan is all executing in row mode, then the 2.698 seconds here includes the 179 milliseconds here. Likewise, the 3.066 seconds here includes the 2.698 seconds here. And that will be true of all the operators going throughout the rest of the plan.

Right. So this thing didn’t execute for 3.1 seconds or 3.114. That includes the 3.112 from here. So that was like 2 milliseconds, really. And then the 3.112 to 3.100, that was really like 12 milliseconds. So it’s cumulative reading from right to left. There is a trace flag that makes row mode plans read a little bit more like batch mode plans, but we don’t have time for that now.

This query plan will execute mostly in batch mode. When I say mostly, it’s because the operator at the end here, this gather streams operator, this is a parallel exchange. There’s gather streams, distribute streams, and repartition streams that are the three parallel exchanges available in SQL Server.

And these do not support batch mode, at least currently. Who knows if maybe one day they will. Personally, I think they’re a waste of time in batch mode plans. So hopefully they never do. But batch mode plans are a little bit different.

So this gather streams did not run for 393 milliseconds. No siree, Bob. We already know that, though, because we just talked about that. The difference is that in batch mode plans, each one of these operators just times itself.

So this one really did run for 241 milliseconds. This one really did run for 147 milliseconds. This one really did run for 1 millisecond.

And this one really did run for 0 milliseconds. So all of the times that was here and here gets reflected here at the end because this is, again, row mode. Right? Whereas all the other stuff, well, actually, that’s row mode too, but that’s okay.

This is batch mode. The stuff, the individual operators after the caboose operator are all in batch mode. So even this filter is in batch mode. So batch mode operators, time themselves, row mode operators, they also include the time of their child operators.

At least that’s how SQL Server works today, unless you’re using a trace flag to make that different. So let’s get back to costing and stuff because it’s terribly interesting to me. One part of costing is the concept of a trivial plan.

Trivial plans are low cost, low memory, well below the cost threshold for parallelism. And SQL Server does not explore optimization opportunities for them. Often they are just so, like, there’s just such an obvious thing that it doesn’t go beyond any, like, just, oh, yeah, here’s a query plan, get out of my face.

So what we’re going to do is create a constraint on the user’s table. And that constraint is going to tell us, or rather, it’s going to tell the optimizer that the reputation column, the only valid values are greater than zero and less than 2 million, okay?

Once you hit 2 million reputation, they sit, like, hey, get a life. Go outside, go do something. Do some push-ups and some sit-ups.

Get out of here, right? You’ve been in the house too long. Get out. So if we look at this check constraints view, we will see that this constraint is not not trusted. Whoever named this column should be fired immediately into the sun.

Is it trusted or not? Is not trusted zero is a little hard mentally for most people to grasp, but I can assure you that it is not not trusted. It is trusted.

When we run these two queries right here, we might expect SQL Server to use the knowledge that is passed along via that constraint to maybe say, hey, there’s no data there for that because we don’t allow anything less than zero.

Everything has to be greater than zero. So if we tell SQL Server this, and let’s just run these two queries together, we’re going to get back, of course, this query returns zero rows up here, and this query returns 1,090,000 rows.

If we look at the two query plans together, something kind of funny happens, right? This query scans the entire user’s table, takes 212 milliseconds and finds nothing. This query does the same thing but finds 1,000,000 somethings.

So if we look at the properties of this, right, we will see that we got a trivial plan, and we’ll also see that this query has been simple parameterized. And because we got a trivial plan and because it was eligible for simple parameterization, SQL Server is going to cache this plan and maybe reuse it for other queries that do the exact same thing.

And other queries that do the exact same thing might have different literal values that we substituted a parameter for. So guess what?

SQL Server can’t cache a plan specific to zero, or less than zero, rather. All right, it’s because we would reuse that plan for reputation is less than two, and that would find a million rows, and that wouldn’t be safe at all.

If we add a silly little thing to the end of our query, one equals select one, all of a sudden SQL Server will go beyond the trivial plan stage of things. This will still return a count of zero, but it returns a count of zero in a much different way.

We just have a constant scan here. We didn’t touch the table at all. We didn’t do any I.O. to find zero rows. Also, our query was not simple parameterized, right? And now this query down here is like, hey, maybe a missing index would be…

Maybe if you added an index, that’d be great, right? We could figure some stuff out if we had an index, right? This one up here with the trivial plan, no missing index, right? Look at that execution plan, zero missing indexes, right?

Do you want that? No, I don’t want that. But trivial plans, the trivial plan giveth and the trivial plan taketh away. Anyway, let’s move right along here.

So let’s look at some other costing decisions that SQL Server might make. So this is a very fickle demo. So I need to run this once and actually look at the execution.

Okay, this turned out fine. Great. You know, it’s fun to do these fickle demos live and in person. I guess they’re good for a joke. But something like even choosing an aggregate, like choosing how to aggregate data, is a costing decision.

If we’re saying select the top 4,277 distinct reputations from this table, SQL Server can use a hash match flow distinct because it is less than or equal to the number of rows that it thinks it can aggregate things down to.

So we get this hash match flow distinct operator, right? If we do, well, we have 4304 here, right? We could do 4278 for this one, I think, right? Just do this and say 4278, right?

We run this. SQL Server will choose a hash match aggregate, not a flow distinct, right? So there is a costing decision based on the number of, even the number of rows that you’re selecting and aggregating, right?

So that’s another fun costing thing. SQL Server has another choice too. SQL Server could have gone and, oh, I mean, we could just do this, right? Let’s just make this a little nice looking. But we can actually force SQL Server to use a different type of aggregate.

But it had this choice, right? It had this choice available to it. It just costed it away, right? So we could have used a stream aggregate or actually two stream aggregates to do that. But we didn’t, right?

Because we would have had to sort data because stream aggregates require sorted data. So the SQL Server has many different things that it thinks about when it’s costing even something as simple as what kind of aggregate to use. There are three different search phases, but they’re not numbered 1, 2, 3.

They are numbered 0, 1, 2, like European elevators. And these search phases are based on sort of an initial cost given to the query based on various heuristics looking at like joins and group buys and like how complicated things are.

But the three search phases are 0, which is OLTP. You get some basic decisions about stuff and you get SQL Server might choose between nested loops and hash joins. Sounds kind of like an adaptive join thing, right?

Then you have search 1. And search 1 is where SQL Server starts exploring parallel plans and different transformations and some join reordering. And then you have search 2, which is really like everything. SQL Server throws the works at search 2.

And this is where some queries might have long compilation times and stuff. But when you write a query, very boring stuff happens that I don’t talk about ever, like syntax validity, object reference existence, maybe even some security stuff.

But then some cool stuff happens, right? After all the boring stuff. Yeah.

Wait, I went to draw a line there. That was supposed to be like this. There we go. Let’s cross that out. Boring stuff, right? Thumbs down. If I could draw a thumb. I can’t even draw a thumb in real life. Never mind with a mouse. But this is the boring stuff.

Then the fun stuff happens, right? Like simplification. Like SQL Server removing unnecessary joins and things. Figuring out cardinality estimation and join ordering. And then figuring out if we’re going to do trivial plan, full optimization, stuff like that.

The optimizer will look at your query and do good things for you. Like discard unnecessary joins, collapse, duplicative expressions, push predicates as deep as it can into the query plan.

They do sometimes end up in filters, which is unfortunate. It will also do things like match expressions. So things like if your query, if the expression in your query matches an index view or a computed column or a filtered index, it will start matching those things up and saying, oh, I could use this index view.

Or I could use one of the other things that I just said out loud. Great. We can also do something called contradiction detection. So if you have something in your query that obviously can’t be true, like where something is greater than five and something is less than five, there is no such thing that is both greater than and less than five.

SQL Server will throw a constant scan at you and say, go away, you ding dong. You have made something. You have made a terrible mistake. You can view, much like you can view the transformation rules, you can view the different optimizer things that the optimizer has done.

Optimizing things? I don’t know. Whatever. So if we run this query and we look at this, we will see all sorts of things that SQL Server has done, right, at various points to look at stuff.

We’ll even see the search zero, one, and two stuff that I just talked about, right? So search zero with the tasks and the time it spent in those tasks in search one and search two. So these things are all available to you to look at and maybe use to figure out, like, what the optimizer did when it got your query and went, oh, God, not again.

Wow. Why do they keep doing this to me? But one of those things might be discarding an unnecessary join, right? Like this, right?

SQL Server’s like, we don’t need that left join for anything. Why? Because we joined the post types column to the post table on two unique, well, I mean, they’re clustered primary keys on both of these.

This join is not technically correct for these two tables, but we’re doing a left join and we are not selecting any columns from the table we are left joining to. We are only selecting from this. So SQL Server says post table, no way, right?

So I’m not bothering with that. And it’ll also do stuff like I talked about with matching expressions, right? So if I say, if I create an index on reputation with a filter that says where reputation is greater than equal to, I think, is that a million or a hundred?

That’s a hundred thousand. And then I say, SQL Server, get a count from the user’s table, where reputation is greater than one million, that number, whatever’s going on in there.

SQL Server will match to our filtered index. And we will, SQL Server will say, that’s a good idea. All right, great. One thing that’s important to know is that missing index requests are often quite junky.

And I’m going to show you an example of that here, where we’re going to run this query, which has a scalar UDF in it. And we are going to, you know, obviously the scalar UDF makes things a bit funky, but we’re selecting the top three rows from the user’s table, where reputation is greater than 100,000.

I should really learn my zeros better. And order by last access date. And if we look at the execution plan for this, SQL Server said, well, guess what? We could reduce the impact of this query by 83.684% by creating an index on the user’s table.

The thing is, this query ran for five and a half seconds, but only 175 milliseconds was spent scanning the user’s table. Would I add an index to fix this?

No, but it’s very, very misleading. Why? Because this costs 97% and this costs 0%. You might look at this query plan out in the wild and say, golly and gosh, we need that index. We’re not going to survive without it.

It’s a sink and ship, not knowing the whole time that all the problem was in the scalar UDF and this compute scalar operator, right? Very dingy times here.

So if you see missing index requests in the wild, please don’t just add them all, right? It’s not a terribly good practice. They’re not terribly smart. They’re not terribly thoughtful. There’s all sorts of stuff in here that you can read when you diligently download the demo script that I’m going to provide to you and all that other stuff.

So please just don’t do it, right? There are things to pay attention to in query plans, right? So the stuff that we care about, operator times, wait stats.

There are wait stats in query execution plans. There are session level wait stats and sys.dm exec session wait stats. Estimated versus actuals of all variety, rows and things like that.

Spills. Another good thing to pay attention to, right? Especially batch mode spills. Batch mode spills are the worst. They take forever. Batch mode sorts are the devil.

And of course, parallel thread distribution. So we’ve looked at operator times a bit so we can look at the other things a little too. So this is a good example of wait stats in a query plan, right?

So we’re going to run these two queries, right? We’re going to run them back to back or neck and neck or something like that. And they both do the same thing, right?

They both do the exact same thing. But, you know, one of them takes a little bit longer than the other. When we go look at the execution plans for these, I wish there was a way to just make this not have to be fussed with. All right.

We look at this first query. This first query takes 6.3 seconds. The second one takes 695 milliseconds. Why? Well, for this one, we can go figure it out with the wait stats, right? Like, why did this clustered index scan take 6.287 seconds and this one takes 6.94 seconds, right?

Why? These mysteries reveal them to me. Well, if we right-click on the root operator of the query plan and we expand the wait stats down here and we look, this query spent almost 39 seconds waiting on page IOLATCH SH or reading pages from disk into memory.

This query, well, its top wait was 698 milliseconds on CXSync port. So some kind of parallelism stuff. First query took a long time reading from disk.

Second query was fast reading from memory. Shocking, I know. But you can start to figure this stuff out by looking at wait stats instead of puzzling over costs and percentages and other goofy things. And then there’s this query, right?

This query right here. This query is a funny one, right? So we’re going to declare a couple local variables up here. Most obviously one called top, which is a big int, which is equal to 100 times 358.

And this one down here called dummy, which is also an integer equal to zero, at least until we do something down here. And then we’re going to tell SQL Server to get the top number, this number of rows. And then we are going to say to optimize this query for top equals one, right?

So now we’ve done something quite devilish to SQL Server. And this is all quite funny, right? What happened in here?

Well, we had a big spill, didn’t we? Look at this big spill, right? All right. Hashmatch flow distinct, our friend. Hashmatch flow distinct, it spilled on us.

All right. And we can see that this thing took 7.983 seconds. And this thing took 26.8 seconds minus 7.9 seconds. And if we hover over this, we can see that this thing did quite a bit of spilling.

All right. Spill level four spilled one threads. So that took quite a while. So pay attention to things like spills and query plans. When weight stats aren’t useful is when you have parallel, at least when weight stats that you see in the query plan are not useful, there are other useful places to get them, are when you have parallel thread distribution issues.

So let’s put this stuff into a new window, I guess, because it’s telling me to. All right. We all do that.

Oh, you can see I’m almost done here. Ah, nuts. Not thank you yet. So let’s put these into a new window. And oh, the index finished.

It’s our lucky day. Let’s turn on query plans, right? So I’m going to run this query. And then I’m going to use this session weight stats view that I’ve been talking about so much, right?

Because this is a very useful view. We’re going to run this query. It’s going to take a little bit to run. But you might infer from the name of the view that we’re selecting from parallel, but serial that we’re going to get a parallel plan.

But the parallelism is not going to be very effective or efficient at all. All right. It’s going to be terrible.

So we go look at our execution plan. We will. Oh, come on. Give me the grabby thing. Come on. Give it to me. Give it to me. Why are you being shy?

If we look at this execution plan, we’ll see that there is a serial zone in the plan over here. And then we distribute streams over here. And then we go into a nested loops join here. And we go into an index seek here.

And we go into a key look up here. Okay. All fine. All well and good. The problem is when we distribute streams, right? We use something called, what is it?

Round robin. Sounds good. You know, nice round robin. Everyone loves a round robin. We wouldn’t want a square robin. But the problem that we run into is that the rows, ah, that’s the wrong one. There it is.

All these rows end up on thread one this time, right? They could end up on a different thread another time, right? So that’s not fantastic for us. And then this pattern will continue throughout the query plan too. Oh dear.

I’ve lost all sorts of context here, right? So we come over to this side and I should have highlighted one of these not to lose it. There we go. We still have all our rows on thread one. And we still have all our, well, now we have 27,000 rows here. And now we go to here and now we go to here.

And now, but now something magical happens. After all this stuff, right? We’re like, like coming up on this one. Oh gosh, the compute scalar is ruining my life. If we look at this one and the actual number of rows here.

So all 27,000 are still on one thread here. But then we have this repartition streams and repartition streams does, I mean, almost exactly what it sounds like.

And it repartitions our rows around, right? So if we look on this side, at least if the demo gods are working with me here today, now we have a little bit of thread distribution throughout, right? Not great.

That one’s got zero, a couple with ones and some fives and a six. But at least it’s not all on one thread. Granted, it’s not that many rows at that point. But the repartition streams helps to redistribute rows on parallel threads so that we don’t end up with all of them in one place.

Getting back to my point, though, if we look at the wait stats in this execution plan, right? If we right click on this and we hit properties and we go to wait stats, we are not going to see anything terribly useful to us, right?

We’re going to say, well, I mean, what is a CX sync port? What is, I mean, we know what SOS schedule yield is, right? Query scheduling, all that.

Why are we reserve memory allocation xing? I don’t know, right? Like nothing in here gives us a good reason for why things are slow. But when you have a parallel query where parallelism is very lopsided, something that the query plans won’t show you is that you waited a long time on something called CX consumer, right?

This thing right here, CX consumer, the wait time, 138.966 milliseconds. And this is a very, very good sign that you have unbalanced parallelism in your query plans. So if you have a singular query and it’s parallel and you look at the wait stats for it, not in the query plan because Microsoft screen CX consumer out.

Why? I don’t know. Ask Salesforce. This is going to be why, right? Because this is the CX consumer. We were not properly consumed.

So you can see that there, but you can’t see that in the query plan, right? So if you see CX consumer weights looking at the wait stats for a single query from like the session wait stats view, then you know you have unbalanced parallelism and you should be right clicking around looking to figure out where the rows are, where things are unbalanced.

But now we will do the actual thank you, right? So this is where I normally would finish things out and say, hey, good job. You made it through. I’m happy for you. I’m proud of you.

You can get in touch with me here if you want to hire me or make fun of me or call me names or something. But this is the end. This is the, again, the final installment of Lost in Cost. So we’ve done it.

High five. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you most likely in an office hours video after this. All right. Goodbye.

Going Further

---

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

Lost In Costs: Part 2

---

Video Summary

In this video, I dive into the fascinating world of query optimization in SQL Server, focusing on how rules and heuristics drive the process. We explore the often confusing structure of estimated execution plans versus actual ones, highlighting why certain costs remain estimates even after running a query. By walking through examples like reputation lookups and index scans, I illustrate how SQL Server’s cost-based optimizer can sometimes make decisions that don’t align with modern storage capabilities or our expectations. The video also touches on the nuances of random I/O costing, covering topics such as key lookups, bitmap indexes, and the impact of select star queries, all while emphasizing the importance of understanding these intricacies for effective query tuning.

Full Transcript

Erik Darling here with Darling Data. And because you are such data darlings, I’m going to skip over the usual intro. We are going to start with part two of the Lost in Cost series. If you just stumbled upon this, maybe you should probably watch part one first if you haven’t seen that already. And after this, well, I mean, depending on when you stumble upon this, remember that? Remember that website stumble upon? What a nightmare. But depending on when you find this video, there might even be a part three available, which would be the final part. So lucky you wouldn’t get to see me three times. Doesn’t get much better than that. Anyway, where were we? Right? Query optimization. Query optimization is driven by rules and heuristics. Early on, queries are mapped as these funny little tree structures, something like Bob Ross type tree structures. And those structures are pattern matched to various rules that are pattern matched to various rules that the optimizer has to figure out different things to do. It might do with your query plan in order to make it very cheap, very cost effective. Those rules provide different logically equivalent plans. Things like the placement of aggregates, the placement of predicates and the placement of joins are all it can all be affected by these rules. But if you ever want to see them, there’s this great DMV called sys.dm exec query transformation stats.

And if you look at it, you can see the names and birthdays and social security numbers of all these different rules that the optimizer has at its disposal to come up with query plans for you. Right? And if you scroll down through them, there are some pretty funny ones down at the bottom here. This is one of my favorites is top on empty. That’s a good one. I’m not sure why the O is capitalized for that T up on purge, whatever. Anyway, Paul Whitehead, has a great video about all these things you should watch that sometime. Anyway, when you get an actual execution plan, there are lots of operators that will have required estimates for cost based optimization. And they will have information added to them that will reflect actuals for a number of things, a variety of metrics. Costs are not one of them. Right? So you will see things for rows and potentially batches and executions and stuff like rebinds and rewinds. But let’s use this query, very simple query as an example. If we get the estimated plan, I’m only hitting control and L for this one. Right? You can tell that it’s control and L because we don’t have the operator times in here.

Right? But if we look at this and we zoom in, we will see some estimates, right? Estimated IO, subtree and CPU cost. They will be 65, 72. This is another one that gets me. It’s like, why wouldn’t you put like the main cost at the top? Now you have to like, I have to add 65 to 7.2 to get… It’s nuts, right? Who designs these things? But the important thing here is to sort of remember these numbers, right? 65, 72, 7.2. If we run that query and get the actual plan, right? I assure you this is the actual plan because we got a result back, right? It counted zero rows. And we look at this. Now we have operator times and stuff. So this actually did a thing. We will have… This tooltip got magically bigger. And it got magically bigger because now we have these actual things in it, right? We have these things that are actualities in the plan, including this one, which doesn’t actually say actual, but we must infer that they meant to put actual in there.

And maybe someone was just a little drunk that afternoon. We don’t know. But there are also some estimates down here. Some of the estimates down here correlate to actuals up here, which is also a bit confusing, but you know, we’ve all had bad days. But the important thing that I want to show you is that these costs remain estimates and these costs all remain the same, right? We have 65, 72 and 7.2, right? So that’s what we get. Estimated costs, no actual costs. Some costs and estimates and stuff might look wrong, right? I think a great example of when this happens is when maybe some things happen after initial cardinality estimates.

Like sometimes SQL Server might add something to a query plan, perhaps like a bitmap, right? Which gets created up here, but then gets used down here and can affect cardinality, right? So SQL Server expected to read 2.4 million rows, but actually only got, only actually read 40,000 rows. And that’s because this bitmap got used up here, passed through the hash join, passed through the repartition streams, and then finally applied as a predicate when SQL Server was reading data down here.

So SQL Server actually could read fewer like pages and stuff with the bitmap in there. The optimizer is heavily biased against doing random I.O. Why?

Well, if you remember from the first video, if you did not get too drunk between videos, then you will remember I showed you a picture of a computer. And the hardware within that computer was not nearly as powerful as the hardware that you might have today. Maybe not if you’re on Azure, but in general.

And the storage in particular on those things was pretty bad. And when you did random I.O. on old spinning hardware, things that looked a little bit more like record players in the storage you might know and love from today, there was a big physical penalty to going around and doing random I.O.

This is why back when people were on storage hardware where things would have to move about, then things like index fragmentation and page splits and stuff were a much bigger deal. Right?

On modern storage hardware, far less of a big deal. Unless you’re in the cloud. Who knows? Right? So I’ve got this index on the reputation column. I’ve already got that there. And what I want to show you is sort of, I mean, some people might call it like a tipping point demo.

This isn’t really a tipping point demo. This is just a more, I’m going to show you like costing stuff. So if we look at this query for reputation equals nine, SQL Server uses a key lookup.

Right? It seeks into our nonclustered index. It takes one row at a time from there. It goes into a nested loops joint.

And because it’s a loop, we’re doing random I.O. at this point. And then it goes into the key lookup down here to fetch all the columns that I’m selecting from the users table that are not contained. And that’s one single key column index on the reputation column.

So, you know, that’s what we got there. Nice key lookup plan. Probably not a key lookup plan that I would want to fix.

It takes about 27 milliseconds. Right? So I don’t think adding a covering index and trying to reduce the impact of this query by 99.8% is well worth my time. But let’s continue looking at the costs.

Okay? That’s what we’re here to do. So if we run this query and we say, hey, SQL Server, I want you to go find reputation 51 now. SQL Server changes its mind about a few things.

SQL Server decides, well, I’m just going to scan the clustered index for this one. Doing a key lookup would be too much work for me. Right? And this takes about 200 milliseconds. Hmm.

We went from 27 milliseconds to 200 milliseconds. SQL Server, I think you might be incorrect about some of this random I.O. stuff. If we were to force SQL Server to use that narrow index, right? We would apply an index hint here.

SQL Server would say, well, guess what? I can use that index and I can finish this query in 33 milliseconds. So costing around random I.O. is often wrong.

And there’s often, at least, you know, again, newer SQL Server, you know, local factors apply. Often random I.O. is not the terrible enemy that it used to be. Now, I know what you’re saying.

Eric, Eric, darling. Please. Why are you using select star? Why are you selecting star? Surely, selecting star must be a terrible, must be a terrible thing for you to be doing in front of all these people. And you know what?

You’re right. The thing is, excuse me, the thing is that SQL Server costs that random I.O. the same, whether you are saying select star or just selecting one additional integer column. If we look at these two queries and we force SQL Server to use our index on reputation, when we look at what gets returned, we can see up here, clearly, this is select star.

We have all of the columns from the user’s table. And clearly down here, we have just two narrow integer columns. These two query plans, though they look identical in many ways, will also have identical costs, right?

So it’s not about 37.67 query bucks and 37.6, well, 6767 for both of them, right? 37.6767. So random I.O. cost is not at all affected by, like, perhaps the amount of work that each of those random I.O.s might have to do getting more data from the clustered index.

SQL Server costs them the same if it’s one column or all the columns, right? It’s kind of a nutty thing. Now, of course, it’s not a very good idea if one were to go around, right?

Maybe all your queries are select star for a reason. I don’t know. Maybe you have a great reason for doing that. It would not be a good idea for you as a responsible, seasoned, qualified database professional to have every nonclustered index include every column that you have in your table, right?

What’s a cluster? Because we think about maybe what a clustered index is, right? A clustered index is every column in the table logically ordered by the clustered index key column or columns.

If you think about what a nonclustered index is, well, it is every column in the includes, if you have includes, right? You don’t, includes are optional, ordered by whatever the nonclustered index key columns you assign to that index are. If you’re the type of crazy person who would create a nonclustered index with, you know, n number of key columns, and then you included every other column in the table, it would be essentially a second clustered index, like just in name only, right?

Or rather, the only, it would just not in name only, right? Because it would be every column in the table logically ordered by the nonclustered index key columns. It’s not a good practice to do, right?

It’s not a good thing for you to be doing. I’ve explained that in another video, so if you care, go find those. Anyway, let’s look at another example of SQL Server hating random I.O. Okay, it’d be a good time.

Oh yeah, we’re creating indexes. Now, there are times when higher cost plans can be faster, but of course won’t be chosen because of the perceived expense of doing random I.O. by the optimizer.

That’s obviously not a good situation for us to be in because we would expect queries to be as fast as possible. But they’re not always because SQL Server often makes wrong costing decisions because of the random I.O. bias. So if we run this query and get the actual execution plan, returns one row, right?

Fine. And it takes 904 milliseconds in total. Okay, well, is that good?

Is that bad? Was that ugly? I don’t know. How do you feel about it? 900 milliseconds. Probably not the end of the world. Probably not the worst query you’ve ever seen. But if we tell SQL Server, right? Actually, there’s an important part here.

SQL Server scans the nonclustered index that we created on the POST table, but scans the clustered index on the user’s table, which is not what we would expect because we just created an index on the user’s table that helps our query quite a bit.

If we say SQL Server, well, could you pretty please use my index, right? So we’re going to tell SQL Server, I would prefer if you use this index. All right, remember, 900 milliseconds.

Well, that sure felt a bit snappier to me. And it was, right? We got that in 170 milliseconds about. Now, of course, SQL Server chose a parallel plan here, which, of course, helps the final speed of it a bit.

But the reason why it chose a parallel plan was because of how high it costs doing the random IO associated with the key lookup, right?

The initial query was not… Oh, I meant to scroll up there. Actually, let’s run these together. Let’s make sure we do the right thing. If we look at these two queries, the first one has a cost of 30 query bucks.

All right, okay. Cheaperoo. The second one has a cost of 76 query bucks. So this one obviously wasn’t chosen because it has a much higher cost. But the much higher cost led to a parallel plan, led to a faster query.

I’m not saying parallelism is precisely why this one is faster, but it’s just interesting to note the plan changes because of the higher cost here. So there we have sort of an interesting thing, right?

We have SQL Server choosing a slower plan that does not do random IO because the plan that would have done random IO would have cost more and would have been parallel.

So it’s like, ah, it’s crazy, right? And like, it didn’t consider… It didn’t think that the parallelism would reduce the effort of this plan enough to make it worthwhile. Kind of a funny thing. Anyway, sometimes when we want to figure out stuff about parallelism, it helps to start with understanding how SQL Server chooses a parallel plan.

Sometimes it helps to start way back at the beginning. And for me, that beginning is, of course, looking at the cost threshold for parallelism setting, which I have set to 50 here locally.

And what I want to show you, actually, we’re going to look at just a saved plan. Again, this demo, I’ve been burned by it too many times live and in person. So we’re going to…

You and I, we’re going to look at this thing. Saved. All right. So up here, we have a query that’s selecting the top 1892 rows from the user’s table joined to the post table.

There is no hint applied to this one, right? If we look at the query plan itself is sort of immaterial. But what I want to show you is that this thing has a cost of 49.9968 query bucks.

All right. So that’s about as close as you can get to 50 without hitting 50. All right.

And maybe having SQL Server think about a parallel plan. If we look at this query and say we want the top 1893, right? So that’s one more than the 92 plan up there. And we look at this.

It has a cost of exactly… Well, 50.0038 query bucks. But this query has a maxed up one hint on it. So we said SQL Server, you’re not allowed to make this query parallel.

Okay. So keep that in mind there. The one at the very bottom is the same select top 1893, but you’ll notice that there’s no maxed up one hint over there. This query has a cost of 44.5187 query bucks.

That’s quite interesting, isn’t it? Something that trips a lot of people up around costing is especially parallel plan stuff is they expect a parallel plan to have a cost that is higher than the cost threshold for parallelism, right?

After all, SQL Server chose parallelism. The cost of the query must be so high that it’s above the cost threshold for parallelism setting, but that’s not true. See, every query plan starts as a serial execution plan, right?

Parallelism is explored later, potentially, if the cost of a serial query is higher than the cost threshold for parallelism setting. And that’s what happened here, right?

For the top 1893 query, we set maxed up to one, right? We said no parallelism, and the cost of the serial query was 50 query bucks. When we ran that query without the maxed opend, we said, hey, SQL Server, do whatever you want.

Go crazy, pal. And what did it do? Well, it said 50 point something query bucks. Hell yeah. Parallel plan exploration. And SQL Server looked at the parallel plan and said, hey, you’re pretty good.

I like you. How much do you cost? And the plan was like, I cost 44 query bucks. SQL Server said, I’ll take it. So all the parallel plan has to do is be cheaper than the serial plan, right?

The parallel plan doesn’t have to have a higher cost than cost threshold for parallelism. It only has to have a cost lower than the serial plan that it started off with.

Very interesting stuff there, I know. We’re going to pause here, right? That’s a good logical stopping place, I think, for this. We’re also nearing the 20 minute mark.

And, you know, I see the watch times on these things. You squirrel people. Anyway, thank you for watching. I will see you in part three, where we will finish up all of this grand and groovy material. 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.

Lost In Costs: Part 1

---

Video Summary

In this video, I delve into the concept of “Lost in Costs,” a term that highlights how query costs can sometimes mislead us when tuning SQL Server queries. I start by explaining what query costs are and why they should be treated as estimates rather than definitive performance metrics. Using my favorite demonstration, I illustrate how even simple changes like adding a hint can drastically affect execution plans and performance. By walking through this example, I aim to show that while high-cost queries aren’t necessarily slow, understanding the underlying costing mechanisms is crucial for effective query optimization. Throughout the video, I also touch on various factors that influence costs, such as row projections, I/O operations, CPU usage, and memory requirements, emphasizing that these are all part of a highly generalized set of algorithms designed to generate good-enough plans across different hardware configurations.

Full Transcript

Erik Darling here with Darling Data. And it occurred to me while I was traveling overseas, while I was being the European correspondent for Darling Data, that there was some session material that I had presented that I had not yet recorded. And that includes this session that I’m going to do now, which I presented a few different places. And the Rogel session that I presented at the Pass On Tour events. So the next few videos, aside from office hours, are going to focus on those. So that’s a good time there. So this is the lost in cost, lost, actually, I don’t know if that’s supposed to be plural. Let’s skip that. Anyway, as usual, down in the video description, all sorts of helpful links. If you want to hire me for consulting, purchase my wonderful top-notch, best-in-class training materials at a reasonable price. Become a subscribing channel member. Ask me office hours questions. You can do all that down yonder. And, of course, if you enjoy this content, please do like, subscribe, tell a friend, all that good stuff. Since I just got back from Utrecht, I trekked to Utrecht, and I trekked all the way home.

The only thing that I have left through the end of 2025 as far as speaking engagements will be the Pass Data Community Summit taking place in lovely Seattle, Washington, November 17th to 21st, where I have not one, not two, actually exactly two, days of T-SQL pre-cons with Kendra Little. So that’s great for everybody. Except you if you’re not going. So that’s your fault. Anyway, it is still October, so we can still have this lovely Halloween database party going on, can’t we?

All right. So, oh, that’s a web browser. So if you ever wonder, this is how I generate my ASCII art, and this is the last thing I had to generate ASCII art for. So we’re off to a good start. Anyway, oh, see, it is plural. I didn’t mess it up. Anyway, this is going to be part one of Lost in Costs, and we’ll talk all about what this means coming up.

But, you know, so since I’m a professional presenter, I always have my contact info up so people can bother me, but we don’t need to talk too much about that. Because you are already at this one, and you might have gotten a link from this one or this one to get here. So it’s not a lot of point in that, is there? So the first question that we must ask ourselves when we are looking at query plans and we are trying to figure out exactly what we are trying to tune about a query, we must ask ourselves what are query costs. And of course, query costs, no matter where you are looking, are estimates.

Right? So cost does not equal time or anything else. They are unitless measures. Right? They are not durable performance metrics in any way, shape, or form. Even in actual execution plans, costs are all an estimate. There are no actual costs derived anywhere during query execution.

SQL Server does not change its mind and say, oh, I thought that was going to cost 25 cents and it costs $70,000. My bad. It just doesn’t happen. Costs are merely how we got to the plan that we are currently looking at. All right? So when you think about how SQL as a language works, you tell SQL Server what rows you want, what columns you want, which order you want.

Maybe you might even say, summarize these columns to the group by or something. But you basically tell the database, I’m looking for this, and the database has to go find it. Boy, I should probably fix that at some point. Right? It’s not 2024 anymore. Let’s pretend that’s a five.

All right. I obviously didn’t go through this with too fine of a comb before deciding to record it. Anyway. So queries are really just descriptions of what you want to see. Right? They are just a description of the data that you require from the database.

And then the job of the query optimizer is to figure out how to best enough find it within a reasonable amount of time. It’s a bit like how indexes contain data and statistics describe data. Databases contain a whole bunch of data and we describe what data we want to see from them.

Costs, as we know and love them, are just a bunch of internal algorithms that SQL Server uses to shape and choose the execution plans that it ends up executing. Right? The hope is that all those costing mechanisms are correct enough and the optimizer has correct enough information about the data underneath.

Right? The statistics, statistics, histograms and things like that to get you a, well, let’s just, let’s call it a good, a good enough plan. Right? Good enough plan found here. But all of this is to answer the question your query is asking as efficiently as possible.

Costing considers all sorts of fun things. Right? So like the number of rows that are going to like project out from various things. It might be a seek or a scan.

It might be like a where clause. It might be a filter. It might be a join. Like how many rows are going to come through stuff? What kind of IO is being done? Because there is different costs involved or there are different costs involved for random IO versus sequential IO.

CPU effort is also a factor in this. Parallelism is considered a CPU effort reducer because you have multiple CPUs working on the same set of data. Right?

Rather than having one CPU deal with all of the giant amounts of data that you are reading through. And of course, memory requirements are part of that as well. Cost is all just a very highly generalized set of algorithms. The thing that I always have to tell people is that cost has nothing to do with you.

Nothing to do with your hardware. It doesn’t matter how much your hardware costs because the costing algorithms are not looking at your hardware and thinking, Oh, that’s a nice computer.

I bet this would be easy. It just doesn’t do anything. It’s meant to come up with good enough plans on any set of hardware. Right? That is the whole point of a generalized cost-based system. So what I always like to show people is this image, which is, of course, fondly referred to as Nick’s computer.

There’s a note there about SP2, I think, of something. I don’t know SP2 of what. But there might be like an original SQL Server activation code on there.

But this is the computer that all of the costing stuff was originally done on. Now, aside from some very generic terms like CPU or motherboard or RAM or disk or something like that, that has very, very little in common with the type of hardware that SQL Server runs on today.

At least we hope it does. We’re not sure what’s actually going on up in Azure. It could be a whole farm of those things. We don’t know.

They won’t tell us. But anyway, all planned decisions are based on these costs. Some costs are fixed per unit, like CPU and I.O., like a CPU and an I.O.? N.I.O.?

N.I.O.? Whatever. Other costs are based on statistical information that our databases contain, which describe the data in them, like the size of tables, the histograms that are attached to columns or indexes. And, of course, uniqueness is a very good descriptor of data that we might have.

There are lots of things that we can add to our database in order to better describe it or better contain data for it, sometimes more efficiently. Useful indexes.

Useful indexes. We can write our queries with sargable predicates that do not require extra work to locate rows that we care about. We might want to think about adding unique constraints or even value constraints or, you know, unique indexes would be something else. We might want to consider having up-to-date statistics.

At least some of us might. And we might want to even do something as crazy as limit query complexity so that cardinality estimation is easier for the optimizer. We may want to even avoid things that do not have good costing support.

Maybe something like XML, JSON, string splitting, built-in functions, user-defined functions, local variables, and table variables, just to name a few off the top of, well, not my head anymore. It’s just stuff that I wrote down quickly.

So I’m going to start this off by showing you one of my favorite mis-costing demos in the world. Since I am smart, and I’ve made a note saying if I’m smart, but I am smart. So I already ran this.

This is like, this is one of like my grand mal, the optimizer is not always right type of demos, where I have two queries that do essentially the same thing, except one of them has a hint, not this one. This one does not have a hint, right?

There’s no like with or, you know, hey, do this thing here. And then this one down here, which does have a hint, it uses the force seek hint to tell SQL Server it does not always know better. But if we look at the query plans for this, we are going to see two, well, not very different, but different enough to make a big performance difference.

For example, zooming in over here, we can see that the first query does not have a force seek hint on it, ran for 25 seconds. And the second query, which ran for about one and a half seconds, this did have the force seek hint on it.

Now, what I want to point out before I go too much further is that what’s going on in the outer reply is a correlation. And the correlation is from the users table, which has a unique, sorry, a clustered primary key on the column called ID. It’s an integer, it’s an identity.

And I am correlating that to the clustered primary key of the post table, which is also an integer and an identity column. They are very, very similar columns in most ways, aside from which table they belong to. If you’ve been poking around the Stack Overflow database long enough, you’ll realize that this is not how you join users to post.

This is not how the users table correlates to the post table. So it would correlate either to the owner user ID column or the last editor user ID column. But I wrote this demo specifically to show you a failing of the optimizer, where essentially joining two tables together on two unique primary or clustered primary keys results in a really strange execution plan unless you supply a hint to it.

So what make what really slows this first query down is SQL Server looks at this, looks at this and says, hey, you know what? Man, what I’d really love to do is build an index off the index that I already have. SQL Server says, nah, you know, that clustered primary key wasn’t good enough for me.

I need a brand new index and I’m going to store that index in tempDB and we’re just going to hope that this goes well. Right. And of course, it doesn’t go well.

You can see that this like most of the time in this plan is spent here. Right. There’s about five seconds scanning the clustered primary key on the post table. And then since this is a row mode plan, right, there’s an additional 20 seconds here adding up to 25 seconds total.

Right. So we had we spent a lot of time building the spool. It’s a 17 million roll spool.

Part of the reason why the spool takes so long to build is because when eager index spools are doing their thing. Oops, that’s the wrong one. That’s the right one. What happens is all of the all of the rows end up on a single thread. Right.

You can see the rest of these. These are all zeros for the rest of the threads involved here. And this this isn’t just a quirk of this demo. This is every single eager index spool that gets built. Not a good choice, SQL Server. Right.

For the fast plan, SQL Server said, oh, yeah, I guess I could just use that index. Right. I mean, it’s a little it’s a little strange because SSMS does this thing where it cuts off the name of the like or like rather the what like certain operator text. I know Plan Explorer used to do this better, but I can’t use Plan Explorer anymore.

Functionally worthless for this type of activity. But this is a clustered index scan. Right.

And this is a clustered index seek because we said, hey, SQL Server, please force seek. Please force a seek into this index. And so it does. And we can when we tell SQL Server what to do, we end up with a much faster plan. Right.

We don’t spend 25 seconds in this sort of zone of the world. We spend about one second in this zone of the world. All right. And this is because when we ran this query, SQL Server applied a bunch of costing algorithms and said, I can make this faster by creating another index. No, you can’t.

No, you can’t. SQL Server. People always want to do ridiculous things with the various places that SQL Server stores query plans. Right.

So, you know, like back in the plan cache days when I used to do a lot of work on SP Blitz cache. And when I first started working on Quickie Store, which uses Query Store, people would say all the time, hey, I would like to order this result set by query cost. And I always say, why?

Say, well, I want all my expensive queries first so I can make them less expensive. Like, OK, well, you can have expensive queries that run very fast. You can have inexpensive queries that run very slow.

Query cost has nothing to do with how fast your query actually runs. Right. It’s just the amount of effort that SQL Server expects to use to run it. But expects and actually happens is two very different things.

Right. That’s the old everyone has a plan until they get punched in the mouth thing. So I’m not saying that you can’t take a high cost query and make it faster. That’s certainly untrue.

Right. Tune things all the time. High cost, low cost doesn’t matter here at Darling Data. But it does bring up an interesting question. If costs are stupid, what should we look at in our query plans? Well, the first thing that you should find are maybe some things that aren’t even the query plans themselves.

Right. Maybe you should look for things that run at times that you care about. Right.

But once you start finding those things, the stuff that I usually go after are things that have a high average or maximum CPU or duration. You know, there are a lot of metrics that you could look at to figure out various things about a query. Right.

You could look at reads if you were that type of person, but you would be reading the wrong thing. Because reads don’t often tell the story of how long a query executed for. If you do a bunch of logical reads, it’s stuff that’s already in memory.

It’s not going to be particularly slow. You might coincidentally reduce the CPU or duration of a query and also reduce reads. But that’s not necessarily guaranteed.

Not really something that correlates incredibly highly. But anyway, the things that I generally look for, because the things that people appreciate you doing when you tune a query is making it faster. It’s a great way to figure out if something is slow.

Look how long it runs for. Right. Look how much CPU it uses. These are the things that you can use to tell it like, hey, I made that this query took 30 minutes. Now it takes three minutes.

Right. I reduced 27 minutes from this query. You might not reduce a single read from that query, but you might greatly impact the total duration. You might want to maybe even focus in on stuff that executes a lot.

I don’t know if you’re into that sort of thing. If you’re very OLTP-ish. So once you figure out what you should tune. Right.

Not what you want to tune. Because there is often a pretty big gap between what people want to tune and what they should be working on. Right. It’s, you know, there’s lots of stuff that I should be doing right now, but I wanted to do this. So we’re here.

Right. But generally, you know, and I’m not saying that there’s no such thing as like a red flag in a query plan. Because there are certainly some query plan patterns that I can, when I see, I’m like, oh, that’s, that’s probably it. But getting an actual execution plan is the most valuable thing that you can do because that will tell you where the, like where the query is spending time.

No one is going to complain about the number of logical reads that a query is doing. People are going to complain about how slow a query is. What you can figure out when you get an actual execution plan is where things are slow.

If there were any weight stats that help you figure out why they were slow. If there are any big differences between estimated and actual plans that maybe because those estimates were incorrect, SQL Server chose a not so great execution plan. You might look at if the query had any spills.

And you might even look at like what we did earlier with the eager index pool plan about parallel thread distribution. Right. If all your rows are ending up on a single thread and a parallel plan, not a good sign, not a good sign at all. Logical reads are largely a vanity metric these days.

I say it’s like SQL Server 2008 mentality. It’s not, not what you want to focus on in the sort of modern world, especially where people put such a high emphasis on reducing cloud spend because reducing CPU is generally the best way to reduce cloud spend. Physical reads, maybe logical reads, not so much.

So we’re going to pause here before we move on and we’re going to, well, stop because I’m going to do this in three parts. I’m trying to limit the parts to about 20 minutes a piece. So we’re going to stop here and we’re going to pick up here in the next video.

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.

A Little About Scalar UDFs and Read Committed Snapshot Isolation In SQL Server

---

Video Summary

In this video, I delve into an interesting quirk involving recommitted snapshot isolation and non-inlineable scalar UDFs in SQL Server. Specifically, I explore how these UDFs can affect the row versions read by a query under recommitted snapshot isolation, leading to seemingly inconsistent results. By walking through a demo, I illustrate why this happens and explain that it’s not an issue with snapshot isolation itself but rather a consequence of scalar UDFs executing once per row. If you’re curious about more details on SQL Server isolation levels or want to dive deeper into the topic, be sure to check out my playlist titled “Everything You Know About Isolation Levels is Wrong.”

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about a little oddity with, excuse me, recommitted snapshot isolation and non-inlineable scalar UDFs. I don’t think this goes for snapshot isolation.

This is only recommitted snapshot isolation. And this could also like just as easily go for a multi-statement table valued function. But the basic gist of it is, is that like when you run a select query and you read row versions, like the normal select query, the row versions that you read are from if under snapshot isolation, when the transaction begins and you first access the row versions or under RCSI, if you like, you know, when you, when your query starts.

And the thing that, um, scalar UDFs mess with is that the query inside of them. Remember, because like we’ve talked about scalar UDFs a bunch of times here where they don’t like the non-inlineable ones don’t run once per query. They run once per row that the query has to like process and return in some manner.

I’ve gone into detail on that in another video, so I’m not going to redo that here. But the main thing is that like in your select list for every row that your UDF has to execute for, you’re going to read data for the point in time that the UDF starts reading data.

So like your outer select, you know, that’ll, you know, do its thing. But the UDF that executes once per row, that’s going to start reading data for each time that the UDF executes. So you could read different rovers. It’s almost like going back to read committed where, um, this is again, something, if you have questions about any of this, if you want to learn more about this stuff, I have a whole playlist on everything you know about isolation levels is wrong.

And that has a lot of background on this, but it’s like almost like with read committed where like the, like when you start reading data, it’s like a whole bunch of different points in time as you like read along an index. So that’s, that’s kind of what the gist of the demo is.

So you’ll see it in action in a moment. If you want to click links, I’ve got some links down in the video description. Uh, you can hire me for consulting. You can buy my training. You can become a member of the channel that, that donates a tiny fraction of money, like, like the office space, penny stealing thing, uh, every month and say, thank you for the high quality SQL Server content that I produce.

Uh, you can ask me a question for office hours. And of course, if you, if you like this content, uh, enough to, um, maybe spread the good word, uh, about darling data, uh, you can like, like subscribe. And of course, tell a friend or two or 10, uh, once again, I will be at past data community summit, Seattle, November 17th to 21st, uh, two huge days of T-SQL pre-cons with Kendra Little.

It’ll be the best T-SQL you’ve ever learned. So there’s that. Anyway, let’s get back to the database party. I love this picture. So spooky and database-y Halloween-y. I’m almost not looking forward to changing it when Halloween’s over. Maybe, maybe I’ll leave my Halloween decorations up until Christmas.

Who knows? Who knows what’ll happen? Anyway, uh, let’s make sure that, uh, recommitted snapshot isolation is on and we need to make sure that we can, uh, reload these tables here. So we’re going to create a couple of tables and you, you may have seen this demo before in other videos that I’ve done. If you have, I promise this one is a little bit different, but I am going to, uh, show the sort of basic, um, stuff first and then we’ll go back and look at, um, we’ll look specifically at the function thing to see, to see why it’s different. So if we look at what we run this query and we look at what we get back, um, we get rows one through 10 and all total is a thousand for these. Great. Uh, over in this window, I have an update statement. Then I’m just going to run in a loop to add one to the totals column. Where that’s going to get interesting, is with this query. So that update is not yet running. And if I, let’s put, let’s do the read committed one first. So we have that fresh in our brains, uh, right now read committed is telling us that, you know, the same thing as before, but if we start running this update, right. And we come and run this query. The main thing to keep in mind is that for every row that comes back, uh, we’re going to get slightly different numbers. It’s like 100, 200, three, four, five, six, seven, eight, nine, up until we move into a new one. And this will happen every time we run this, because like I said, um, under read committed, as you’re reading data, like, like, like, especially like seriously, if you haven’t watched the isolation levels playlist, go watch it. If you’re, if you’re, if you’re lost on that, as you’re reading data, data can change all around you. Read committed doesn’t guarantee a point in time. Read it guarantees many different points in time that you’re going to read from. So the, the totals for that are all whack, right? So if we, if we run this without the update running, we get a stable result back. So we’re going to do is quote out this read committed lock end, and I’m just going to reset this table to something, right? And show you, you know, just start from sort of zero. Now we get all one thousands, right? So this is our starting place.

And if I start running this update again, right, if I kick this off and we come over here under read committed, these numbers are all stable, right? So because we read a snapshot, read committed snapshot isolation, we read a snapshot of the data. Every time we run this, we’ll get a consistent result back, right? That wasn’t true under read committed. All right, cool. So why does the UDF change things? Well, first let’s create a simple UDF in here. And this UDF is, we’re going to do things slightly differently for this because it just to make it clear what’s happening.

Inside the UDF, we’re actually getting the sum here. So I’m going to change my select query a little bit to rather than sum a sum, I’m just going to get the max from a sum. So inside the this column right here, we are getting this is where our UDF kicks off. If I show the estimated execution plan for this, we get as usual for non-inlineable scalar UDFs, we get two execution plans back, we get the execution plan for the query that’s running and doing stuff, right? This is our outer query, but then we get the plan back for the for the UDF in here. This is where we’re grabbing the sum of stuff from table two. So what I’m going to do is we don’t have any locking hints on this one, right? So we’re not using read committed lock this because this query is executing without any locking hints. It’s using row versions when it runs. But if I come over here and I start running this update again, we’re almost going to see something as weird as when we were using read committed snapshot isolation, where every single row in here is going to be slightly different.

It’s like two 300 500, 879 94, 31, blah, blah, blah, blah, blah. It’s like every time we run this, we’re going to get what seems like non snapshot inconsistent results back from the query that we’re running in there. But it’s really because every time that query executes is once per row, right? Every time that query executes, it’s reading the row versions from a different point in time.

So this can make things look all skewed. So this is just another weird problem that you can run into with queries that that have non in lineable scalar UDFs in them. This is not the fault of read committed snapshot isolation. This does not mean read committed snapshot isolation is bad. This is just, uh, this is a byproduct of the, uh, sort of procedural black boxy, like row by row things that happen, um, when you use, uh, scalar UDFs and SQL Server. Um, so that’s fun, right? Great. It sucks.

Uh, scalar UDFs, stop using them. Get away, right? Just run screaming, go do something else with right functions that different. Don’t do it. Uh, it will cause nothing but problems and headaches for years to come. Your children will inherit these problems. Uh, they’re a nightmare. So don’t do it, right?

No scalar UDFs because they ruin everything except demos for consultants who, um, find weird things about scalar UDFs and isolation levels, which I suppose I should be somewhat thankful for, but, um, you know, it’s a little tough to find gratitude for things that are so annoying, but I don’t know.

Maybe it made a good video. Maybe it didn’t. We’ll find out. We’ll see how many people give this a thumbs up, won’t we? All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. Au revoir and all that stuff.

Bye. Bye.

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.

SQL Server Performance Office Hours Episode 36

---

Questions:

* Back in the days of yore, when you worked for somebody else, did you ever become despondent with your work (or SQL). If so, how did you re-motivate yourself?

* Why have I never heard you suggest ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT?

* For a table-valued function, which would *generally* be the better return type: `RETURNS @tbldata TABLE(ID INT IDENTITY(1,1), col VARCHAR(MAX))` or `RETURNS @tbldata TABLE(ID INT NOT NULL IDENTITY(1,1), col VARCHAR(MAX), PRIMARY KEY CLUSTERED (ID) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY=ON))`

* if you had to do work in another database what would it be?

* what dou you think heaven purgatory and hell are?

To ask your questions, head over here.

Video Summary

In this video, I, Erik Darling from Darling Data, hosted a lively office hours session where I answered five community-submitted questions and shared my insights on various SQL Server topics. Whether it was re-motivating yourself in the face of work challenges or choosing between different table variable configurations for functions, you got direct access to my expertise. I also took the opportunity to discuss some of my personal preferences, like working with DuckDB, a database platform that has really caught my attention due to its innovative approach and impressive features. If you have any questions or want more detailed advice on SQL Server topics, feel free to ask in the comments or through the provided link.

Full Transcript

Erik Darling here, Darling Data. And of course, it is a day of the week that begins the work week, no matter what your language settings are, which means it’s time for office hours, where I answer five community submitted questions, and you get five Erik Darling submitted answers. It’s amazing. What a fine transaction that is. If you want to ask me a question, the link to do that is down in the video description. There are many other helpful links in the video description as well. You can hire me for consulting, you can buy my training, you can support this channel with money, and if you don’t feel like doing any of that stuff, but you still like the content, well, liking, subscribing, and telling a friend that this channel is the best, well, it’s a pretty good way to help me out a little bit. The only thing left on my calendar for the year, past Data Community Summit, Seattle, Washington, Washington, November 17th to 21st, where it’ll be two days of T-SQL pre-cons with me and Kendra Little, and a bunch of other days of other stuff. So you should come, and you should come to my pre-cons, and I don’t know, maybe I’ll dress like a pilgrim or something. Anyway, let’s do the office hours thing, because that’s what we’re here to do.

All right. Here we go. Back in the days of yore, when you worked for somebody else, did you ever become despondent with your work or SQL? If so, how did you re-motivate yourself? Well, I am in a weird place in life, because I love what I do. And you know what they say? When you love what you do, you’ll never take a day off in your life. You know, of course, there would be times when I would get frustrated with something I was working on, or, you know, maybe someone I was working with. I don’t mean like within a company. I mean like, you know, like externally. But, you know, I’ve always found it pretty easy to keep going and find something new and interesting that catches my eye or imagination with SQL Server. So I consider myself very fortunate in that regard. If you are having that problem, I would maybe suggest seeing if there is something else maybe that you could focus on a little bit. Maybe just, you know, change a pace, change a mindset, change a point of view is what you need. Other than that, I don’t know.

You know, there are certainly downsides to working for someone else, like always having to build something that someone else thought of. But, you know, there’s downsides to working for yourself too. Like, sometimes you have no idea what to build. So it’s all, the grass is not always greener. But, I don’t know. Really, I don’t know what you’re doing with SQL or with your life in general.

So, I can’t really give you more specific advice. But, you know, I consider myself fortunate that I have never really had that big of an issue. All right. Next question here. Why have I never heard you suggest alter database current set read committed snapshot on with no wait? Because it’s a stupid waste of time. Just use with rollback immediate and you’ll actually get what you want to happen.

No wait doesn’t really help that much. All right. For a table valued function, which would generally be better? Be the better return type. Returns table data, table ID identity, call varchar max.

Or returns table data, table ID not null identity, call varchar max. Primary key clustered with optimized for sequential key. Why the hell would you turn on optimized for sequential key for a table variable?

Inside of a table valued function. Son, put the database down. Optimized for sequential key does not help you with that.

And, I mean, I’m sorry. I’m hyper focused on how silly that is. So, other than that, you know, the primary key obviously can be useful in some cases if you have equality predicates or certain join types to the table variable.

Without knowing more about how you’re using it, I can’t tell you if the primary key would be helpful for anything. Of course, even indexes on table variables do not get any statistical information. So, and have all the same limitations within a multi-statement table valued function.

It’s been discussed many times on the channel. So, I don’t really think that either one is going to solve a giant problem for you. But if I had to opt for one, it would probably be the one with the clustered primary key on it.

Because it doesn’t really hurt you in any way. And at least for some types of queries, it would be less painful or less annoying. It would still be equally as painful, just maybe less annoying.

All right. Next question. If you had to do work in another database, what would it be? I don’t know. It would still be work.

I get what you’re asking. If I had to work with another database platform, what database would I want to work with? Well, I’ve worked in a few other database platforms. And I got to tell you, I wasn’t that impressed.

You know, there are a couple of nice things that SQL Server doesn’t have in some places. And there was some stuff that I was like, wow, I really wish SQL Server had, I really wish other database had this SQL Server thing.

But I think the database that I am most enamored with currently is DuckDB. I think they are doing absolutely fantastic things. I think the way that they have narrowcast the purpose of their database is fantastic.

I love their focused efforts. And I love the stuff that they’re doing to extend SQL and their database. They can do all sorts of fun stuff.

Like they can read like Postgres and MySQL database files. They have a CSV import that is so simple and so good at what it does that the first time I ever tried it, it made me emotional. It was like, wow, you just did that.

I was like, I got a little misty looking at it. So if I had to do work in another database, it would definitely be DuckDB. I just don’t know how to make money with DuckDB. So that’s another thing.

All right. This is apparently going to be a quick one. All right. What do you think heaven, purgatory, and hell are? Well, I am not the most spiritually scholarly person in the world.

So I don’t know that I have the best point of view on that. I can tell you that for me, heaven would be smoking cigarettes in a French graveyard. Purgatory would probably be being on a team’s call for eternity or however long purgatory lasts.

If I recall correctly, there are some rules and limits around how long you spend in purgatory depending on your situation. And, well, of course, hell would be waiting for a squat rack for all eternity. I think that would be my, those would be my leanings there.

All right. I think that I’ve answered all those questions sufficiently. This is a really short one.

Anyway, I hope you enjoyed yourselves. I hope you learned something. Thank you for watching. And I will see you, well, definitely in the next office hours, but certainly in tomorrow’s video as well. So we have that to look forward to.

So perhaps we are not in heaven, purgatory or hell unless one or more of those is recording YouTube videos, in which case I am in whatever one that is. 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.