temporary table sql Archives | Page 15 of 140

A Little About Default Constraint UDFs in SQL Server

Posted on December 2, 2025January 23, 2026 by Erik Darling

A Little About Default Constraint UDFs in SQL Server

Video Summary

In this video, I delve into the fascinating world of default constraint user-defined functions (UDFs) in SQL Server, exploring how they behave differently from other UDFs when used as default constraints. I walk through creating and testing these functions to understand their performance implications, especially in scenarios where parallelism is involved. By examining both a simple processing function and a more complex badge check function, I highlight the nuances of how SQL Server handles these UDFs under various conditions. Whether you’re looking for insights into default constraints or just curious about the intricacies of T-SQL functions, this video offers plenty to explore and ponder.

Full Transcript

All right, Erik Darling here with Darling Data, your pal, your bosom buddy, your friend till the end. And today’s video, we’re going to talk about, there we go, thank you, PowerPoint, default constraint UDFs. Because I got an office hours question about these at some point in recent memory, and I was not prepared for the level of sickness that exists in this world.

So, I had to look at this more, and I did, and I regret every moment of it. So, if you look down in the video description, you’ll see all sorts of helpful links, where you can hire me for consulting, buy my training, become a paying, supporting member of the channel, like PBS or something. You can, for free, ask me office hours questions, and if you like this content, please do like, subscribe, and tell a friend, because, well, what’s the point of making these if no one watches them, I guess?

Good question, right? So, I’ve mentioned before, I do have a few announcements that I will be making, hopefully in short order, about upcoming speaking events. But the only one that is publicly available at the moment is Past Data Community Summit, taking place in Seattle, November 17th to 21st, where I have two days of the best T-SQL material with Kendra Little that you will ever see in your life.

And, you know, you should just be there, right? So, that’s fun. Anyway, it is still Rocktober, so we are going to continue to be spooky.

Anyway, we’re going to look at two different kinds of functions here today. We have this first function, which doesn’t do anything. We’re going to call this loosely processing in memory.

I have another one that performs data access, which is a little bit more interesting. But we’ve got this function here called serializer. It takes an input here and then spits that input back out here. Well, very simple stuff.

And what we’re going to look at is, you know, kind of how that works. Now, when I first started looking at this, I was like, man, like this could get real bad, right? Because like, you know, like a lot of the UDFs I have in the Stack Overflow database are ones that like, you know, you put in like a user ID or a post ID and you go get information about that user ID or that post ID, right?

So, I was like, man, if I can pass a column to a default constraint UDF, we’re in big trouble. Thankfully, we can’t do that, right? Thankfully, Microsoft was like, nah, right?

So, we’re not allowed to put a column in there. We can only put, let’s see, expressions, constants, constant expressions, and in some contexts, variables. But for us, we’re going to not do most of that stuff.

We’re going to, whatever. Anyway, that is not a name resolution thing. So, like if I create the table like this without anything, and then I try to add that constraint, right?

If I say, hey, why don’t we make that thing in here? SQL Server is still like, nah. All right.

So, same error there. Good. So, like that’s actually a load off my mind immediately. So, the best we can put in there is a literal value. So, if we just say the number one, which means this is going to just spit the number one out no matter what, and we create the table, we are now successfully do that. We’re all set, right?

SQL Server is happy. We’re happy. So, on and so forth. Anyway, if we, like, we have a default constraint, right? And obviously the column is not nullable.

So, we can’t like put a null in there. But, if we look at this and we actually supply the column with a thing to put in there, what happens is kind of neat. SQL Server actually doesn’t deal with the UDF.

Now, this will only be obvious when we look at the other plan, but usually when there’s a UDF, let’s say like in a query, there would be like a non-inlineable UDF for a UDF and a computed column or check constraint, and now default constraint, holy cow. We would get like a non-parallel plan warning somewhere in here. We don’t have that here, but we will in a minute.

So, when you supply a value for the column, SQL Server does not run the UDF because it doesn’t have to produce a value, right? So, if we look at sys.dmExec function stats, a brand new DMV in SQL Server 2016. Wow.

We will see that there are no executions of our UDF in there. But if we rely on the default constraint, so we’re just inserting into this sumDate column. We’re not inserting into the column directly that has the default constraint UDF in it.

If we do this, we’re going to have something slightly different happen. We’re looking at the query plan. Now, we have this tsql user-defined function is not parallelizable thing in the query plan.

Telling SQL is telling us we cannot generate a parallel execution plan. Not that we need it to insert one getDateRow, but you see my point, right? Like we can tell the UDF was invoked there.

And if we look in our wonderful brand new 2016 DMV, we will see that we have one execution of the UDF. Okay? All well and good.

Now, the same pattern is going to follow if we say insert a bunch of rows, right? So let’s put a hundred rows into the table. And let’s look in here, right?

So since we supplied a value for bad default, we’re not going to have any additional executions of the UDF after doing the insert, right? So even though we put a hundred rows into the table, we had a value for every row. So SQL Server was like, we can bypass invoking the UDF for those rows.

If we put more rows in, but we rely on the default constraint, right? We’re not, we don’t have that bad default column in here. So we’re relying on SQL Server to do its thing.

And we run this, we put a hundred rows into the table. But now we look at this. We only have two executions, right? So SQL Server really only invoked that UDF once. Since we’re supplying a literal value to that thing and the value that it produces is, you know, like always like a static value almost.

SQL Server is like, well, I just need to figure out what this is once and I can do all the inserts with that. Right? So we get, even though we put a hundred rows into the table again, right?

We did that. SQL Server only, only executed the function once when we didn’t reference it in the insert list and supply of value. Now, normally, if there’s a computed column in a, sorry, if there’s a UDF in the computed column or a check constraint, there are some weird, there’s weird stuff that happens around parallelism.

For a computed column UDF, it’s like, unless the column is persisted and used trace flag 174, you’ll get a fully, fully serial execution plan no matter what. If it’s in a check constraint, you get a plan that’s forced parallel if you reference the column that has the check constraint, right? So for these, it’s kind of funny because all of these queries, whether I reference the whole table or just the bad default column in any way, SQL Server is allowed to use parallel plans for all of these.

All of these plans have all the visible indicators of parallelism, whether it’s parallel exchanges happening in here like these, or whether it’s the little, you know, icons that say, hey, you went really fast, right? So all of these queries are allowed to go parallel despite the UDF default constraint. Now, if we run this and look just to make sure, we still only have the two executions of the UDF from the inserts that we did.

Good stuff. All right. Let’s look at a different type of function. This one is called badge check.

And what this one does is a little bit different. And this was actually kind of tricky to come up with because like I said, a lot of the, a lot of the UDFs that I have in Stack Overflow, except something like a user ID or a post ID. And then they go out and find some information about that user or that post.

So it was a little hard to figure out something that I might be inserting into a table that was something I could go out and look and like, like also insert like a value that exists in the Stack Overflow database as the same thing. So what this thing does is like you supply a badge name to the UDF. SQL Server goes and figures out if that badge exists in the badges table.

And if it exists, then you put the badge name in. If it doesn’t exist, then you just, you say, then there’s no badge, right? So pretty simple stuff there, but this is the best I could come up with.

So I don’t have an index. So I intentionally do not have an index on the badges table on the name column. So if I run this query, it’s going to take around 500 milliseconds.

It’s not going to return anything, which is fine because that’s, that’s what I expected. But I wanted to do this to sort of like exacerbate the issue with the UDF. So let’s clear up the plan cache and let’s redo our bad default constraint table with this.

Right. So we’re still supplying just a static value in here. Like we’re not like, like this isn’t going to execute, like do something different for every row. It’s always going to be nope. Right.

So we’re going to ever for every row that goes into the table, we’re going to go look at the badges table and say, hey, does a badge called nope exist? If not insert whatever like other value was in there. So it’s a little bit different from the last one.

And the almost the same thing happens, at least at first. So if we insert into the table here and we look at the query plan. Oh, thank you, tool tip.

I definitely wanted you to show up and not let me click on properties. We won’t get the non-parallel plan warning. Right. So all the same so far. If we look at the exec function stats, DMV, we see no executions. And then if we insert, but we make the default constraint do some work.

Ah, you notice that took about 500 milliseconds. Right. And you might notice that there’s a compute scale out right here that took about 500 milliseconds because we had to go look at the badges table.

Now we’re going to see that we could not generate a valid parallel plan because the function plan is not parallelizable. Right. So good, good, good. Right. So just about the same thing. And if we look here, we’re going to see that we have one execution.

And now we’re going to insert 100 rows, but we’re going to supply a value for the column bad default. Right. So just the same thing as before. You do this. That happens instantly. Right. So this, this, you know, the zero milliseconds. And we will not have the go away tool tip. I hate you.

We don’t, we don’t have that non-parallel plan warning over here. It doesn’t exist. So all like all kind of good stuff there. Right. And if we go look at our function stats, DMB again, we still only have the one execution from when we did the single row insert.

But, so you might notice that there’s not a hundred rows in here because 500 milliseconds times a hundred is like 50 seconds. Right. We don’t want to sit here for that long, proving that, proving these things. So we’re just going to insert 20 rows here. And this is a little different, right?

Cause now it’s 500 milliseconds times 20 rows, which I forget how long that is. It’s maybe 10 seconds, maybe 20 seconds. No, 10 seconds. All right, cool. So now we have our execution plan. And if we right click here and look at the properties, we have our non-parallel plan warning over here.

But far more interestingly, we have a compute scalar that takes up 10 and a half seconds of time almost. Right. Like there’s no time here in any of these, there’s no extra time. Oh, there’s a millisecond added there. And there’s a, well, there’s no time added there. So all like basically everything except one millisecond of execution time is in this compute scalar.

And of course this compute scalar. Oh, thank you. Tooltip. They keep showing up. You’re really helpful. If we dig in a little bit to through the defined values, oops, that’s the wrong one. That’s also the wrong one. We want this one. We’ll see our scalar string for badge check with nope. Right.

So that this, this compute scalar was responsible for invoking our UDF. Right. And if we look in here, right. And we say, Hey, what happened? Now we have 21 executions. So once per row that UDF executed to go look stuff up.

So depending on what your scalar UDF does, if it just processes data, let’s say in memory, right? Like it, like you take an input and you return some other input based on that without going out and touching user tables, it’s all fine. But if your scalar UDF goes out and looks at another table, right?

Then you’re going to get this really crappy, like once per row execution and maybe even very, very slow execution because of what your, if your function body is slow. And then for the parallelism stuff, well, let’s, let’s go look at that. Do, do, do, do, do, do.

We get three plans and all of them are allowed to go parallel. It’s all the same visual indicators of parallelism, whether it’s the parallel exchanges like gather streams or repartition streams, or whether it’s the little racing stripes on all of our little operators here. Right.

So all of these things. So at least it doesn’t hurt select query performance. But if you write a really monstrous T-SQL UDF that goes out and touches user tables, well, you might be in for a bad time for other things. And of course, if we go look at this, we should still have just the 21 executions of the function from the single row insert.

And then the 20 row insert that did not where we did not supply a value for the column that has a default constraint on. So anyway, I thought this was all very fun and interesting. I hope you also thought it was fun and interesting or maybe just fun or interesting.

Maybe one of the two, maybe you just found it incredibly sad and depressing, which is also a good outcome because now you won’t do this. Right. Or maybe you never would have. Maybe you’re just that much smarter than whoever asked me the question. I don’t know. But anyway, that’s good here.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where I think we’re going to talk about a bug in adaptive join plans with query execution time. So fun things to look at there. Anyway, thanks. 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.

SQL Server Performance Office Hours Episode 38

Posted on December 1, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 38

Questions:

3. What are your thoughts on using AI (non-Microsoft) for query optimization and index maintenance? I’ve had 50-70% success tuning queries/indexes with 2-3 LLMs by providing schema/index/stat context first. Considering AI automation: identify poor-performing queries, add env context + tuning rules, use MCP tooling to test in read-only dev env; 3 models propose optimizations, best performer wins and goes to DBA for review. Thinks this is worth pursuing now, since AI will get better over time too? Not worried it will replace my job, performance tuning is only about 5% of my work and changes, generally, only make it to prod if prod reports performance issues. Even when I took the worst offending query and reduced runtime by 12X (no exaggeration) using indexed temp tables and a CTE to do aggregates then join on the temp tables instead of repeated joins on the same tables over and over. The change was *I think* was “too good to be true” and “might have a logic flaw that we can’t properly test for in dev” and thus hasn’t made it to prod… sorry for the rant.
Lets say there is a table with Key column of number but it has trailing zeros (may be obe or two zero), i had that column as varchar, but business would never put zero when searching for that column. All out queries had like clause with ‘%%’, that killed the perofrmance. How would you approach the problem ?
I’ve never seen anyone use a plan guide, not even in a blog post or quick video. Any idea why? When do you use them?
We have a UDF as a default expression for a column on a table. Does SQL Server still evaluate the UDF in default expression even when I explicitly insert values into that column?
Hey Erik. It took me many months, but I’ve finished watching all of your videos and turned your lessons into a drinking game. What do you think? I’m playing it right now https://dbfiddle.uk/CQbdBNH5

To ask your questions, head over here.

Video Summary

In this video, I dive into a lively session of Office Hours where we tackle five community-submitted questions on various SQL Server topics. From exploring the use of AI in non-Microsoft environments for query optimization and index maintenance to discussing the pitfalls of storing numbers as strings with trailing zeros, we cover a wide range of scenarios. Additionally, I delve into the lesser-known topic of plan guides and address some humorous yet insightful queries about UDFs used as default expressions and even the personal experience of someone who has binge-watched all my videos—turning them into a drinking game! It’s been an engaging session, and I hope you found it both entertaining and informative.

Full Transcript

Erik Darling here with Darling Data. And today’s video is, of course, everyone’s favorite. My favorite, your favorite. Of course, that is everyone, isn’t it? Office Hours, where I answer five community submitted questions to my fancy little Google spreadsheet. And hopefully everyone is happy with the answers. If you look down in the video description, there are all sorts of helpful, useful links for you and me. You can hire me for consulting by my training, become a paid member of the channel. Of course, you can ask me Office Hours questions for free. They are not insert coin here. But we’ll see, we’ll see how this, see how this next year’s economy is. And if, of course, if you like this material, you, I would be ever so pleased, ever so chuffed. If you would like, subscribe and tell a friend or two or 10. I think I’ve suggested in the past, just taking people’s laptops and hitting subscribe in their YouTube accounts. Past data community summit coming up in Seattle, November 17th to 21st. Kendra Little and I doing two bang up days of T-SQL pre-cons, arguably the best two days of T-SQL pre-cons that have ever taken place in the world. I think I’ve mentioned before, I do have a couple other announcements coming up soon, hopefully sooner than later, so that you can see what you’re doing.

The fine folks of the world can start purchasing some tickets. Be nice there. Anyway, let’s go answer these gosh darn questions, shall we? Oh, we have a third. So if you watched last week’s episode of Office Hours, we might remember our friend with the high volume fintech OLTP something or other on SQL Server 2017 Enterprise Edition. Third question has arisen from our friend. Hopefully we can give our fintech friend enough free advice here. What are your thoughts on using AI non-Microsoft? Well, geez, I’m convinced. For query optimization and index maintenance.

We’re having AI rebuild our indexes? I don’t know. What is that? What are you on about? I’ve had 50% to 70% success tuning queries. Okay, well, I have 100% success. How’s that? With two to three LLMs by providing schema index that…

I’m just going to say something here. You work in fintech and you’re sending schema index and stat context to different LLMs. That’s interesting. Okay.

I hope they’re local. Considering AI automation. Identify poor performance queries. Add environment context and tuning rules.

Use MCP. Holy Moses. Okay. Performance tuning is only 5% of your work. Boy, oh boy.

Look, if it’s working for you, go ahead. I don’t know. Performance tuning is 5% of your work. If you need an LLM to tell you to use index temp tables and a CTE, you’re probably better off doing that.

All right. I need to move on before my head explodes. Okay. Let’s say there is a table. Yes, let’s say there’s a table. I would love to say there’s a table.

With a key column that’s a number, but it has trailing zeros. I don’t know why a number with trailing zeros is strange. Many numbers have trailing zeros.

Numbers with leading zeros are a different matter. I had that column as Varkar. Oh, great way to store numbers.

Varkar. Good job. But business would never put zero when searching for that column. All our queries had claws with double wildcard that killed performance. How would you approach that problem?

Well, I wouldn’t. That is just a fundamentally unsound design. You have allowed business users to trample over the database entirely, and they’ve won now. You can’t take that away from them.

I’m saying you because you asked the question. But whoever designed this screwed it up from the beginning. Even if you put a columnstore index on that, the double wildcard, assuming this is going to be a Varkar at least 10 or 11, right?

Because most people, when they store numbers as strings, at least account for the maximum width of whatever data type the number is supposed to start with. So if it’s an integer, then it would be like, what, 10, 11 or something, right? If it’s a big, the 9 quintillion number, it’s like 15 digits.

So, like, you know, it would be like a Varkar, like 10, 11 or 15 or whatever. So, Varkar 10, sorry, because it’s the same number of digits as a phone number. It’s a 214, three other digits, four other digits.

So, you’ve just ruined the whole thing from the get-go. You might be able to do some very complicated stuff with trigram searches, but I don’t know if you want to implement all that just to help these people out. But the smarter thing to do would have been to give them more restrained search protocols so that you did not end up here in the beginning or from the beginning.

There you go. Anyway. I’ve never seen anyone use a plan guide, not even in a blog post or quick video.

Any idea why? When do you use them? I’ve seen many people use plan guides in blog posts and videos. So I have no idea why you’re not able to find them.

Perhaps you do not search well, whoever you are. When do you use them? Well, you use them when you need to guide a query to a different query plan that it is not finding naturally. The name is somewhat self-explanatory in that regard.

I’m not going to pretend that they are very easy or ergonomic to use. There are many times when even yours truly has struggled to get the query to pick up on the plan guide. But with enough trial and error, you can usually get it working.

All right. Let’s see here. We have a UDF as a default expression for a column on a table. Does SQL Server still evaluate the UDF in the default expression even when I explicitly insert values into that column?

Oh, I see. It’s a default. Okay.

It shouldn’t know, but there’s no telling what strange things might happen when you start doing awful things like using UDFs as default expressions. I’d actually never even considered that level of terrible.

So good job you. You might make it into a future demo with that level of bad. Holy Moses. All right.

My Lord. Hey, Eric. Hey, Eric. It took me many months, but I finished watching all of your videos. You should go outside.

You should go enjoy life. There is a world out there. I don’t know why you’d want to watch all of them. Oh, well, that’s why. You’ve turned my lessons into a drinking game. Well, I changed my mind.

I wholeheartedly approve of you. What do you think? I’m playing it right now. Well, what do I think? I fully endorse drinking games. I think that they are one of the last vestiges of proper modern society and that you should continue to watch my videos and drink.

I’m not going to that link, but I don’t want to know because I have a feeling if I go there, I’m going to know how much you drank and I’m going to know how much drinking it took you to get through my videos and that might make me a little sad.

So I’m not going to go there, but I like that you have the kind of go-getter attitude that you create drinking games for me. All right.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in next week’s office hours, but also in tomorrow’s video, whatever that may be.

I’m sure it’ll be a humdinger. Actually, we’re going to go through my Rogols presentation that I recently finished giving at the Pass on Tour events. So we’ll have that to look forward to, won’t we? Rogols.

Yay. Fun. It would be awesome as if I talked about Rogols and no one compared me to other people who have talked about Rogols.

That would be fantastic. It’d be nice if I could talk about something without someone just pointing out that, hey, someone else has talked about that. Wow. Cool. All right.

Rogol and away all this week. Thanks for watching.

Going Further

Row Goals: Part 4

Posted on November 28, 2025January 23, 2026 by Erik Darling

Row Goals: Part 4

Video Summary

In this video, I delve into the world of query tuning for Rogals—those pesky tables that can sometimes cause performance issues. We explore how to identify when and if using a top hint or row goal is appropriate to optimize queries. By analyzing a specific example where SQL Server scans 11 million rows unnecessarily, I demonstrate how adding a seek hint with the top operator can significantly improve query performance. Additionally, I discuss the nuances of using row goals in conjunction with cross apply and for hints, showing how they can help SQL Server make better decisions about execution plans without resorting to overly aggressive hints that might not always be allowed by strict DBAs.

Full Transcript

All right, welcome back to the fourth and final installment of the Rogals talk that I’ve been recording and hopefully presenting to the adoring masses. We’ve looked at a number of different things about Rogals, like sort of some like what they are, some good stuff, some bad stuff. We’re going to look at some cool query tuning stuff that you can do with them now, and ways that you can sort of figure out if Rogals are appropriate for you.

All right, so we’ve got this query right here, and this query is kind of funny because it only returns one row. Again, going back to the rows per second ratio thing, and it takes about 900 milliseconds to run. It’s not disastrous, but like, I don’t know, maybe we’re just unhappy with it for whatever reason.

The reason why I would be unhappy with this query is because I’ve got a perfectly good index on the post table that we could seek into and life could be grand with. Okay, but no, we start off by scanning the post table, and it takes about 900 milliseconds.

If we look at the work that SQL Server did to, on that scan, it was 11 million rows. All right. Why would SQL Server scan 11 million rows when it could seek to them?

Well, if we add in the for seek hint here, we will get a different execution plan, arguably better and faster execution plan back, right? So we say, oh, well, there’s 15 milliseconds now.

We started seeking into the users table. We found some rows. We did a seek into the post table. We did a seek into the comments table. Note here, there’s a top above a seek, and this is not a disaster the way a top above a scan was. I mean, this one’s, oh, this is okay for us.

So maybe for whatever reason, we were like, ah, we’re better than for seek. We can, we can get away without that. Maybe, maybe we have terrible DBAs who would yell at us for, for using query hints.

Cause some DBAs are like that. Some DBAs who couldn’t, who couldn’t actually tune the query themselves. It’d be like, you never use a hint.

Uh, so a way that we could get around our DBAs prying, hinting, hating eyes, uh, would be to, uh, use a top. Right? So just like we’ve done in other queries, we’re going to use a top with a big number here to tell SQL Server that we want to do a particular part of this query together.

And then, uh, we want to make sure that, um, SQL Server sort of like puts a little like hug around this and does this stuff and then does the other stuff or not, not like then, but it does this stuff on it in its own sort of world. So if we run this, uh, with the top in there, we get back a, another, another query plan. Again, this like, just like using the four secant and the last one, this one also takes about 15 milliseconds.

Now we don’t have the outer, we don’t have an outer top out here, right? We just have a top down here, right? There’s no root level, uh, row goal, but there is one down here in the top.

And the top puts this part of the plan together. What, what this does that’s kind of cool is it, again, it starts off with the users table, right? So we seek into the users table, we find the stuff we care about there, and then we go into our nested loops join.

And then for each row that, uh, comes through here, we go into the, we go into this part of the plan and we seek into the post table without having that four secant. So the combination of sort of cross apply in the top, um, with the cross apply with the top, and we have the, the correlation within the cross apply here. If we take out the top and we rerun this, we’re basically back to the original execution plan, right?

This one’s back to like 900, 900 milliseconds. SQL Server makes the same kind of boneheaded choice to scan the post table out here first, and then do a hash join to the users table, which is not what we want, right? But having the top in here is what kind of gets SQL Server on board with doing what we want within the cross apply.

Uh, one thing that’s actually, uh, kind of worth noting here, because, uh, someone actually asked me about this, uh, at, uh, the, the first time I gave this talk, which is in New York, I think. God, it all feels like 10 years ago now. I’ve slept like five minutes in the last three months.

Uh, but it was about the, the, the star in here, right? So we have a select P dot star from this, but obviously, uh, the only thing that we’re getting in the outer part of the query is the ID column, right? So SQL Server is smart enough during sort of like, uh, like some, some of the early optimization stages where like, I don’t know, parsing, binding, algebraizing, simplifying stuff, all that stuff that we usually skip over.

Well, I mean, simplification is cool, but the rest of it, uh, SQL Server figures out pretty early on that we only need the ID column in the, like the outermost project of this. And so it throws away all the star that we don’t need in here. So kind of a nice thing.

Uh, anyway, um, I’ve spent a lot of time in my videos, making fun of, uh, the optimize for unknown hint. Uh, because people use optimize for unknown, uh, I mean, or some, some equivalent of it to fix parameter sniffing, right? Uh, they’ll either use optimize for unknown or they’ll set, uh, some local variable equal to a formal parameter passed into the store procedure.

And they will put a quote in there that, Hey, I fixed this whole thing. Optimize for is cool for a lot of reasons. Uh, but not that.

So optimize for is neat because you don’t have to optimize for unknown. You can optimize for specific values. And one place that you can optimize for specific values is with top. Now this of course deserves, uh, the biggest shout out in the world to Adam Mechanic.

Because if you look through SP who is active or some of his other code and stuff, you’ll see this pattern all over the place. And it’s a very cool one. So thank you, Mr. Mechanic for, uh, introducing the world to this one.

Uh, so all the, all the credit goes there. So let’s say we have this query. And we run this and we have sort of a similar situation to the last one.

SQL Server, once again, chooses violence. It starts off by scanning the post table. And again, this is reading 11 million rows and it takes 900 milliseconds, right?

And we are, well, just about 900 milliseconds, 863 milliseconds, which we’re rounding up, uh, just to keep things in order, right? 900 milliseconds. This sucks.

We hate it. We’re mad at it. We don’t want this thing to keep doing what it’s doing. No, we could, of course, like in the previous example, we could throw a force seek hint on the post table. But again, we have mean, crabby DBAs who do not allow us to use force seek hints. They only allow us to use, uh, code from crazy people.

So let’s say we want, uh, in a different way for SQL Server to use, uh, the, to start with the users, kind of start with the users table and the filtering that we’re doing on the users table and then move on from there. All right. Let’s say that that’s just our goal for this.

All right. It’s our, it’s our, our goal. And we’re going to use a row goal to do it. So, uh, what we’re going to do is, uh, we’re going to use some again, top magic, right? We’re going to say, we’re going to declare the top and we’re going to set it to the nine quintillion number, right?

The big, big number, the hugest number in SQL Server. I guess, I guess some decimals and stuff could be bigger, but who has time for that? One, one could die thinking about these things.

Some, some probably have, you know, if you think, if you think carefully about it, but here we’re going to use top. Uh, we’re going to use this top, well, local variable, uh, in this part of the query. So we’re going to cordon off this select at this top at top from users where creation date, blah, blah.

Uh, and reputation is greater than blah, blah, right? And we want SQL Server to give us the top, uh, nine quintillion rows for this. But maybe we don’t want SQL Server to come up with a query plan for nine quintillion rows.

Maybe, maybe if we gave to see if we gave SQL Server that big number, maybe SQL Server would do something terrible. Maybe it wouldn’t use our index. Maybe it would freak out and just be like, uh, I can’t, I can’t wrap my head around it.

I’m going to give you the worst query plan I can. Ha ha ha. This is the most expensive one sucker. Right? Like you don’t know, like it just freaks you out when you think about it, keeps you up at night.

So let’s say we wanted SQL Server to, uh, do this, but, uh, Oh, sorry about that. Do this. Right.

We want, we want to get the top big number, but we want SQL Server to act like this is a small number. And maybe we don’t want to add a fast end hint to the outer query because again, the crabby DBAs, the discipline crabby DBAs will come and pinch us at night. So what we can do is something that’s very cool.

And we can say, we can tell SQL Server to optimize that for that top being equal to one. So even though we have the top with the big number, right? We’re going to keep, we’re going to get all the rows in the world back.

We’re going to opt. We’re going to ask SQL Server to optimize for a query plan that only asks for one row. So it’s sort of like saying fast one, just with the top and the row goal. And when we run this, we get back a much snappier execution plan.

Right. And we indeed have a top operator here and we have a seek into our index on the users table and then a key lookup to get some other stuff. And then we go and get the rest of the query plan done, going into a nested loops, seeking into the post table and then doing the not exist portion of the post table thing.

So row goals, fun, fun, fun. Uh, row goals are sort of like promises from the optimizer about how many rows will be produced. Um, you can set them, uh, or rather you may set them explicitly using top offset, fetch fast.

And those are the most common. Uh, there’s also like the set row count thing, but we don’t need to talk about set row count. That’s you use that.

I know, no mercy and no pity. Uh, and the optimizer may set them implicitly. Uh, if you use, uh, things like exists, not exists in, not in, or equals any equals all or equals any or equals some equals all is much different. Uh, and they can act at times like optimization fences to help you reshape query plans.

Why they matter is because they change how the optimizer costs and build plans. Uh, they can make some pretty impossible queries very, very fast by forcing, uh, better join orders or just sort of better, like things happening together that, you know, especially if you can get it. So they reduce rows very, very early on.

That’s a very powerful thing that you can do, um, to, uh, start getting better query performance without having to throw hints everywhere. Cause hints often break down over time. Um, exists will, uh, generally already have a role goal of one.

You don’t need to say select top one distinct order by group by all the other stuff. Cause SQL Server just throws it right in the garbage. Uh, top 100%.

Like we talked about, if you put this in a view, the optimizer completely throws it in the garbage, slam dunks on your head, gives you a wedgie. It’s a mean time. Um, if you are going to use top and you are expecting data back in a specific order, make sure you have use deterministic ordering. That is like, you know, the order by elements that you have, there is at least one unique tiebreaker within them.

Otherwise you have non-deterministic ordering and you may have silent bugs and stuff in your query plan. There are some very cool things that you can do, uh, especially with cross supply and top. Um, you can get nice, easy nested loops joins.

You can get SQL Server to seek into stuff when, uh, you thought that you would never convince the optimizer to do the right thing. Uh, if you were hitting problems with row goals and stuff like if exists, or, uh, you see that top above a scan, um, for the, if exists, we used count big to fix stuff. Uh, for the top above a scan, it’s usually creating an index. So you have a top above a seek.

That will make life a lot easier. Uh, and if you wanted to, uh, maybe ask SQL Server for a large number of rows, but only come up with an execution plan, like it’s going to hit a small number of rows, then you can use parameterize top and, uh, tell SQL Server to optimize for top equals some small number, right? It doesn’t have to be one, just has to be a smaller number than nine quintillion.

So, uh, that wraps things up. Uh, thank you for watching. Uh, again, we use row goals wisely and as always, I’m Erik Darling and my rates are reasonable. All right.

Goodbye.

Going Further

Row Goals: Part 3

Posted on November 27, 2025January 23, 2026 by Erik Darling

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

Row Goals: Part 2

Posted on November 26, 2025January 23, 2026 by Erik Darling

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

Row Goals: Part 1

Posted on November 25, 2025January 23, 2026 by Erik Darling

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

SQL Server Performance Office Hours Episode 37

Posted on November 24, 2025January 23, 2026 by Erik Darling

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

Lost In Costs: Part 3

Posted on November 14, 2025January 23, 2026 by Erik Darling

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

Lost In Costs: Part 2

Posted on November 13, 2025January 23, 2026 by Erik Darling

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

Lost In Costs: Part 1

Posted on November 12, 2025January 23, 2026 by Erik Darling

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.